Home Assistant Database MySQL vs. SQLite

Home Assistant uses SQLite as a database. In SQLite all data is stored in a simple file: home-assistant_v2.db. I had no problems with the database so far and I am very satisfied with the performance of Home Assistant. Nevertheless I wanted to try a MySQL database, because at least theoretically MySQL could achieve a better performance. In practice, however, I could not notice any difference. According to the manufacturer's page, SQLite is even recommended:

The default, and recommended, database engine is SQLite which does not require any configuration.

Source: www.home-assistant.io/integrations/recorder/

For a direct access to the SQLite database, see also: Home Assistant SQlite - Change statistics data

If you still want to use MySQL, you can adjust the configuration as follows:

docker-compose.yml

[+]

version: "3"
services:
  hass:
    image: homeassistant/home-assistant:stable
    container_name: home-assistant
    depends_on:
      - deconz
      - mysql
    environment:
      VIRTUAL_HOST: 'ha.domain.tld'
      VIRTUAL_PORT: '80'
      LETSENCRYPT_HOST: 'ha.domain.tld'
      LETSENCRYPT_EMAIL: 'admin@domain.tld'
    restart: always
    volumes:
      - ./ha:/config
      - /etc/timezone:/etc/timezone:ro
      - /etc/localtime:/etc/localtime:ro
    expose:
       - "80"
    ports: 
       - 8123:80
    networks:
            - default
            - backend
  mysql:
    image: 'mysql'
    container_name: ha-mysql
    environment:
        MYSQL_ROOT_PASSWORD: 'MyPassword'
        MYSQL_DATABASE: 'hass_db'
        MYSQL_USER: 'hass_user'
        MYSQL_PASSWORD: 'MyPassword'
    restart: always
    volumes:
        - './db:/var/lib/mysql'
        - './mysql.cnf:/etc/mysql/conf.d/mysql.cnf'
    healthcheck:
      test: ["CMD", "mysqladmin", "ping"]
    networks:
       - backend
  deconz:
    image: marthoc/deconz
    container_name: deconz
    environment:
      DECONZ_DEVICE: '/dev/ttyACM0'
      DECONZ_VNC_MODE: '1'
      DECONZ_VNC_PORT: '5900'
      DECONZ_VNC_PASSWORD: 'MyPassword4VNC'
    restart: always
    volumes:
      - ./deCONZ/:/root/.local/share/dresden-elektronik/deCONZ
      - /etc/timezone:/etc/timezone:ro
      - /etc/localtime:/etc/localtime:ro
    devices: 
      - /dev/ttyACM0
    ports:
       - 83:80
       - 5983:5900
    networks:
       - backend
networks:
  default:
    name: webproxy
    external: true
  backend:
    name: smart-home
    external: true

configuration.yml

...
recorder:
  db_url: mysql://hass_user:myPassword@ha-mysql/hass_db
...

Import existing entries: failed

The following command should import all entries from SQLite to MYSQL:

user@server:/var/homeassistant-Folder$ docker run --rm -it -v $(pwd):/db keinos/sqlite3 sqlite3 /db/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;\r$/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' | \
    docker exec -i ha-mysql mysql --default-character-set=utf8 -u hass_user -pMyPassword hass_db

Initially the command aborted relatively quickly, a look into the keinos/sqlite3 container with:

sqlite3 /db/home-assistant_v2.db .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
/**** ERROR: (8) attempt to write a readonly database *****/
ROLLBACK; -- due to errors

Cause: missing write permission in root folder of Home Assistant

ERROR 1451 (23000) at line 1: Cannot delete or update a parent row: a foreign key constraint fails (`hass_db`.`states`, CONSTRAINT `states_ibfk_2` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`))

docker stop home-assistant
docker exec -it ha-mysql /bin/bash
mysql -uhass_user -pMypwd 

mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES where  table_schema in ('hass_db');
mysql> SET FOREIGN_KEY_CHECKS = 1;

One step further, I failed because of this error:

ERROR 1062 (23000) at line 7250: Duplicate entry '1193928' for key 'events.PRIMARY'
                                                                                   write /dev/stdout: broken pipe