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

Actually, is there any need to use sudo in
sudo ./sqlite3-to-mysql.sh ha-database_sqlite.dump.sql > mysql_import_me.sql ??
Beside of that it's awesome howto. Thx for that!

@petos I didn’t have to execute this in ages. You can try without sudo and report back if it worked, then I can adjust this gist. 😊

@PattaFeuFeu It was running w/out issues. I'm currently on "import" step (source). Will report back if the rest was w/out errors as well :)

Awesome gist! Thank you, i could kiss you!

I had to disable foreign key checks to be able to ALTER TABLE:

SET FOREIGN_KEY_CHECKS=0;

And i guess more tables have been introduced, as i had to add auto_increment to the following tables too, finding the value by the same method as above:

ALTER TABLE state_attributes MODIFY COLUMN attributes_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1161801;
ALTER TABLE statistics MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=719464;
ALTER TABLE statistics_meta MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=267;
ALTER TABLE event_data MODIFY COLUMN data_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=80299;
ALTER TABLE statistics_runs MODIFY COLUMN run_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=80587;
ALTER TABLE statistics_short_term MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8388035;
ALTER TABLE states_meta MODIFY COLUMN metadata_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2174;

As a note, it requires about 3-4x the size of the original database (Mine was 1.2GB) to migrate it, and on my 10500k on an NVMe SSD especially the 'source ' took 35 minutes, and several other steps took more than a couple of minutes too.

The energy-dashboard have been transferred and i'm now rocking MariaDB!

Hi!
How can i insert the database in the addon from home assistent?

@userosos You’ll need to import it into MariaDB. That’s separate from the HomeAssistant add-on—that one just allows you to use MariaDB with HomeAssistant.

You can check this guide to import data into a MariaDB database.

@userosos You’ll need to import it into MariaDB. That’s separate from the HomeAssistant add-on—that one just allows you to use MariaDB with HomeAssistant.

I can't get accept into database of the addon.

@userosos Not sure what you’re telling me here. 😊

Either way, I have not used the method outlined in this gist for years and now only use the included sqlite database, so I’m afraid I cannot support you much more than this.

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.

For me, also the table event_types had to be altered:

SELECT MAX(event_type_id) AS Count FROM event_types;
ALTER TABLE event_types MODIFY COLUMN event_type_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=87;

I wrote a migration script in Bash to migrate from SQLite to MariaDB.
It does everything step by step and currently is made for a docker based installation of HomeAssistant. But you can simply skip the steps where the script interacts with docker and only use the rest of the script.
It does everything else fully automatically, even setting the AUTO_INCREMENT values and converting the SQLite schema to MariaDB.

Here's the repository: https://github.com/NicolasGoeddel/ha-sqlite2mariadb