Steps necessary to migrate from HomeAssistant’s standard sqlite database to an external MariaDB(10) SQL database

HA_Migrate-sqlite-to-mysql.md

Migrate Home Assistant’s sqlite database to MySQL, specifically MariaDB (10)

After having added a decent amount of entities to my Home Assistant setup, the user interface—especially the history tab, but also the history for each entity—became very sluggish. Having to wait for up to 30 seconds to see the history graph for a single entity became the norm with an sqlite file weighing in at 6GB and millions of entries in the “events” and “states” tables.

I wanted to keep my acquired data, so I had to migrate it from the current sqlite file to a MySQL database instead of starting anew. I’ve done this using Home Assistant version 0.81 and MariaDB 10. Some parts might change in the future.

Dump Home Assistant sqlite database

Make sure you have sqlite3 installed and in your path—e.g. via installing it using apt-get install sqlite3.

Then, stop home assistant to have a non-changing database file.

Change to your homeassitant data folder and then, using

sqlite3 home-assistant_v2.db .dump > ha-database_sqlite.dump.sql

you can dump your current sqlite Home Assistant database into an sql file. Depending on your database size and I/O speed of your device, this might take many minutes, even hours.

Convert sqlite dump to something suitable for MySQL

Out of the box, the sqlite dump doesn’t work in a MySQL setup.

For conversion, I found https://github.com/athlite/sqlite3-to-mysql/blob/master/sqlite3-to-mysql but it doesn’t completely work, so I had to make some changes.

I replaced

-e 's/"/`/g' \

with

-e 's/"events"/`events`/g' \
-e 's/"recorder_runs"/`recorder_runs`/g' \
-e 's/"schema_changes"/`schema_changes`/g' \
-e 's/"states"/`states`/g' \
-e 's/"end"/`end`/g' \

as otherwise, the json strings within the tables were broken.

See the whole content in sqlite-to-mysql.sh.

You might need to make the shell file executable using chmod +x sqlite3-to-mysql.sh

Afterwards, you start the conversion process using

sudo ./sqlite3-to-mysql.sh ha-database_sqlite.dump.sql > mysql_import_me.sql

Like the initial dump, this may take quite a while to finish. And you won’t have a progress bar either.

Import the converted sql file into a MySQL database

The file won’t have a database creation query in it, so you start by adding one to your MySQL database yourself.

Connect to your local mysql instance, enter the root password when prompted:

mysql -u root -p

Create a database called “homeassistant”:

CREATE DATABASE homeassistant;

Create a user “homeassistant” with access to the database so that you don’t have to use your root account:

GRANT ALL PRIVILEGES ON homeassistant.* to 'homeassistant' IDENTIFIED BY '<yourpassword>'

Now you are ready to import. To speed up the process, I first set autocommit to false and afterwards manually commited. I found that this increased the speed of the import. YMMV.

SET autocommit=0;
source <full path to your mysql_import_me.sql file>;
commit;

Again, this will probably take some time.

Fix some issues the import file doesn’t address yet

Find out current max of event_id to set AUTO_INCREMENT:

MariaDB [homeassistant]> SELECT MAX(event_id) AS Count FROM events;
+---------+
| Count   |
+---------+
| 3189954 |
+---------+
1 row in set (0.000 sec)

Then alter the events table, use the previous max, incremented by 1 (=> +1), and set it as the starting point for AUTO_INCREMENT:

ALTER TABLE events MODIFY COLUMN event_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3189955;

Repeat this process for the three other tables. Especially events and states will take some time (half an hour was the longest I saw so far).

MariaDB [homeassistant]> SELECT MAX(state_id) AS Count FROM states;
+---------+
| Count   |
+---------+
| 3189396 |
+---------+
1 row in set (0.000 sec)

MariaDB [homeassistant]> ALTER TABLE states MODIFY COLUMN state_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3189397;

MariaDB [homeassistant]> SELECT MAX(run_id) as Count FROM recorder_runs;
+-------+
| Count |
+-------+
|   192 |
+-------+
1 row in set (0.042 sec)

MariaDB [homeassistant]> ALTER TABLE recorder_runs MODIFY COLUMN run_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=193;

MariaDB [homeassistant]> SELECT MAX(change_id) AS Count FROM schema_changes;
+-------+
| Count |
+-------+
|     3 |
+-------+
1 row in set (0.043 sec)

MariaDB [homeassistant]> ALTER TABLE schema_changes MODIFY COLUMN change_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

Then, drop the foreign key of states, should it exist, so we can recreate it on our own with the right parameters.

ALTER TABLE states DROP FOREIGN KEY states_ibfk_1;
ALTER TABLE states ADD CONSTRAINT `states_ibfk_1` FOREIGN KEY (`event_id`) REFERENCES `events` (`event_id`)

Change configuration.yaml to use the MySQL database

You’re almost done! Only task left is to add MySQL to your HA configuration.

See https://www.home-assistant.io/components/recorder/#custom-database-engines for your specific setup.

For MariaDB 10, I had to set

recorder:
  db_url:  mysql://homeassistant:<YOURCHOSENPASSWORD>@<MARIADB10HOST>:3607/homeassistant

And that’s it!

sqlite3-to-mysql.sh

#!/usr/bin/env sh

if test -z "$VARCHAR"
then
	VARCHAR="255"
fi

sed \
-e '/PRAGMA.*;/ d' \
-e '/BEGIN TRANSACTION.*/ d' \
-e '/COMMIT;/ d' \
-e '/.*sqlite_sequence.*;/d' \
-e "s/ varchar/ varchar($VARCHAR)/g" \
-e 's/"events"/`events`/g' \
-e 's/"recorder_runs"/`recorder_runs`/g' \
-e 's/"schema_changes"/`schema_changes`/g' \
-e 's/"states"/`states`/g' \
-e 's/"end"/`end`/g' \
-e 's/CREATE TABLE \(`\w\+`\)/DROP TABLE IF EXISTS \1;\nCREATE TABLE \1/' \
-e 's/\(CREATE TABLE.*\)\(PRIMARY KEY\) \(AUTOINCREMENT\)\(.*\)\();\)/\1AUTO_INCREMENT\4, PRIMARY KEY(id)\5/' \
-e "s/'t'/1/g" \
-e "s/'f'/0/g" \
$1

When i do import to an mariaDB databses i can see
ERROR 1136 (21S01) at line 36014 in file: '/data/databases/mysql_import_me.sql': Column count doesn't match value count at row 1
I do:

1. mysql -u root -p homeassistant
2. input
   SET autocommit=0;
   source /data/databases/mysql_import_me.sql;
   commit;

Notes for future newbie google users like me:

If run MariaDB over Docker, you'll connect using:

docker exec -it mariadb_containername mariadb -u root -p

After creating the user and setting the password, enter into database using:

USE homeassistant;

Then by placing the mysql_import_me.sql file into your data volume, the import command will be:

SET autocommit=0;
source /var/lib/mysql/mysql_import_me.sql;
commit;

If you really plan to migrate your data, to this before starting Hass with the YAML changes. Or you'll need to loose the data created in the meanwhile dropping the database to start a fresh one.

https://4pda-to.translate.goog/forum/index.php?showtopic=871505&st=42580&_x_tr_sl=ru&_x_tr_tl=en&_x_tr_pto=wapp#entry128823246

I try your script but it was not working for me, so i found other way to do the import.

Because of the Table column order have change in homeassistant, i had to change the order for getting this to work.

Here is my approach to migrate sqlite3 to mariaDB

Also auto-increment should be added to table event_types:

ALTER TABLE event_types MODIFY COLUMN event_type_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=35;

Look at wiki, there you find info. Den fre. 26. apr. 2024 kl. 16.46 skrev Petos Safarik ***@***.***>:

…

@petos commented on this gist. ________________________________ @JacobsenKim it's nice, but it's missing a docs how to use all these scripts. Something like from "for dummies" book series... — Reply to this email directly, view it on GitHub or unsubscribe. You are receiving this email because you were mentioned. Triage notifications on the go with GitHub Mobile for iOS or Android.