DbLog-MySQL: Unterschied zwischen den Versionen
Andies (Diskussion | Beiträge) |
K (Sichtung / Korrektur der letzten Änderungen) |
||
(4 dazwischenliegende Versionen von 2 Benutzern werden nicht angezeigt) | |||
Zeile 4: | Zeile 4: | ||
== Server version: 10.1.40-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04 == | == Server version: 10.1.40-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04 == | ||
=== db.conf === | === db.conf === | ||
{{Randnotiz|RNTyp=y|RNText='''Anmerkung''': MariaDB10 nutzt nicht Port 3306 sondern 3307. Dies ist zum Beispiel bei Synology NAS der Fall.}} | |||
[[DbLog]] wird durch zwei verschiedene Einträge aktiviert/definiert. In einer Datei namens '''db.conf''' werden die Parameter für eine Verbindung zur Datenbank (host, username, password, etc.) hinterlegt. Diese Datei kann in einem beliebigen Verzeichnis angelegt werden. Für eine MariaDB-Datenbank sieht die db.conf folgendermaßen aus: | [[DbLog]] wird durch zwei verschiedene Einträge aktiviert/definiert. In einer Datei namens '''db.conf''' werden die Parameter für eine Verbindung zur Datenbank (host, username, password, etc.) hinterlegt. Diese Datei kann in einem beliebigen Verzeichnis angelegt werden. Für eine MariaDB-Datenbank sieht die db.conf folgendermaßen aus: | ||
Zeile 97: | Zeile 98: | ||
== Beispiel: Anlegen und Nutzung einer MariaDB-Datenbank == | == Beispiel: Anlegen und Nutzung einer MariaDB-Datenbank == | ||
Anstatt nano kann jeder andere kompatible Editor verwendet werden. Weiterhin bitte beachten, dass die hier genannten Befehle teilweise root-Rechte voraussetzen. Entweder komplett als root arbeiten oder mittels sudo. | Anstatt nano kann jeder andere kompatible Editor verwendet werden. Weiterhin bitte beachten, dass die hier genannten Befehle teilweise root-Rechte voraussetzen. Entweder komplett als root arbeiten oder mittels sudo. In den nachfolgenden Beispielen wird daher stets sudo in Klammern geschrieben, '''dies bitte aber nicht so eingeben''', sondern eben sudo su grundsätzlich voranstellen oder vor jedem Befehl sudo einsetzen. | ||
Hinweis: im Folgenden ist "#" der Shell-Prompt und "mysql>" der MariaDB-Prompt innerhalb mysql, dieser kann mit exit verlassen werden. | Hinweis: im Folgenden ist "#" der Shell-Prompt und "mysql>" der MariaDB-Prompt innerhalb mysql, dieser kann mit exit verlassen werden. | ||
Unter Ubuntu/debian: | Unter Ubuntu/debian: | ||
# apt-get update && apt-get install mariadb-server | # (sudo) apt-get update && apt-get install mariadb-server | ||
Nach der Installation muss das Script | Nach der Installation muss das Script | ||
# mysql_secure_installation | # (sudo) mysql_secure_installation | ||
ausgeführt werden. Dieses legt ein root-Passwort fest, entfernt die anonymen User und entfernt die test-Datenbank. | ausgeführt werden. Dieses legt ein root-Passwort fest, entfernt die anonymen User und entfernt die test-Datenbank. | ||
Empfohlen wird, alle Fragen mit '''Y''' zu beantworten. | Empfohlen wird, alle Fragen mit '''Y''' zu beantworten. | ||
Zum Test mit MariaDB verbinden: | Zum Test mit MariaDB verbinden: | ||
# mysql -u root -p | # (sudo) mysql -u root -p | ||
Enter password: | Enter password: | ||
MariaDB [(none)]> exit | MariaDB [(none)]> exit | ||
Zeile 119: | Zeile 120: | ||
Hierfür kann die Datei /opt/fhem/contrib/dblog/db_create_mysql.sql als Vorlage verwendet und das Passwort und der Benutzername geändert werden. | Hierfür kann die Datei /opt/fhem/contrib/dblog/db_create_mysql.sql als Vorlage verwendet und das Passwort und der Benutzername geändert werden. | ||
cd /opt/fhem/contrib/dblog/ | cd /opt/fhem/contrib/dblog/ | ||
nano db_create_mysql.sql | (sudo) nano db_create_mysql.sql | ||
Dann wird die Datei eingelesen: | Dann wird die Datei eingelesen: | ||
# mysql -u root -p < db_create_mysql.sql | # (sudo) mysql -u root -p < db_create_mysql.sql | ||
Enter password: | Enter password: | ||
MariaDB [(none)]> exit | MariaDB [(none)]> exit | ||
Zeile 128: | Zeile 129: | ||
Jetzt kann man den Zugang als fhemuser testen (eventuell muss der Befehl sudo mysql lauten): | Jetzt kann man den Zugang als fhemuser testen (eventuell muss der Befehl sudo mysql lauten): | ||
# mysql -p -u <fhemuser> | # (sudo) mysql -p -u <fhemuser> | ||
Enter password: <fhempassword> | Enter password: <fhempassword> | ||
MariaDB [(none)]> show databases; | MariaDB [(none)]> show databases; | ||
Zeile 160: | Zeile 161: | ||
die die Tabellen current und history enthält. | die die Tabellen current und history enthält. | ||
Nun in der Datei db.conf den mysql-Block auskommentieren und ebenfalls Benutzername, Passwort UND HOST anpassen. Leider ist hier | Nun in der Datei db.conf den mysql-Block auskommentieren und ebenfalls Benutzername, Passwort UND HOST anpassen. Leider ist hier nichts standardmäßig eingestellt. | ||
nano /opt/fhem/db.conf | (sudo) nano /opt/fhem/contrib/dblog/db.conf | ||
Jetzt kann unter FHEM ein DbLog-Device angelegt werden (mit dem beispiel wird alles geloggt: | Jetzt kann unter FHEM ein DbLog-Device angelegt werden (mit dem beispiel wird alles geloggt: | ||
define logdb DbLog ./db.conf .*:.* | define logdb DbLog ./contrib/dblog/db.conf .*:.* | ||
Als State muss ein "connected" angezeigt werden. | Als State muss ein "connected" angezeigt werden. | ||
Zeile 218: | Zeile 219: | ||
=== phpMyAdmin === | === phpMyAdmin === | ||
Ein sehr beliebtes Tool ist phpMyAdmin. | Ein sehr beliebtes Tool ist phpMyAdmin. | ||
# apt-get install | # apt-get install phpmyadmin | ||
Bei der Installation wird man nach einem Passwort für den User "phpMyAdmin" gefragt, der im weiteren Verlauf erstmal nutzlos ist. | Bei der Installation wird man nach einem Passwort für den User "phpMyAdmin" gefragt, der im weiteren Verlauf erstmal nutzlos ist. | ||
Nun kann man sich unter <meine-fhem-Domain>/phpMyAdmin mit der Seite verbinden. | Nun kann man sich unter <meine-fhem-Domain>/phpMyAdmin mit der Seite verbinden. |
Aktuelle Version vom 13. April 2021, 09:22 Uhr
Einleitung
Da bei der Inbetriebnahme und der Migration von MySQL bzw MariaDB-Datenbanken einige kleine Fallen auftreten können, gibt's hier ein paar Hinweise.
Server version: 10.1.40-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04
db.conf
DbLog wird durch zwei verschiedene Einträge aktiviert/definiert. In einer Datei namens db.conf werden die Parameter für eine Verbindung zur Datenbank (host, username, password, etc.) hinterlegt. Diese Datei kann in einem beliebigen Verzeichnis angelegt werden. Für eine MariaDB-Datenbank sieht die db.conf folgendermaßen aus:
%dbconfig= ( connection => "mysql:database=fhem;host=db;port=3306", user => "fhemuser", password => "fhempassword", );
Im Verzeichnis contrib/dblog der FHEM-Installation befindet sich eine Beispielkonfiguration mit der Syntax für jeden unterstützen Datenbanktyp. Bei der Verwendung von MariaDB muss die MySQL-Datei verwendet werden. Es wird empfohlen, diese Datei zu kopieren und erst dann entsprechend zu bearbeiten. Am Besten kopiert man diese Datei in das FHEM Home Directory /opt/fhem/ und achtet auf die entsprechenden Rechte!
chown fhem:dialout /opt/fhem/db.conf
Tabellen
Die Datenbank ist relativ simpel gestaltet und besteht lediglich aus den folgenden beiden Tabellen:
- current
- history
DbLog ist auf eine feste Tabellenstruktur angewiesen. Man muss daher in seiner Datenbank eine Tabelle mit folgenden Spalten anlegen:
Spalte | Beschreibung (en) | Beschreibung (de) | Beispiel |
---|---|---|---|
TIMESTAMP | timestamp of event | Zeitstempel | 2007-12-30 21:45:22 |
DEVICE | device name | Device-Name | Wetterstation |
TYPE | device type | Device-Typ | KS300 |
EVENT | event specification as full string | Eventspezifikation als Text | humidity: 71 (%) |
READING | name of reading extracted from event | Bezeichnung des Readings | humidity |
VALUE | actual reading extracted from event | Wert des Readings | 71 |
UNIT | unit extracted from event | Einheit des Readings | % |
Im Verzeichnis contrib/dblog befindet sich das Script db_create_mysql.sql , welches eine neue Datenbank samt Tabellenstruktur anlegt. Ausserdem wird ein Benutzer "fhemuser" mit einem (zu ändernden) "fhempasswort" angelegt.
Primary Keys
current
Die Tabelle current enthält für jedes zu loggende Device lediglich den letzten Wert. Falls noch kein Wert geloggt wurde, ist diese Tabelle leer. Falls der Inhalt gelöscht wird, bauen sich die Daten automatisch wieder auf. Es gehen durch das löschen der Tabelle current keine Log-Informationen verloren. Der Inhalt wird aber u.a. für die Dropdown-Felder beim Plot-Editor verwendet.
Um doppelte Einträge in der Tabelle zu vermeiden, wurde die Möglichkeit geschaffen, Primary Keys zu definieren. Da in der Spalte READING
u.U. bei verschiedenen Geräten gleiche Namen vorkommen können, sollte der Primary Key um den Gerätenamen erweitert werden. Der Primary Key sollte also aus DEVICE
und READING
bestehen. Um in der Datenbank fhem diesen PK zu setzen, kann folgender SQL Code verwendet werden:
ALTER TABLE `fhem`.`current`
CHANGE COLUMN `DEVICE` `DEVICE` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL ,
CHANGE COLUMN `READING` `READING` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL ,
ADD PRIMARY KEY (`DEVICE`, `READING`);
history
Die Tabelle history enthält alle bisher geloggten Daten. Löschen in dieser Tabelle bedeutet automatisch Datenverlust (gewollt oder nicht ... ) Der Inhalt dieser Tabelle wird verwendet, um die Plots zu zeichnen oder Auswertungen mit DbRep anzufertigen
Um Probleme beim Import von cacheFiles zu vermeiden, kann in der Datenbank ein PK angelegt werden, welcher Timestamp, Device und Reading umfasst. Dadurch werden doppelte Einträge wirksam verhindert.
ALTER TABLE `fhem`.`history`
CHANGE COLUMN `DEVICE` `DEVICE` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL ,
CHANGE COLUMN `READING` `READING` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL ,
ADD PRIMARY KEY (`TIMESTAMP`,`DEVICE`, `READING`);
Beispiel: Anlegen und Nutzung einer MariaDB-Datenbank
Anstatt nano kann jeder andere kompatible Editor verwendet werden. Weiterhin bitte beachten, dass die hier genannten Befehle teilweise root-Rechte voraussetzen. Entweder komplett als root arbeiten oder mittels sudo. In den nachfolgenden Beispielen wird daher stets sudo in Klammern geschrieben, dies bitte aber nicht so eingeben, sondern eben sudo su grundsätzlich voranstellen oder vor jedem Befehl sudo einsetzen.
Hinweis: im Folgenden ist "#" der Shell-Prompt und "mysql>" der MariaDB-Prompt innerhalb mysql, dieser kann mit exit verlassen werden.
Unter Ubuntu/debian:
# (sudo) apt-get update && apt-get install mariadb-server
Nach der Installation muss das Script
# (sudo) mysql_secure_installation
ausgeführt werden. Dieses legt ein root-Passwort fest, entfernt die anonymen User und entfernt die test-Datenbank. Empfohlen wird, alle Fragen mit Y zu beantworten.
Zum Test mit MariaDB verbinden:
# (sudo) mysql -u root -p Enter password: MariaDB [(none)]> exit
Sollte hier eine Fehlermeldung "Access denied for user 'root'@'localhost'" trotz richtigem Passwort erscheinen, muss man sich als root mit der Datenbank verbinden; also sudo mysql...
Jetzt die Tabellenstruktur anlegen. Hierfür kann die Datei /opt/fhem/contrib/dblog/db_create_mysql.sql als Vorlage verwendet und das Passwort und der Benutzername geändert werden.
cd /opt/fhem/contrib/dblog/ (sudo) nano db_create_mysql.sql
Dann wird die Datei eingelesen:
# (sudo) mysql -u root -p < db_create_mysql.sql Enter password: MariaDB [(none)]> exit
Jetzt kann man den Zugang als fhemuser testen (eventuell muss der Befehl sudo mysql lauten):
# (sudo) mysql -p -u <fhemuser> Enter password: <fhempassword> MariaDB [(none)]> show databases;
Das Ergebnis lautet
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | fhem | | information_schema | | mysql | | performance_schema | +--------------------+
Nun müsste eine Datenbank "fhem" angezeigt werden. Um die Tabellen zu sehen, kann man den folgenden Befehl eingeben:
MariaDB [(none)]> use fhem; show tables;
und das Ergebnis lautet
MariaDB [fhem]> show tables; +----------------+ | Tables_in_fhem | +----------------+ | current | | history | +----------------+ 2 rows in set (0.001 sec),
die die Tabellen current und history enthält.
Nun in der Datei db.conf den mysql-Block auskommentieren und ebenfalls Benutzername, Passwort UND HOST anpassen. Leider ist hier nichts standardmäßig eingestellt.
(sudo) nano /opt/fhem/contrib/dblog/db.conf
Jetzt kann unter FHEM ein DbLog-Device angelegt werden (mit dem beispiel wird alles geloggt:
define logdb DbLog ./contrib/dblog/db.conf .*:.*
Als State muss ein "connected" angezeigt werden.
Ein rereadcfg in FHEM stellt sicher, dass die neue Konfiguration übernommen wird - ein Neustart ist nicht erforderlich.
Nun kann die Funktion noch einmal überprüft werden:
# mysql -u <fhemuser> -p
Enter password: <fhempassword>
mysql> use fhem;
Database changed
mysql> show tables;
+----------------+
| Tables_in_fhem |
+----------------+
| current |
| history |
+----------------+
2 rows in set (0,00 sec)
mysql> select * from history; # Achtung, kann sehr groß werden .... #
Beispiel: Abfragescript PHP/MySQL
Um eine schnelle Übersicht zu bekommen habe ich mir dieses Script geschrieben:
<?php $pdo = new PDO('mysql:host=localhost;dbname=fhem', 'fhemuser', 'fhempasswort');
echo '<h2>Tabelle Current</h1><br><table border="1">';
echo "<tr><th>Anzahl</th><th>Name</th><th>Readings</th></tr>";
$sql = "SELECT COUNT(*), DEVICE, GROUP_CONCAT(DISTINCT READING ORDER BY READING DESC SEPARATOR '</li><li>') FROM current GROUP BY DEVICE;"; foreach ($pdo->query($sql) as
$row) {
echo "<tr><td>" . $row[0] . "</td><td>" . $row[1] . "</td><td><ol><li>" . $row[2] . "</li></ol></td></tr>";
}
echo "</table>";
echo '<h2>Tabelle History</h1><br><table border="1">';
echo "<tr><th>Anzahl</th><th>Name</th></tr>";
$sql = "SELECT COUNT(*), DEVICE FROM history GROUP BY DEVICE;"; foreach ($pdo->query($sql) as
$row) {
echo "<tr><td>" . $row[0] . "</td><td>" . $row[1] . "</td></tr>";
}
echo "</table>";
?>
Bitte passt fhemuser und fhempasswort an. Das Ganze kommt dann nach /var/www/html/fhemdb.php und ist mit <IP>/fhemdb.php aufrufbar. Wenn ihr den 2. Block für die history Tabelle ausklammert oder entfernt läuft das Script viel schneller ab - klar die history Tabelle ist meist randvoll.
Bearbeitung von Datenbank-Einträgen
Irgendwann wird der Fall eintreten, dass in der Datenbank Einträge drinstehen, die geändert oder gelöscht werden sollen (zB. fehlerhafte Sensor-Rückmeldungen, umbenannte Readings). In klassischen Log-Dateien würde man diese einfach bearbeiten und löschen/anpassen (wobei man aber tunlichst zuvor FHEM stoppt, um Datenfehler zu vermeiden). Eine Datenbank kann bearbeitet werden, ohne FHEM stoppen zu müssen.
Datenbanken kann man ohne weitere Hilfsmittel direkt von der Kommandozeile/Shell aus bearbeiten. Alternativ gibt es auch verschiedenste Tools (webbasiert oder als Applikation), die einen dabei unterstützen (Bsp. findet man u.a. hier). Für einfache Arbeiten reicht allerdings idR. Shell.
phpMyAdmin
Ein sehr beliebtes Tool ist phpMyAdmin.
# apt-get install phpmyadmin
Bei der Installation wird man nach einem Passwort für den User "phpMyAdmin" gefragt, der im weiteren Verlauf erstmal nutzlos ist. Nun kann man sich unter <meine-fhem-Domain>/phpMyAdmin mit der Seite verbinden. Zum einloggen kann nun der in der config-datei angegebene fhemuser mit dem fhempasswort verwendet werden, oder, sofern ein Passwort vergeben ist, auch den User root. Der Unterschied liegt darin, dass root *alle* Datenbanken sehen und ändern darf (was zum ändern von Datenbankeigenschaften sowie Benutzerkonten notwendig ist), während dem fhemuser nur die fhemdatenbank angezeigt wird(was für suchabfragen, deletes und inserts sowie Tabellenänderungen völlig ausreicht.
Datenbank migrieren
Eine schöne Anleitung zur Migration von SQLite zu MySQL/MariaDB mit Hilfe von DbRep findet sich hier: [1].
Hinweis: Wenn die SQLite-DB sehr groß wird, kann es sein, dass der oben beschriebene Weg nicht funktioniert (konkret war dies bei meiner 15 GB großen DB nicht möglich, der Prozess hat sich immer nach mehreren Stunden aufgehängt).
Nützliche Codeschnipsel
Anbei ein paar nützliche Codeschnipsel rund um DbLog
Dateigrösse mitloggen
Da die Datenbank ins Unermessliche wachsen kann, empfiehlt es sich - je nach Speicherplatz - ab einer bestimmten Grösse tätig zu werden. Dazu muss diese Grösse allerdings ermittelt werden. Zur Ermittlung der MariaDB-Größe kann wie hier beschrieben vorgegangen werden.