FEsaCTs3

1# prerequisites:
2## install software
3apt install mariadb-server libmariadbclient-dev sqlite3
4## install mysqlclient in virtualenv
5su -c 'homeassistant/bin/pip3 install mysqlclient --upgrade' -l homeassistant
6## create database
7mysql -e 'CREATE SCHEMA IF NOT EXISTS `hass_db` DEFAULT CHARACTER SET utf8'
8## create user (use a safe password please)
9mysql -e "CREATE USER 'hass_user'@'localhost' IDENTIFIED BY 'hass_pw'"
10mysql -e "GRANT ALL PRIVILEGES ON hass_db.* TO 'hass_user'@'localhost'"
11mysql -e "GRANT usage ON *.* TO 'hass_user'@'localhost'"
12# stop HA now
13systemctl stop home-assistant # or whatever it is for you
14# now edit the configuration to point hass to mysql
15nano ....
16# now start HA once and stop it right away, we only want it to create the tables:
17systemctl start home-assistant # or whatever it is for you
18sleep 20
19systemctl stop home-assistant # or whatever it is for you
20# now empty the tables
21mysql hass_db -e 'delete from events;delete from recorder_runs; delete from schema_changes; delete from states;'
22
23# this is the actual conversion:
24sqlite3 home-assistant_v2.db .dump \
25| sed -re 's/^PRAGMA .+OFF/SET FOREIGN_KEY_CHECKS=0;SET UNIQUE_CHECKS=0/' \
26       -e 's/^CREATE INDEX .+//' \
27       -e 's/^BEGIN TRANSACTION;$/SET autocommit=0;BEGIN;/' \
28       -e '/^CREATE TABLE .+ \($/,/^\);/ d' \
29       -e 's/^INSERT INTO "([^"]+)"/INSERT INTO \1/' \
30       -e 's/\\n/\n/g' \
31| perl -pe 'binmode STDOUT, ":utf8";s/\\u([0-9A-Fa-f]{4})/pack"U*",hex($1)/ge' \
32| mysql hass_db --default-character-set=utf8 -u hass_user -p