====== Middleware-Datenbank ======

Für die Speicherung der Daten wird eine Datenbank eingesetzt. Da nahezu jede Datenbank dazu verwendet werden kann, wird hier beispielhaft nur die Verwendung von MySQL beschrieben. MySQL ist eine kostenlose Datenbank für viele Betriebssysteme und kann auch auf den hier interessanten Kleinstrechnern unter Linux betrieben werden.

Das Datenbankschema für den Volkszähler ist einfach. Es besteht aus fünf Tabellen:
  * data: Tabelle der Datenwerte mit Zeitstempel und Zuordnung zum Messgerät/Sensor (channel)
  * entities: Die Messgeräte und ihre ID (UUID)
  * properties: Die Bezeichnung und andere Eigenschaften der Messgeräte wie Auflösung und ob sie öffentlich auswählbar sind, oder nicht
  * entities_in_aggregator: Zuordnung der Messgeräte zu Gruppen (parent-child-Struktur)
  * aggregate: Verdichtete Datenwerte, zum schnelleren Anzeigen eines längeren Zeitraums

<note important>Das Datenbankschema wird durch das für die middleware verwendete doctrine-framework automatisch generiert!</note>

Der Speicherbedarf eines Satzes in den Tabellen data und aggregate beträgt 24Byte.


===== Datenbank administrieren =====

Die beste Möglichkeit besteht darin über externen SQL-Client (z.B. [[https://www.heidisql.com/download.php|HeidiSQL]]) zu administrieren. Im Falle des RasperryPi-Images ist der Zugang von außen allerdings gesperrt und muss erst [[howto:raspberry_pi_image#einrichten|aktiviert]] werden.

Als Benutzername/Passwort dann entwender vz/demo für Lese- und Schreibzugriffe, vz-admin/secure für Löschzugriffe oder den lokalen Administratorzugang (root/raspberry) der in ''/etc/mysql/debian.cnf'' steht.

Phphmyadmin wäre eine Möglichkeit, aber mit Einschränkungen. Es hat Schwierigkeiten mit großen Datenbanken und ist auf dem Image nicht vorinstallliert.

===== Datenbank sichern =====

==== Mariadb Backup ====

Auf vielen Linuxdistributionen wird mysql inzwischen durch MariaDB zur Verfügung gestellt. Diese DB besitzt ein Backup-Tool welches in der Lage ist ein konsistentes Backup zu erzeugen, während die Datenbank beschrieben wird. 

Auf Debian und Ubuntu muss dieses Tool nachinstalliert werden:

  sudo apt install mariadb-backup

Ein vollständiges Backup wird erzeugt mittels

  sudo mariabackup --backup --target-dir=<Pfad> --user=root
  
Das Backup wird unter <Pfad> abgelegt. Für ein Restore muss das Backup "präpariert" und dann zurückgeschrieben werden. Dazu ist notwendig:

  sudo mariabackup --prepare --target-dir=<Pfad>
  
  # Datenbank stoppen (Ubuntu)
  sudo systemctl stop mariadb
  
  # Alle Datenbankdateien löschen (siehe /etc/mysql/my.cnf, "datadir" welcher Pfad)
  sudo rm -rf /var/lib/mysql
  
  # Backup zurückspielen 
  sudo mariabackup --copy-back --target-dir=<Pfad> --user=root
  
  # Besitzer der Dateien einrichten
  sudo chown -R mysql:mysql /var/lib/mysql
  
  # Datenbank starten
  sudo systemctl start mariadb 
  
Mit Hilfe des Tools sind darüber hinaus sowohl inkrementelle als auch Delta-Backups möglich. Weitere Informationen finden sich im [[https://mariadb.com/kb/en/full-backup-and-restore-with-mariabackup/|Referenzmanual von MariaDB (englisch)]]): 

==== Komplettbackup mit mysqldump ====
Die Standardanwendung
<code>mysqldump -uvz -pdemo volkszaehler > mysql_backup.sql</code>
Als Ziel taugt jedes gemountete Verzeichnis, vorzugsweise auf einem externen Datenträger.
<note important>Mysqldump verursacht eine sehr hohe Last bei der Datenbankanwendung und kann auf dem Raspberry den vzlogger beeinträchtigen!</note>
Eine solche Sicherung ist Klartext und kann recht Umfangreich werden. Speicherplatz spart man indem man die Sicherung direkt komprimiert.
<code>mysqldump -uvz -pdemo volkszaehler | bzip2 >mysql_backup.bz2</code>
Sqldump nimmt beim Erstellen allerdings keinerlei Rücksicht auf Abhängigkeiten in der Struktur. Das führt zu Abbruch des restore und erfordert viel manuelle Nacharbeit. Daher ist es ratsam die wichtigsten Tabellen separat zu sichern.
<code>mysqldump -uvz -pdemo volkszaehler entities > mysql_backup_entities.sql
mysqldump -uvz -pdemo volkszaehler entities_in_aggregator > mysql_backup_aggregator.sql
mysqldump -uvz -pdemo volkszaehler properties > mysql_backup_properties.sql</code>
Die sind von überschaubarer Größe und brauchen nicht gepackt werden.



==== mysqldump per ssh ====
Möchte man der SD-Karte im Rasperry unnötige Schreibzugriffe ersparen kann man auch über ssh auf einen Backuprechner sichern.
<code>destssh0=/home/backup/`uname -n`/
destssh2=${destssh0}/`date +%Y%m%d`
mysqldump -uvz -pdemo volkszaehler --single-transaction | gzip -c | ssh root@backuprechner "cat > $destssh2.mysql_backup.gz"</code>

Achtung: bevor das Script funktioniert, MUSS der SSH Fernzugriff ohne Passwort aktiviert sein. Ich habe hier auf die Anleitung [[http://mathias-kettner.de/lw_ssh_anmeldung_ohne_passwort.html]] zurückgegriffen.

==== Inkrementelles Backup mit dbcopy ====
Möchte man die Nachteile von mysqldump umgehen bietet sich [[software:tools:dbcopy|dbcopy]] an.

==== Backup per phpMyAdmin ====
Unter phpMyAdmin hat man grundsätzlich die Möglichkeit teilweise oder auch ganze Datenbanken zu sichern. Allerdings unterliegt PHP als Skriptsprache starken Restriktionen welche das Zurückspielen eines Backups über phpMyAdmin erschweren oder sogar unmöglich machen.

===== Datenbank zurückspielen =====
Um sicher zu gehen das keine Konflikte entstehen sollten während des Restore keine neuen Daten eintreffen. D.h. Middleware deaktivieren (vzlogger kann während dessen weiterlaufen) und die Cronjobs für die Aggregation abstellen. Auch alle Scripte stoppen die direkt auf die Datenbank zugreifen.
<code>mysql -uvz -pdemo volkszaehler < mysql_backup.sql</code>
<note>Das bezieht sich auf ein ungepacktes Backup!</note>
Falls ein Komplettbackup mit Störung abgebrochen wird kann das an Abhängigkeiten zu Tabellen liegen die noch nicht aufgebaut wurden. In dem Fall auf die separate Sicherung der betreffenden Tabelle zurückgreifen und zuerst wiederherstellen.

===== Datenbank nach CSV exportieren =====
Um die Daten sich in Excel oder Openoffice anzusehen, ist es sinnvoll diese nach CSV zu exportieren.
<code>
#!/bin/bash

database=volkszaehler
table=data

echo 'SELECT * FROM ' ${table} | mysql -B -uvz -pdemo ${database} | gzip -c >  ${database}_${table}_`date '+%Y-%m-%d-%H%M'`.txt.gz 

</code>