I setup home assistant around six months ago, and I have amassed a sqlite database around 700Mb. Today, the entire six months of history is in the Sqlite database which is causing home assistant to hang for minutes when I try to look at history, even if the requested history is only today.

For other stuff, I’m happily using MariaDB and I have a beefy server which is much faster than the Raspberry Pi SD Card which hosts my Home Assistant and its sqlite3 database.  I’m also experimenting with the Google Cloud SQL Database and Amazon Webservice’s RDS. My goal is to get away from the SQLite DB into MariaDB, then easily in the future if I choose a cloud hosted database it’s a simple change. I couldn’t find any specific instructions on how to migrate from sqlite3 for home assistant.

I used the following procedure, which was a bit of a hackery, but ended up working in the end. Since it only needs to be done once, I’m happy with the result. Read on for a detailed description of each step:

1. Stop the application
2. Dump the sql lite + convert to mysql import
3. Import into MariaDB
4. Adjust missing fields
5. Re-configure home-assistant, start and done!

The above procedure was found via trial and error and worked for me. Your results may vary, so always save a copy of your sqlitedb in case things go wrong. Full details are below which I carried out with Home-Assistant version 0.38. This should work for future versions as well, though additional fields and constraints may require a few minor adjustments.

1. Stop home assistant
2. Export the database: sqlite3 home-assistant_v2.db .dump > sqlite3_dbfile_.dump.sql
3. Convert the dump:  convert_db.sh sqlite3_dbfile_.dump.sql > mysql_importme.sql
4. Setup a database in MySQL and grant a user rights
   1. create database homeassistant;
   2. grant all privileges on homeassistant.* to someuser identified by somepass
5. Import the dump into MySQL: mysql -u someuser -p -h db-hostname  homeassistant < mysql_importme.sql
6. Change home assistant to use mysql: recorder:
   db_url: mysql://someuser:somepass@db-hostname/homeassistant
7. Annoyingly, the sqlite conversion script doesn’t set auto increment.
   1. select max(run_id) from recorder_runs; <– This tells you how many IDs you have in recorder runs.  I had 223.
   2. ALTER TABLE recorder_runs MODIFY COLUMN run_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=224; <– 224 is one more than the number from above
8. There’s a foreign key constraint which prevents doing the above procedure to the events table. Remove the foreign key constraint, calculate the number and add it
   1. alter table states drop foreign key states_ibfk_1;
   2. select max(event_id_ from events; <– returned 817514 for me, so add one to this
   3. ALTER TABLE events MODIFY COLUMN event_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=817515;
   4. ALTER TABLE states ADD CONSTRAINT 'states_ibfk_1' FOREIGN KEY ('event_id') REFERENCES events ('event_id');
9. Start home-assistant and test