One-liner to convert an existing Home-Assistant SQLite database to MySQL

convert_ha_sqlite2mysql.sh

# prerequisites:
## install software
apt install mariadb-server libmariadbclient-dev sqlite3
## install mysqlclient in virtualenv
su -c 'homeassistant/bin/pip3 install mysqlclient --upgrade' -l homeassistant
## create database
mysql -e 'CREATE SCHEMA IF NOT EXISTS `hass_db` DEFAULT CHARACTER SET utf8'
## create user (use a safe password please)
mysql -e "CREATE USER 'hass_user'@'localhost' IDENTIFIED BY 'hass_pw'"
mysql -e "GRANT ALL PRIVILEGES ON hass_db.* TO 'hass_user'@'localhost'"
mysql -e "GRANT usage ON *.* TO 'hass_user'@'localhost'"
# stop HA now
systemctl stop home-assistant # or whatever it is for you
# now edit the configuration to point hass to mysql
nano ....
# now start HA once and stop it right away, we only want it to create the tables:
systemctl start home-assistant # or whatever it is for you
sleep 20
systemctl stop home-assistant # or whatever it is for you
# now empty the tables
mysql hass_db -e 'delete from events;delete from recorder_runs; delete from schema_changes; delete from states;'

# this is the actual conversion:
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 hass_db --default-character-set=utf8 -u hass_user -p

Thanks for this!

The first startup is a bit longer but it works like a charm, thanks!

Thanks, did need to change a few things for my setup. The conversion part was very helpful

Why do you remove the indexes?

Because the syntax is different and the indices are created by HA when it starts up.

Why don't you use utf8mb4 instead utf8?

mysql --binary-mode=1 if you get ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected

Thanks :-)

this command return error
mysql hass_db -e 'delete from events;delete from recorder_runs; delete from schema_changes; delete from states;'

Cannot delete or update a parent row: a foreign key constraint fails (hass_db.states, CONSTRAINT states_ibfk_1 FOREIGN KEY (event_id) REFERENCES events (event_id))

This worked for me, however I had to use utf8mb4 encoding, and I also had to add two extra columns. Something like:

alter table states
	add context_id character(36) null;

alter table states modify context_id char(36) null after created;

alter table states
	add context_user_id character(36) null;

alter table states modify context_id char(36) null after context_id;

and the one-liner was

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, ":utf8mb4";s/\\u([0-9A-Fa-f]{4})/pack"U*",hex($1)/ge' \
| mysql homeassistant --default-character-set=utf8mb4 -u xxxx -p 

Aside from that, I had problems converting unicode characters - a lot of entries in states had unicode \u00b0C that was copied as ?C for example, but an update/replace query sorted that out..

Thank you for this, very helpful.
I basically followed your guide, but I did have trouble with the degrees symbol from my temperature reporting.

To get past that, I replaced the last line calling myql with a redirect to a file something like this:
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, ":utf8mb4";s/\\u([0-9A-Fa-f]{4})/pack"U*",hex($1)/ge' > output.sql

This is how I discovered the error was coming from the degrees symbol. I wasn't concerned about that data so I just grep'd those lines out into a cleansed version like this:
grep -v "C\"" output.sql > cleansed.sql
I was too lazy to figure out the encoding of the degrees symbol to use it on the command line, so I just looked for C" to find these lines

After that it was simply:
cat cleansed.sql | mysql -h 127.0.0.1 homeassistant --default-character-set=utf8mb4 -u xxxx -p

One other note, I did have to specify the hostname in the mysql command as shown above to get it to connect to MariaDB in a docker container.

@kaitlinsm I've just done this approach for to migrate my recorder DB to MariaDB. I've got an current hass.io docker install. My states table doesn't include the fields context_id or context_user_id , so I guess that these have been added by some customisation.

UTF-8 encoding uses multiple bytes per UTF character with core ASCII taking one and emoticons 4. The MySQL / MariaDB implementation of the "utf-8" is actually an invalid implementation in that it can't handle the 4 byte UTF codes correctly; however the name has been retained for backwards compatibility. "utf8mb4" really means utf-8 correctly implemented. AFAIK, perl utf8 output loads correctly into utf8mb4 collated fields. I certainly couldn't find any probs with my field names (including ° C).

A couple of other points for HassOS users:

• Don't move reporter to mariaDB if your data partition is on microSD. I use an RPi4 for my HA system, but my data partition is on a USB3 mapped M.2-SATA SSD which is fine for the InnoDB engine.
• Doing this is a lot easier if you have SSH access to the kernel (port 22222) and the HA CLI (port 22) containers.
• sqllite3 isn't available in the mariadb container so you need to do the unload by SSHing to the kernel. The /config folder is pathed at /mnt/data/supervisor/homeassistant in the kernel. So after stopping the supervisor, I did:

# CONFIG=/mnt/data/supervisor/homeassistant
# MARIADB=`docker ps | grep mariadb | cut 1-12`
# sqlite3 $CONFIG/home-assistant_v2.db .dump > $CONFIG/ha.sql
# docker cp $CONFIG/ha.sql $MARIADB:/ha.sql
# rm $CONFIG/ha.sql
# docker exec -it $MARIADB bash

• This last command puts you into an interactive session in the MariaDB container with the unload file at /ha.sql. This container does have a proper sed and perl 5.30.
• I added my DB pwd and DB connection URL to my secrets.yaml, installed the mariaDB container, configured the homeassistant password and started mariaDB to create the homeassistant DB. I did a ha core stop added the db_url: !secret mariadb_url to my recorder entry, and did a ha core start <wait> ha core stop to allow HA to create the reporter tables in mariaDB.
• You can log onto the DB with mysql -u root homeassistant to truncate all 4 tables. (You will need to SET FOREIGN_KEY_CHECKS=0 first otherwise truncating states will fail.)
• Edit the SQL to make mariaDB conformant; run though perl filter, and reload into mysql, then cleanup crap sql files and exit the container.
• The recorder history should now be available once you restart the HA core.

I used this perl command and had no issues with the utf8 encoding of any symbols, degrees, cosine, ...

perl -pe 'use utf8; use open qw(:std :utf8); binmode STDOUT, ":utf8mb4";s/\\u([0-9A-Fa-f]{4})/pack"U*",hex($1)/ge'

Im very novice here...
I'm using HAOS and have MariaDB addon installed.
It looks like the instructions install mariaDB at HAOS root? Im not sure how to SSH into the addon.

Is this the correct way to migrate for HAOS users?
Is it possible for detailed steps for a real novice HAOS users, like myself?

Aside from that, I had problems converting unicode characters - a lot of entries in states had unicode \u00b0C that was copied as ?C for example, but an update/replace query sorted that out..

@kaitlinsm Could you give an example of what this query looked like?

I ended up using the sqlite3-to-mysql python script
So far it seems to work well.
Also utf8mb4 seems to be default on my ubuntu server setup

# prerequisites:
## install software
apt install mariadb-server
## create database
mysql -e 'CREATE SCHEMA IF NOT EXISTS `hass_db` DEFAULT CHARACTER SET utf8mb4'
## create user (use a safe password please)
mysql -e "CREATE USER 'hass_user'@'localhost' IDENTIFIED BY 'hass_pw'"
mysql -e "GRANT ALL PRIVILEGES ON hass_db.* TO 'hass_user'@'localhost'"
mysql -e "GRANT usage ON *.* TO 'hass_user'@'localhost'"
# stop HA now
docker stop homeassistant # or whatever it is for you
# now edit the configuration to point hass to mysql
nano ....
# convert the sqlite db to mysql
sqlite3mysql -f ./home-assistant_v2.db -d hass_db -u hass_user -p
# start HA
docker start homeassistant # or whatever it is for you

I ended up using the sqlite3-to-mysql python script So far it seems to work well. Also utf8mb4 seems to be default on my ubuntu server setup

# prerequisites:
## install software
apt install mariadb-server
## create database
mysql -e 'CREATE SCHEMA IF NOT EXISTS `hass_db` DEFAULT CHARACTER SET utf8mb4'
## create user (use a safe password please)
mysql -e "CREATE USER 'hass_user'@'localhost' IDENTIFIED BY 'hass_pw'"
mysql -e "GRANT ALL PRIVILEGES ON hass_db.* TO 'hass_user'@'localhost'"
mysql -e "GRANT usage ON *.* TO 'hass_user'@'localhost'"
# stop HA now
docker stop homeassistant # or whatever it is for you
# now edit the configuration to point hass to mysql
nano ....
# convert the sqlite db to mysql
sqlite3mysql -f ./home-assistant_v2.db -d hass_db -u hass_user -p
# start HA
docker start homeassistant # or whatever it is for you

This method worked for me. Awesome. You're a star.

If you are getting error:

2022-07-12 09:32:29 ERROR 2003: Can't connect to MySQL server on 'localhost:3306' (111 Connection refused)
2003: Can't connect to MySQL server on 'localhost:3306' (111 Connection refused)

Let´s try this way:

sqlite3mysql -f ./config/home-assistant_v2.db -d hass_db -u hass_user -h core-mariadb -p

I ended up using the sqlite3-to-mysql python script So far it seems to work well. Also utf8mb4 seems to be default on my ubuntu server setup

# prerequisites:
## install software
apt install mariadb-server
## create database
mysql -e 'CREATE SCHEMA IF NOT EXISTS `hass_db` DEFAULT CHARACTER SET utf8mb4'
## create user (use a safe password please)
mysql -e "CREATE USER 'hass_user'@'localhost' IDENTIFIED BY 'hass_pw'"
mysql -e "GRANT ALL PRIVILEGES ON hass_db.* TO 'hass_user'@'localhost'"
mysql -e "GRANT usage ON *.* TO 'hass_user'@'localhost'"
# stop HA now
docker stop homeassistant # or whatever it is for you
# now edit the configuration to point hass to mysql
nano ....
# convert the sqlite db to mysql
sqlite3mysql -f ./home-assistant_v2.db -d hass_db -u hass_user -p
# start HA
docker start homeassistant # or whatever it is for you

This is exactly what i need, work without any problem (debian 11) (HA 2024.10.3)

sqlite3mysql version 2.3.1
sqlite3mysql --sqlite-file home-assistant_v2.db --mysql-user user --mysql-password pass --mysql-database dbname

ha config:
recorder:
db_url: mysql://user:pass@ip/dbname?charset=utf8mb4
auto_purge: false

Thanks!