FHEM Tutorial-Reihe - Part 7: mySQL-Server für Logging nutzen

Achtung: Dieser Inhalt ist nicht mehr aktuell! Es ist nicht empfohlen nach dieser Anleitung vorzugehen.

FHEM Tutorial-Reihe - Part 7: mySQL-Server für Logging nutzen

20.05.2016
Veraltet!
mySQL
Integration

Jetzt haben wir langsam so viele Geräte angelegt, dass es sich lohnt ein alternatives Ziel für das Logging festzulegen. In diesem Teil installieren und konfigurieren wir einen mySQL-Server und nutzen diesen als Ziel für alle Eventdaten in FHEM.

Was genau wird alles gelernt?

wie man einen mySQL-Server installiert
wie neue Benutzer angelegt werden
wie einem ein mySQL-Client das Leben viel einfacher gestalten kann
wie ein DB-Log für FHEM konfiguriert wird
wofür man diese Daten nutzen könnte

Folgende Befehle braucht ihr:

sudo apt-get update
sudo apt-get install mysql-server mysql-client

sudo aptitude install libdbi-perl

sudo apt-get -y install libclass-dbi-mysql-perl

sudo vi /etc/mysql/my.cnf
#bind-address = 127.0.0.1

mysql -u root -p

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';
GRANT USAGE ON *.* TO 'root'@'%' WITH GRANT OPTION;
exit

sudo service mysql restart

fhempassword sollte natürlich mit einem neuen Passwort ersetzt werden.

CREATE DATABASE `fhem` DEFAULT CHARACTER SET = `utf8`;

CREATE USER 'fhemuser'@'%' IDENTIFIED BY 'fhempassword';

REVOKE CREATE ROUTINE, CREATE VIEW, CREATE USER, ALTER, SHOW VIEW, CREATE, ALTER ROUTINE, EVENT, SUPER, INSERT, RELOAD, SELECT, DELETE, FILE, SHOW DATABASES, TRIGGER, SHUTDOWN, REPLICATION CLIENT, GRANT OPTION, PROCESS, REFERENCES, UPDATE, DROP, REPLICATION SLAVE, EXECUTE, LOCK TABLES, CREATE TEMPORARY TABLES, INDEX ON *.* FROM 'fhemuser'@'%';

UPDATE mysql.user SET max_questions = 0, max_updates = 0, max_connections = 0 WHERE User = 'fhemuser' AND Host = '%';

GRANT CREATE ROUTINE, CREATE VIEW, ALTER, SHOW VIEW, CREATE, ALTER ROUTINE, EVENT, INSERT, SELECT, DELETE, TRIGGER, GRANT OPTION, REFERENCES, UPDATE, DROP, EXECUTE, LOCK TABLES, CREATE TEMPORARY TABLES, INDEX ON `fhem`.* TO 'fhemuser'@'%';

USE `fhem`;

CREATE TABLE `history` (
    `TIMESTAMP` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `DEVICE` varchar(64) DEFAULT NULL,
    `TYPE` varchar(64) DEFAULT NULL,
    `EVENT` varchar(512) DEFAULT NULL,
    `READING` varchar(64) DEFAULT NULL,
    `VALUE` varchar(255) DEFAULT NULL,
    `UNIT` varchar(32) DEFAULT NULL,
    KEY `IDX_HISTORY` (`DEVICE`,`READING`,`TIMESTAMP`,`VALUE`),
    KEY `DEVICE` (`DEVICE`,`READING`)
);

CREATE TABLE `current` (
  `TIMESTAMP` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `DEVICE` varchar(64) DEFAULT NULL,
  `TYPE` varchar(64) DEFAULT NULL,
  `EVENT` varchar(512) DEFAULT NULL,
  `READING` varchar(64) DEFAULT NULL,
  `VALUE` varchar(255) DEFAULT NULL,
  `UNIT` varchar(32) DEFAULT NULL
);

sudo apt-get install vim

sudo vi /opt/fhem/contrib/dblog/db.conf

fhempassword sollte natürlich mit dem oben vergebenen Passwort ersetzt werden.

%dbconfig= (
        connection => "mysql:database=fhem;host=localhost;port=3306",
        user => "fhemuser",
        password => "fhempassword",
);

define DBLogging DbLog /opt/fhem/contrib/dblog/db.conf .*:.*
attr DBLogging DbLogType Current/History

define DBLogging_Reopen at +*00:15:00 set DBLogging reopen

SELECT DEVICE, COUNT(*) FROM history GROUP BY DEVICE ORDER BY 2 DESC;

define at_DbLoggingReduce at *03:00:00 set DBLogging reduceLogNbl 90 average

DELETE FROM history WHERE DEVICE = 'OUT_Wetter' AND READING NOT IN ('temp_c', 'humidity');

attr .* DbLogExclude .*

define n_DbLogExclude notify global:DEFINED.* attr $EVTPART1 DbLogExclude .*

list TYPE=SVG

list a:DbLogInclude!=

OPTIMIZE TABLE history;

SELECT DISTINCT DEVICE, READING, COUNT(*) anzahl FROM history GROUP BY DEVICE, READING ORDER BY 1 ASC, 2 ASC, 3 DESC;