Homeassistant - Datenbank auf einen eigenen Server auslagern
Einleitung:
Ich baue seid geraumer Zeit mein Smart Home auf und mit einer der
Hauptgründe für eben dieses, sind für mich ganz simpel gesagt:
Statistiken. Diagramme aller Art, Analysen und Auswertungen über mein
Leben erstellen, ist für mich ein sehr interessanter Bereich, um mich
und meine vernderung grafisch darstellen zu können. Kostenminimierung
und Komfort stehen aber selbstverständlich auch auf meiner Agenda. Um
das alles umsetzen zu können bzw. prüfen zu können (im Fall von
Kostenminimierung), benötige ich jedoch Daten. Diese Daten werden im
Normalfall in einer SQLite3 Datenbank auf dem Homeassistant Server
selbst gespeichert.
Nun habe ich schon öfter gelesen,d ass genau
diese Datenbank gerne mal Fehler hat und dann nur durch ein Backup
wiederhgergestellt werden kann. In meinem Fall werden alle 24h Backups
erstellt, was bei mir im worstcase 23h Datenverlust bedeutet.
Grundsätzlich für meinen Rahmen als Privatperson nicht schlimm, aber
trotzdem ist das für mich zuviel.
Dementsprechend möchte ich meine
Datenbank gerne auslagern, das hat auch noch weitere Vorteile, denn auch
wenn mein Homeassistant einmal nicht mehr funktionieren sollte, sind
meine Daten immer da. Ich kann ohne Probleme die Homeasisstant VM
löschen und neu aufsetzen, die Daten bleiben durch einen externen DB
Server erhalten. Weiterhin können dadurch auch Fremdprogramme auf diese
Daten zugreifen und analysieren.
Zuletzt kann ich auch ein
hausgemachtes Problem, durch die Auslagerung lösen, nämlich
Speichermangel. Meine Homeassistant Instanz hat 32 GB Speicher zu Anfang
zugewiesen bekommen und da ich danach neue Maschinen erstellt habe, ist
eine Vergrößerung nur mit Aufwand möglich, wenn ich den gesamten
Speicherbereich verschiebe. Ohne meine Datenbank spare ich (nach drei
Monaten Laufzeit) bereits 4GB ein. Daher zeige ich im folgenden, wie die
SQLite3 Datenbank auf einen Maria DB Server unter Debian migriert
werden kann.
Homeassistant Datenbank exportieren
Zuerst muss die Homeasistant Datenbank exportiert werden. Dafür
können zahlreiche Methoden verwendet werden, unter anderem kann die
Datenbank einfach aus dem GUI mit einem Addon heruntergeladen werden,
jedoch wollte ich die Datenbank exportieren, währenmd Homeassistant
nicht läuft, daher habe ich den Server einfach heruntergefahren und mein
Image (im qcow2 Format) mit einer VM gemounted und die Datei auf ein
internes SMB Laufwerk kopiert um damit weiterarbeiten zu können. Je nach
Installationstyp, kannst du den Homeassistant Dienst auch einfach
stoppen und ganz normal auf deinem Filesystem navigieren.
Ich nutze die Homeassistant OS Version unter Unraid als VM und hatte daher einfach die VM gestoppt und wie in diesem Beitrag gezeigt, die Datei exportiert.
Datenbank Server vorbereiten
In meinem Fall nutze ich als Datenbank Server einen “externen” Server, welcher ebenfalls virtualisiert auf meinem Unraid läuft (mit “extern” meine ich, dass nicht die selbe VM wie die HASS Maschine verwendet wird).
Ich gehe hier davon aus, dass bereits ein MariaDB Server (oder Ähnliches) installiert wurde und eine Datenbank mit passendem User erstellt wurde, wie sowas gemacht wird, ist breit im Internet beschrieben, runtergebrochen auf einen Satz wäre es aber nicht viel mehr als “apt install mariadb-server”, die Anpassung des Netzwerks und dann im MariaDB CLI ->
create database homeassistant;
create user 'homeassistant'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES on homeassistant.* to homeassistant@'%';Im Normalfall ist die MariaDB Installation lokal gebunden und kann nicht von aussen erreicht werden. Das möchte ich bei mir ändern, da da mein Homeassistant später über das Netzwerk auf diesen zugreift.
Dafür passe ich die MariaDB Server config an (Datei: /etc/mysql/mariadb.conf.d/50-server.cnf)
Hier muss der Abschnitt “[mysqld]” angepasst werden, da wir dem
Daemon Dienst sagen wollen, dass er nicht nur auf localhost sondern auch
auf die IP Adresse reagieren soll.
Die angepasste Konfigurationszeile sieht dann bei mir wie folgt aus:
[mysqld]
...
bind-address = 192.168.2.16Danach starte ich den Dienst durch den Befehl “sudo service mariadb restart” neu.
Übertragen der Daten von Homeassistant
WARNUNG: Wie öfter in verschiedenen Foren zu lesen ist, gibt es
derzeit Probleme mit dem Energy Dashboard, wenn eine andere Datenbank
verwendet wird.
In meinem Fall werden die Strompreise derzeit nicht
mehr korrekt kalkuliert, ansonsten funktioniert aber alles, weshalb ich
weiter auf diese Lösung setzen werde und nebenbei schaue, wo das Problem
liegt. Derzeit ist eine Vermutung vom Forum, dass die Daten des
Energydashboards an einer weiteren Stelle gespeichert, aber nicht
korrekt migriert werden. Falls euch das Risiko zu hoch ist, solltet ihr
hier abbrechen! – oder die Daten nicht übertragen, bei Neustart tritt
das Problem wohl nicht auf.
Wir nutzen nun die Datenbank, welche wir von Homeassistant exportiert haben.
Zur übertragung der Daten nutze ich ein Python Script, welches bei mir besser, als das GitHub Projekt funktioniert hat. Zur Installation kann einfach dieses Python Projekt verwendet werden. Installierbar mit
pip install sqlite3-to-mysqlnach der Installation kann auch schon die Übertragung stattfinden.
sqlite3mysql -f <<SQLite Datenbank>> -d <<Database>> -u <<User>> -h <<Host>> -p -t -i DEFAULTIch musste meiner VM insgesamt 8 GB RAM für die Übertragung zuweisen, da das Script ansonsten Out of Memory ging, das sollte ggf. beachtet werden, falls ihr größere Datenbanken habt. Meine war ca. 2,2GB Groß).
Nach der Übertragung müssen die Table angepasst werden, dafür habe ich von diesem Post
das vorgehen kopiert. – KEIN COPY PASTE SCRIPT! – LESEN!!!!! – ansonsten zerschießt ihr euch die Daten!
update events set event_data = REPLACE(event_data, '`', '"');
update states set attributes = REPLACE(attributes, '`', '"');
update state_attributes set shared_attrs = REPLACE(shared_attrs, '`', '"');
update event_data set shared_data = REPLACE(shared_data, '`', '"');
# Remove Foreign Key Constraints
ALTER TABLE `states` DROP FOREIGN KEY `states_ibfk_1`;
ALTER TABLE `states` DROP FOREIGN KEY `states_ibfk_2`;
ALTER TABLE `states` DROP FOREIGN KEY `states_ibfk_3`;
ALTER TABLE `statistics_short_term` DROP FOREIGN KEY `statistics_short_term_ibfk_1`;
ALTER TABLE `statistics` DROP FOREIGN KEY `statistics_ibfk_1`;
ALTER TABLE `events` DROP FOREIGN KEY `events_ibfk_1`;
# THIS IS NOT A SCRIPT TO COPY AND PASTE, PLEASE, READ THE FOLLOWING TWO LINES AND EXECUTE EACH STATEMENT REPLACING THE 'N+1' WITH THE VALUE FROM PREVIOUS SELECT STATEMENT
# alter the primary key in the tables to use auto_increment, starting from the last run id +1.
# MANUALLY run the following 'SELECT' statements and replace 'N+1' with the output of the select statment adding 1 (e.g., 1+1=2).
select max(run_id) from recorder_runs;
alter table recorder_runs modify column run_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;
select max(event_id) from events;
alter table events modify column event_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;
select max(state_id) from states;
alter table states modify column state_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;
SELECT MAX(change_id) FROM schema_changes;
ALTER TABLE schema_changes MODIFY COLUMN change_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;
SELECT MAX(id) FROM statistics;
ALTER TABLE statistics MODIFY COLUMN id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;
SELECT MAX(id) FROM statistics_meta;
ALTER TABLE statistics_meta MODIFY COLUMN id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;
SELECT MAX(run_id) FROM statistics_runs;
ALTER TABLE statistics_runs MODIFY COLUMN run_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;
SELECT MAX(id) FROM statistics_short_term;
ALTER TABLE statistics_short_term MODIFY COLUMN id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;
SELECT MAX(attributes_id) FROM state_attributes;
ALTER TABLE state_attributes MODIFY COLUMN attributes_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;
SELECT MAX(data_id) FROM event_data;
ALTER TABLE event_data MODIFY COLUMN data_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;
# Add back the foreign key constraints
ALTER TABLE `states` ADD CONSTRAINT `states_ibfk_1` FOREIGN KEY (`old_state_id`)REFERENCES `states`(`state_id`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `states` ADD CONSTRAINT `states_ibfk_2` FOREIGN KEY (`event_id`)REFERENCES `events`(`event_id`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE `states` ADD CONSTRAINT `states_ibfk_3` FOREIGN KEY (`attributes_id`)REFERENCES `state_attributes`(`attributes_id`);
ALTER TABLE `statistics` ADD CONSTRAINT `statistics_ibfk_1` FOREIGN KEY (`metadata_id`)REFERENCES `statistics_meta`(`id`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE `statistics_short_term` ADD CONSTRAINT `statistics_short_term_ibfk_1` FOREIGN KEY (`metadata_id`) REFERENCES `statistics_meta`(`id`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE `events` ADD CONSTRAINT `events_ibfk_1` FOREIGN KEY (`data_id`) REFERENCES `event_data` (`data_id`);Nachdem das nun abgeschlossen ist, passen wir schlussendlich noch Homeassistant an und fügen in
die configuration.yaml folgende Zeile ein:
recorder:
db_url: mysql://<<user>>:<<passwort>>@<<host>>/<<Datenbank>>
# db_url: !secret mariadb_url //alternative, hier werden die Daten in der "secrets.yaml" Datei gespeichertFalls ihr die 2. Variante mit der Secrets.yaml nutzt, dann müsst ihr da folgende Zeile einfügen:
mariadb_url: mysql://<<user>>:<<passwort>>@<<host>>/<<Datenbank>>Zuletzt wird Homeassistant nocheinmal neugestartet und nun sollte Homeassistant neue Daten auf dem MariaDB Server speichern. Um dies zu prüfen könnt ihr in der MariaDB CLI folgendes eingeben:
mysql > show processlist;Bei der ausgegebenen Tabelle sollte unter der Spalte “db” zumindest eine Verbindung mit der Tabelle “<<euer Datenbankname>>” bestehen. Bei mir sind es 4.
Back…