Migrate Home Assistant SQLite to MySQL in MariaDB container

Setting up MySQL database

• Create database named DB_HomeAssistant making sure it is of type utf8_bin
• Create user homeassistant and give it the correct privileges

Converting db file to query

sqlite3 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;$/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' > mysql.dump

This will read the db file, and transform it into one big query stored in the file mysql.dump. Copy the dump file into the mounted MariaDB volume (ie docker/mariadb/config/databases/)

Gain access to the MariaDB container with docker exec -it mariadb bash, type mysql DB_HomeAssistant --default-character-set=utf8 -u homeassistant -p and insert your password to start the query. The process might take a few minutes, but after that you will have your correct database entries in the new MySQL database.

Setting up Home Assistant

In the configuration.yaml file, add

recorder:
  db_url:  mysql://homeassistant:password@xxx.xxx.x.xxx:xxxx/DB_HomeAssistant?charset=utf8
  commit_interval: 2
  exclude:
    entities:
      - sun.sun

Restart Home Assistant