From Ubuntu

Some of the steps take some time on larger data sets, so be patient when importing.

💡 If you dont have sqlite3 then you need to install it

Open terminal and type or copy in. - To open terminal press (Ctrl + Alt + T) on keyboard.

sudo apt update -y

sudo apt install sqlite3 -y

If you want to remove sqlite3 after the merge you can do it like this:

sudo apt remove --purge sqlite3 -y

💡 If you dont have mariadb-client then you need to install it

sudo apt install mariadb-client -y

If you want to remove mariadb-client after the merge you can do it like this:

sudo apt remove --purge mariadb-client -y

💡 Get the latest script:

.zip (main.zip)

wget https://github.com/JacobsenKim/ha2mariadb/archive/refs/heads/main.zip && unzip main.zip && rm main.zip && mv ./ha2mariadb-main/ha2mariadb ./ha2mariadb && cd ./ha2mariadb

💡 Or if you prefer .tar (main.tar.gz)

wget https://github.com/JacobsenKim/ha2mariadb/archive/refs/heads/main.tar.gz && tar -xzvf main.tar.gz && rm main.tar.gz && mv ./ha2mariadb-main/ha2mariadb ./ha2mariadb && cd ./ha2mariadb

• Make backup of homeassistant and save the file to ~/ha2mariadb/ folder

• Unpack home-assistant_v2.db file to this ~/ha2mariadb/ folder, then from the same folder:

💡 Now do a dump of the

home-assistant_v2.db to sqlite3_dbfile_.dump.sql so we can work with sql.

sqlite3 home-assistant_v2.db .dump > sqlite3_dbfile_.dump.sql

And lets dump the Tables schema also to compare the column order is right in the import file a0_homeassistant_schema_43_tables.sql.

sqlite3 home-assistant_v2.db ".schema" > sqlite3_dbfile_tables_schema.sql

💡 Clean and split big files (only data in the files).

chmod +x grep_sql_data.sh

./grep_sql_data.sh sqlite3_dbfile_.dump.sql

This will create an output folder with all files, the split folder is the big files over 50000 lines split in to small files if neded.

The big files is still in the output folder and is the one that is imported.

💡 Homeassistant have change the table column order over time, so to make sure we have the right column order that fits our data, we need to use the table column order from our homeassistant DB

Open in text editor sqlite3_dbfile_tables_schema.sql

From the output folder open a0_homeassistant_schema_43_tables.sql with text editor, and check the column order match your sqlite3_dbfile_tables_schema.sql files column order, if not edit the order so it match else the data will end in a wrong column.

💡 Make sure the tables column order match the one from youre sqlit3 DB sqlite3_dbfile_tables_schema.sql

💡 Make sure its the a0_homeassistant_schema_43_tables.sql file in the output folder you edit, this file will create the Tables for the data set.

💡 Dont have sqlite3_dbfile_tables_schema.sql in output folder it stays in ha2mariadb folder (we dont want to import that file)

Import remote to home assistant

💡 This will import all .sql files in the output folder and move successful imported files to successful folder.

Now import the files to our home assistant mariaDB (DB name: homeassistant user: homeassistant)

chmod +x sql_import_remote.sh

./sql_import_remote.sh your_db_IP

It will promt for PASSWORD to the mariaDB (DB name: homeassistant user: homeassistant)

When finish importing the tables and data files, then we need to copy z9_grep_mysql_importme_last_43.sql from ha2mariadb to the output folder and edit the 12 numbers to match the one shown when press Y after import is finish.

Save the file and make the last import.

This time we press n to show numbers, and the merge from sqlite3 to mariaDB is now complete.

Last add to configuration.yaml and save.

Remember to use your password DB_PASSWORD_HERE

recorder:
    purge_keep_days: 36 # default is 10
    db_url: mysql://homeassistant:DB_PASSWORD_HERE@core-mariadb/homeassistant?charset=utf8mb4

Restart homeassistant, and you are now merge to mariaDB, check your energy stats and history.

You can remove the port number from mariaDB settings to close the remote acces to mariaDB if you are not using it any more.

💡 Remember to make backup from phpmyadmin or with script, to always have a fresh backup of homeassistant mariaDB.

💡 Script: sql_backup_remote.sh <--------