Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
One-liner to convert an existing Home-Assistant SQLite database to MySQL
# 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
@MarMed

This comment has been minimized.

Copy link

MarMed commented Jun 23, 2019

Thanks for this!

@Pofilo

This comment has been minimized.

Copy link

Pofilo commented Aug 10, 2019

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

@pve84

This comment has been minimized.

Copy link

pve84 commented Sep 25, 2019

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

@elbow

This comment has been minimized.

Copy link

elbow commented Dec 29, 2019

Why do you remove the indexes?

@seidler2547

This comment has been minimized.

Copy link
Owner Author

seidler2547 commented Dec 29, 2019

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

@Pepetor

This comment has been minimized.

Copy link

Pepetor commented Jan 25, 2020

Why don't you use utf8mb4 instead utf8?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.