This post quickly documents some notes around my process of migrating Home Assistant's sqlite3 database to a containerised MySQL database.

I run all of my home server infrastructure in Docker containers as I like the isolation and distribution model that they provide.

The default sqlite3 database used by Home Assistant does not scale well to storing a large number of events and state changes, which makes using the History or Logbook panes of the Home Assistant interface slow to use. MySQL performs significantly better at this, at the expense of having to run a separate Docker container to handle the database.

Migration process

The overall process for migrating from the default sqlite3 database to a MySQL looks like the following:

1. Start a containerised instance of MySQL 8.0
2. Stop Home Assistant
3. Configure HA to point at the containerised MySQL instance
4. Start HA, allow it to create its database schema, then stop it again
5. Truncate all tables in the MySQL database
6. Dump sqlite3 database into an SQL file
7. Transform the SQL file from sqlite3-flavour to MySQL-flavour
8. Import the SQL file into MySQL
9. Start HA

Largely following this guide: https://gist.github.com/seidler2547/93012edf3c7a2414ec1d9a8ebbc9c1a6

Below are my "notable points" relating to steps in the above process.

1. Start a containerised instance of MySQL 8.0

There are a choice of Docker images available - mysql:8.0 and mysql/mysql-server:8.0. The difference is apparently that the image with the longer name is an Oracle fork of the "official" mysql image, which uses Red Hat as the base image. The "official" image uses Debian as the base.

In practice, I found that when I tried to run the Oracle-maintained image, it didn't handle being run with a different uid/gid well (didn't have permission to write temporary files). The "official" mysql image didn't have this problem.

4. Start HA to allow it to create database schema

Home Assistant wouldn't connect to MySQL on startup, and it threw an error into the logs containing the following line:

Plugin caching_sha2_password could not be loaded: Error loading shared library lib/mariadb/plugin/caching_sha2_password.so: No such file or directory  

This led me to a post on the Home Assistant forum, which pointed me in the right direction. MySQL 8.0 changed the default authentication method to one which is more secure, and as part of the MySQL protocol client-server handshake, the server tells the client which auth method to use. The version of SQLAlchemy ORM used in Home Assistant doesn't yet support the new caching_sha2_password plugin, so the easiest solution is to modify the MySQL server settings to default to using the previous mysql_native_password method, which Home Assistant does support.

This can be done by modifying the MySQL container start command to include the --default-authentication-plugin=mysql_native_password flag.

7+8. Transform SQL file from sqlite3 format and import into MySQL

As I didn't have any sqlite tooling installed locally on my server, I used a Docker image containing sqlite to dump the database contents as SQL.

Importing this into MySQL involved transforming the SQL format first, and with a couple of small modifications to the gigantic shell command included in the guide linked above, I was able to do this in one go:

# not shown: cd into home assistant directory
docker run --rm -it -v $(pwd):/db keinos/sqlite3 sqlite3 /db/home-assistant_v2.db .dump | \  
    sed -re 's/^PRAGMA .+OFF/SET FOREIGN_KEY_CHECKS=0;SET UNIQUE_CHECKS=0/' \
        -e 's/^CREATE INDEX .+//' \
        -e 's/^BEGIN TRANSACTION;\r$/SET autocommit=0;BEGIN;/' \
        -e '/CREATE TABLE/,/\);/ d' \
        -e 's/^INSERT INTO "([^"]+)"/INSERT INTO \1/' \
        -e 's/\\n/\n/g' | \
    perl -pe 'binmode STDOUT, ":utf8";s/\\u([0-9A-Fa-f]{4})/pack"U*",hex($1)/ge' | \
    docker exec -i <MYSQLCONTAINERID> mysql --default-character-set=utf8 -u hass -p<YOURMYSQLPASSWORD> homeassistant

This command took about 30 minutes to run, but completed the full migration from a SQLite file to a MySQL database.