FHEM: Umzug von SQLite nach MySQL / MariaDB

Der Einfachheit halber beginnt man bei FHEM mit dem Logging von Daten entweder in Logfiles oder in einer SQLite. So erging es mir mit Letzterem. Die immer größer werdende Anzahl von Einträgen, sowie neue Möglichkeiten rund um Grafana bewegten mich zu einem Wechsel der Datenbank. Wie ich von SQLite nach MariaDB gewechselt bin möchte ich Euch kurz beschreiben und die nötigen Informationen auf einer Seite bereitstellen.

Bitte prüft vor jedem abgesetzten Befehl, ob alles passt. Ich bin für Schäden nicht verantwortlich. Erstellt bitte vorher immer Backups!

Installation von Paketen und Konfiguration der MariaDB/MySQL

Zu allererst solltet Ihr prüfen, ob alle notwendigen Pakete installiert sind. In meinem Fall waren es (etwas abweichend vom FHEM-Wiki) Folgende:

sudo apt install mariadb-server mariadb-client libdbd-mysql libdbd-mysql-perl

Bei den weiteren Schritten für die Einrichtung der MariaDB könnt Ihr Euch komplett ans FHEM Wiki halten. Außer, dass Ihr bei einigen Schritten prüfen müsst, ob wirklich MySQL oder MariaDB gemeint ist. Da ich bei mir ja schon die SQLite in Betrieb habe, musste ich an einigen Stellen abweichen um ein Überschreiben der bestehenden Konfiguration zu vermeiden. Der Vollständigkeit halber liste ich meine Schritte hier auf:

  1. Datenbankschema anlegen durch Import des SQLs /opt/fhem/contrib/dblog/db_create_mysql.sql
  2. Kopieren des Templates in den FHEM-Ordner
cp /opt/fhem/contrib/dblog/db.conf /opt/fhem/db_mysql.conf
  1. Beim Anlegen des Datenbankdevices und der Konfiguration in FHEM habe ich meine Namenskonvention angewendet:
defmod TE.DbLogMySQL DbLog ./db_mysql.conf .*:.*

Die Regexp zum Einschließen der Einträge solltet Ihr mit Bedacht wählen, da ab dem Zeitpunkt des defines alle Einträge geloggt werden. Ich habe daher die Regexp auf einen nicht vorhandenen Wert (demaya:demaya) geändert. Um zu prüfen, ob alles funktioniert ist es allerdings sinnvoll. Man muss nur darauf achten die Tabelle vor den weiteren Schritten wieder zu bereinigen.

Vorbereitung innerhalb von FHEM

Nachdem wir eine neue Datenbank angelegt haben in FHEM und auf diese zugegriffen werden kann, können wir uns die entsprechenden Devices zur Migration anlegen. Zur Migration an sich bietet sich das DbRep-Modul an. Die Migration über Dritthersteller-Tools (SQLite2MariaDB) ist aus meiner Sicht wesentlich umständlicher.

DbRep wird ein mal für die alte Datenbank (in meinem Fall myDbLog) definiert und mit den passenden Attributen versehen

defmod TE.DbRep DbRep myDbLog
attr TE.DbRep expimpfile /tmp/dbexport.csv

Wobei das Attribut „expimpfile“ den Pfad beschreibt, wohin der Dump zum Export / Import gespeichert werden soll.

Die selbe Prozedur führen wir auch für die neue Datenbank (TE.DbRepMySQL) durch:

defmod TE.DbRepMySQL DbRep TE.DbLogMySQL
attr TE.DbRepMySQL expimpfile /tmp/dbexport.csv

Start des Exports/Import von SQLite nach MariaDB/MySQL

Alle Devices sind angelegt und wir können mit dem Export der Daten starten. Vorher müssen wir allerdings schauen, dass wir keine dupliken Einträge in den Datenbanken generieren. Jetzt ist also ein geeigneter Zeitpunkt um das Logging in der alten Datenbank zu deaktivieren und in der neuen Datenbank zu aktivieren.

  1. Ich ändere die Konfiguration der alten Datenbank in einen Wert, der aller Voraussicht nach keinen Wert mehr loggen wird. Je nachdem wie Ihr Eure Logeinträge steuert, müsst Ihr ggf. andere Einstellungen wählen. Ich steuere es nur über die RegExp direkt am DbLog Device. Es empfiehlt sich die RegExp in meinem Fall für die neue DB zu kopieren.
defmod myDbLog DbLog ./db.conf asdf:jklö
  1. Im nächsten Schritt aktiviere ich die neue Datenbank mit der zuvor kopierten RegExp
defmod TE.DbLogMySQL DbLog ./db_mysql.conf .*:(temperature|valveposition|ValvePosition|humidity|power|co2|noise|pressure|fatFreeMass|fatMassWeight|fatRatio|heartPulse|weight|presence|kWh|KostenGestern|energy|measured-temp|desired-temp|cpu_temp|cpu_temp_avg|loadavg|cpu_freq|ram|eth0_diff|statEnergyCalcHourLast|statEnergyCalcDayLast|statEnergyCalcYearLast|statEnergyCalcMonthLast|DumpRowsHistory).*
  1. Erste Einträge sollten sich dann allmählich in der Datenbank finden. Dies kann über einen CountNbl in der Datenbank verifiziert werden.

FHEM DBLog count ausführen

  • Nun exportieren wir in der alten Datenbank die Einträge in die zuvor definierte CSV-Datei. Dies geschieht mit folgendem Befehl:
set TE.DbRep exportToFile

Den Fortschritt kann man am Besten über einen

ls -lh /tmp/dbexport.csv

auf der Kommandozeile beobachten<

  • Sobald der Export fertig ist lohnt sich ein Blick auf das sperrig benannte Reading „/ — / — — ROWS EXPORTED TO FILE –„. Hierin befindet sich die Anzahl der exportierten Datensätze. Diesen können wir später gut verwenden um den Import zu überprüfen.
  • Nun bleibt nur noch ein letzter Schritt: der Import in die neue Datenbank
set TE.DbRepMySQL importFromFile

Hier lässt sich der Fortschritt durch einen count auf die History-Datenbank beobachten. Ich habe in meinem Fall mit HeidiSQL den Fortschritt beobachtet. PS: HeidiSQL läuft in Wine 1a.

  • Sobald der Import vorbei ist, können wir die Anzahl der importierten Daten mit den exportierten Daten vergleichen. Beim Import wird die Anzahl im Reading „– ROWS IMPORTED FROM FILE –“ angezeigt. Sollte alles übereinstimmen ist der Import erfolgreich verlaufen.
  • Herzlichen Glückwunsch! Du hast deine FHEM-Daten von SQLite nach MariaDB/MySQL transferiert.

Nacharbeiten

Damit keine Dummheiten passieren, bietet es sich an zumindest im DbRep der neuen Datenbank den expimpfile wieder raus zu nehmen. Dies geschieht über

deleteattr TE.DbRepMySQL expimpfile

Weiterhin müssen sämtliche SVGs (Plots) angepasst werden. Um einen Überblick der vorhandenen SVGs zu bekommen, hilft ein beherzter

list type=SVG

Bei einer großen Anzahl von SVGs lohnt sich vermutlich ein Skript auf OS-Ebene (sed lässt grüßen). Bei mir war es noch überschaubar. Die Änderung beschränkt sich auf die Auswahl der neuen Datenbank und der Anpassung der Definition. Pro SVG ca. 5 Klicks. Nachdem sich alles etwas eingeschwungen hat, kann man nach und nach die alten DbRep- und DbLog-Devices deaktivieren. Hierzu bietet sich das Attribut disable an. Danach sieht die Übersicht der Datenbanken bei mir so aus:

Der Wechsel von SQLite nach MariaDB/MySQL ist mit ein paar Schritten abgeschlossen. Die alte Datenbank ist nicht mehr nötig und kann bei Gelegenheit gelöscht werden.

Fazit

Ich bereue den Schritt nicht und bin mit der MariaDB happy. Die neuen Möglichkeiten durch Grafana sind ein weiterer Schritt zum smarten Zuhause. Die Anleitung von DbRep hat mir super weitergeholfen. Leider war Sie sehr schwer zu finden (Wechsel von SQLite zu MariaDB). Ich hoffe, dass ich mit meinem Blogeintrag Euch weitergeholfen habe.

Weiterführende Informationen / Quellen: