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

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!