Home Assistant Datenbank MySQL vs. SQLite

This page is also available in English

Home Assistant verwendet als Datenbank SQLite. In SQLite werden alle Daten in eine einfache Datei gespeichert: home-assistant_v2.db. Ich hatte mit der Datenbank bis dato keine Probleme und bin mit der Performance von Home Assistant sehr zufrieden, dennoch wollte ich eine MySQL-Datenbank versuchen, denn zumindest theoretisch könnte MySQL eine bessere Performance erreichen. In der Praxis konnte ich aber keinen Unterschied feststellen. Laut der Herstellerseite wird SQLite sogar empfohlen:

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

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

Wer dennoch MySQL verwenden will, kann die Konfiguration wie folgt anpassen:

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:
    external:
      name: webproxy
  backend:
    external:
      name: smart-home

configuration.yml

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

Bestehende Einträge importieren: gescheitert 

Folgender Befehl soll alle Einträge von SQLite nach MYSQL Importieren:

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

Anfangs hat der Befehl relativ schnell abgebrochen, ein Blick in den keinos/sqlite3-Container mit: 

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

Ursache: fehlende Schreibberechtigung im Root-Ordner von 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;

Einen Schritt weiter, bin ich an diesem Fehler gescheitert:

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

siehe auch: www.alexsilcock.net/notes/migrating-home-assistant-from-sqlite3-to-mysql-8-0/ und gist.github.com/seidler2547/93012edf3c7a2414ec1d9a8ebbc9c1a6

positive Bewertung({{pro_count}})
Beitrag bewerten:
{{percentage}} % positiv
negative Bewertung({{con_count}})

DANKE für deine Bewertung!

Veröffentlichung: 06.11.2021 von Bernhard


Fragen / Kommentare


Durch die weitere Nutzung der Seite stimmst du der Verwendung von Cookies zu Mehr Details