migrate sqlite to a mariadb database

1. intro

a running mariadb server, either externally such as srv202.d01.net or locally.
following procudures has to be excuted on the node where the homeassistant is running.

2. prereqs

yum -y install mariadb sqlite
wget -N -nv http://d01cid.ddns.net/sharel/bin/ha-mariadb -P /bin/
wget -N -nv http://d01cid.ddns.net/sharel/bin/ha-sqlite2mariadb -P /bin/
chmod +x /bin/ha-mariadb /bin/ha-sqlite2mariadb

3. config

ha-mariadb -s srv202.d01.net

grep "hadb" /etc/cid.conf

hadbserver="srv202.d01.net"
hadbname="hadb204"
hadbuserid="root"
hadbpasswd="4627037737371605"

grep recorder /config/configuration.yaml

recorder: !include recorder.yaml

cat /config/recorder.yaml

#
db_url: !secret recorder_db_url
#
//

previous file will be updated automatically, if u change anything, inform the administrator.

cat /config/secret.yaml

//
recorder_db_url: mysql://root:Passw0rd@srv202.d01.net/hadb204?charset=utf8mb4

4. audit mariadb database

mariadb -h srv202.d01.net -u root -pPassw0rd hadb204 -e 'show tables';

+-----------------------+
| Tables_in_hadb204     |
+-----------------------+
| event_data            |
| event_types           |
| events                |
| migration_changes     |
| recorder_runs         |
| schema_changes        |
| state_attributes      |
| states                |
| states_meta           |
| statistics            |
| statistics_meta       |
| statistics_runs       |
| statistics_short_term |
+-----------------------+

only, if no output, continue with following:

wget -N -nv http://d01cid.ddns.net/sharel/sql/homeassistant.sql
mariadb -h srv202.d01.net -u root -pPassw0rd hadb204 -e 'show tables'; < homeassistant.sql

5. audit sqlite file

sqlite3 /config/home-assistant_v2.db 'select * from statistics_meta'

1|sensor.ssw081_energy|recorder|kWh|0|1|
2|sensor.ssw081_power|recorder|W|1|0|
3|sensor.sm_a528b_battery_level|recorder|%|1|0|
4|sensor.sm_a528b_battery_temperature|recorder|°C|1|0|
5|sensor.ssw081_energy_cost|recorder|EUR|0|1|
6|sensor.sm_a528b_battery_level_2|recorder|%|1|0|
7|sensor.sm_a528b_battery_temperature_2|recorder|°C|1|0|
8|sensor.shellyplug_s_1bfe25_power|recorder|W|1|0|
9|sensor.shellyplug_s_1bfe25_energy|recorder|kWh|0|1|
///

6. migration file

vi migration.conf

sensor.elgris_ac_meter_total_watt_hours_imported
sensor.elgris_ac_meter_total_watt_hours_exported

u can also use wildcard characters:

sensor.elgris_ac_meter_total_watt_hours_%
%power%

as a starting point use this:

grep sensor /config/.storage/energy | awk -F':' '{print $2}'

 "sensor.elgris_ac_meter_total_watt_hours_imported",
 "sensor.elgris_ac_meter_total_watt_hours_exported",
 "sensor.stp6_0_3av_40_316_total_yield",
 "sensor.elgris_ac_meter_total_watt_hours_imported"
 "sensor.elgris_ac_meter_total_watt_hours_exported"
 "sensor.stp6_0_3av_40_316_total_yield"

7. and now, the big magic

ha-sqlite2mariadb migration.conf

. server: srv202.d01.net
. dbname: hadb204
. userid: root
. passwd: ******

. delete: statistics
. delete: statistics_meta
 ------------------------------------------------------------
. sensor: elgris_ac_meter_total_watt_hours_imported
.  table: statistics_meta
.  lines: 1
.  table: statistics
.  lines: 21856
. insert: 22.95 secs
 ------------------------------------------------------------
. sensor: elgris_ac_meter_total_watt_hours_exported
.  table: statistics_meta
.  lines: 1
.  table: statistics
.  lines: 21856
. insert: 22.23 secs
//
 ------------------------------------------------------------
. status: done