[Praxis] Einsatz der MySQL(MariaDB) im IOB

fuzzy1955

Teil 1: Motivation zu MariaDB und Voraussetzungen für die Verbindung zum IOBroker

Diese Beschreibungen richten sich an alle Forums-User/-innen, die gerne und schon lange mit SQL-Datenbanken arbeiten. Dies, obwohl ihnen bekannt ist, dass InfluxDB ein Spezialist bei Zeitreihendaten ist und mit einer extrem schnellen Schreibgeschwindigkeit aufwartet. Das ist ein klarer Nachteil bei MySQL. Auch ich nehme das in Kauf, weil ich gerne bis in die letzte Ecke eine Datenbank schauen mag. Und natürlich arbeite ich seit mehr als 30 Jahren mit Oracle, MS SQL Server, IBM DB2 etc. und bin damit sehr vertraut.

Um den IOBroker mit MariaDB zu verbinden sind ein paar Schritte an der Linux-Konsole notwendig:

Installieren der MariaDB

sudo apt update
sudo apt upgrade -y
sudo apt install mariadb-server

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf 
----------------------------------------------------
pid-file                = /run/mysqld/mysqld.pid
basedir                 = /usr
bind-address = 0.0.0.0
----------------------------------------------------

sudo systemctl restart mariadb

Firewall einstellen

sudo ufw allow from 192.168.0.0/24 to any port 3306
sudo ufw reload

Damit läuft die Datenbank auf dem Standardport 3306. Das Standardverzeichnis der Datenbanken ist /var/lib/mysql/iobroker.

Einloggen in MariaDB

sudo mysql -u root -p	                        //nur das 1. Mal und nur Ausnahmsweise !!!
sudo mysql -u iobroker -p -D iobroker		//späterer Einstieg

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 32
Server version: 11.8.3-MariaDB-0+deb13u1 from Debian -- Please help get to 10k stars at https://github.com/MariaDB/Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Anlegen der Datenbank und des DB-Benutzers

wenn noch erforderlich: CREATE DATABASE `iobroker` DEFAULT CHARACTER SET utf8mb4;
CREATE USER  'iobroker'@'localhost' IDENTIFIED BY 'seine';
GRANT ALL ON iobroker.* to 'iobroker'@'localhost' IDENTIFIED BY 'seine' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* to 'iobroker'@'%' IDENTIFIED BY '<dein Passwort>';
FLUSH PRIVILEGES;

Einstellungen im IOBroker

Wenn man die Option „Datenbank nicht erstellen“ leer lässt, legt der SQL-Adapter die DB samt den Tabellen an. Nun kann man bei den gewünschten Datenpunkte im IOBroker die SQL-Protokollierung aktivieren.

Wichtig ist noch die Aktivierung des Backitup:

--------------------------- Das wäre es fürs Erste ---------------------------

fuzzy1955

Teil 2: Beispiele von Tools zur Verwaltung der mySQL-Datenbanken (Kommandozeile, MySQL Workbench, ODBC – MS ACCESS)

Wichtig zu wissen: Bei allen Varianten können Daten geändert und/oder gelöscht bzw. neue Daten eingefügt werden !!

2.1 Die Linux Kommandozeile:
Einloggen in MariaDB und wichtig: Datenbankbefehle immer mit „ ; “ abschließen!

sudo mysql -u iobroker -p -D iobroker

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 32
Server version: 11.8.3-MariaDB-0+deb13u1 from Debian -- Please help get to 10k stars at https://github.com/MariaDB/Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [iobroker]>

SHOW TABLES;					// zeigt die Namen der Datenbanktabellen
SELECT * FROM iobroker.ts_number;		// zeigt die gespeicherten Daten der Tabelle „ts_number“

2.2 MySQL Workbench:
Diese grafische Software kann kostenlos von MySQL Workbench Download heruntergeladen werden.

Das Konfigurieren:

Damit lässt sich doch sehr viel anschaulicher mit der Datenbank arbeiten:

2.3 ODBC-Zugriff am Beispiel MS ACCESS oder MS Excel:
Die Treibervariante 9.06 findet man hier

Die Einbindung im MS ACCESS:

Für MS Excel gilt das Gleiche sinngemäß: Im Menü Daten – Daten abrufen – Aus anderen Quellen – Aus ODBC.

--------------------------- Gutes Gelingen ---------------------------

fuzzy1955

Teil 3: Tabellen der Datenbank "iobroker" - Arbeiten mit SQL-Statements - Erstellen von Views

Für mich gibt es aus Sicht der IOBroker-Daten nur 2 interessante Tabellen: datapoints und ts_number.

Beim Öffnen der Tabelle „ts_number“ fallen einem sofort der Unix Timestamp (ts) statt eines Datumsfeldes nach unserem Geschmack auf. Das Wesen der Unix-Zeit findet sich hier.

Um aus dem Unix-Timestamp ein lesbares Datum zu formen, gibt es die Funktion:

SELECT from_unixtime(SUBSTRING(ts, 1, 10)) AS tsDate FROM iobroker.ts_number LIMIT 10;

Und damit landen wir beim Verknüpfen der beiden Tabellen und statten diese mit virtuellen Feldern zu einer vielseitigen und schnellen View. Dazu öffnen wir im MySQL Workbench ein leeres Abfragefenster und erstellen die View mit SQL-Script:

CREATE OR REPLACE VIEW iobroker.vw_number AS     
    SELECT a.id, b.name, from_unixtime(SUBSTRING(a.ts, 1, 10)) AS tsDate, a.ts, a.val
    FROM iobroker.ts_number a LEFT OUTER JOIN 
		iobroker.datapoints b ON a.id = b.id
    ORDER BY ts desc;

Ein paar Grundsätze beim Benennen von View und virtuellen Feldern:

Keine Leerzeichen, Umlaute, Großbuchstaben, Sonderzeichen
Auch bei kurzen Namen sollte der Zweck leicht ersichtlich sein

Mit weiteren virtuellen Feldern sehen die Daten gleich viel lesbarer aus. Dazu gruppieren und sortieren wir nach Tagen ('%Y-%m-%d'), Monaten ('%Y-%m) oder Jahren ('%Y).

SELECT DATE_FORMAT(tsDate, '%Y-%m-%d'),
	IFNULL(CAST(MAX(CASE WHEN id = 181  THEN val END) AS INT), 0) as pv,
	IFNULL(CAST(MAX(CASE WHEN id = 183 THEN val END) AS INT) * -1, 0) as haus,
	IFNULL(CAST(MAX(CASE WHEN id = 180 THEN val END) AS INT) * -1000, 0) as eauto,
	IFNULL(CAST(MAX(CASE WHEN id = 179 THEN val END) AS INT) * -1000, 0) as gen,
	IFNULL(CAST(MAX(CASE WHEN id = 182 THEN val END) AS INT) * -1, 0) as nots,
	IFNULL(CAST(AVG(CASE WHEN id = 119 THEN val END) AS INT), 0) as akku, 0, 0
FROM iobroker.vw_number
WHERE id IN (119,179,180,181,182,183) 
GROUP BY DATE_FORMAT(tsDate, '%Y-%m-%d');
ORDER BY DATE_FORMAT(tsDate, '%Y-%m-%d') desc;

Das Ergebnis:

Id	Tag	Pv	Haus	Eauto	Gen	Nots	batSOC	Gesamt	autark
295	2026-02-03	30000	-1215	-16000	0	0	15	12788	1
292	2026-02-02	32000	-1015	-14000	0	0	14	16985	1
291	2026-02-01	2000	-990	0	0	0	14	1010	1
290	2026-01-31	0	-1040	0	0	0	16	-1040	0
289	2026-01-30	0	-690	0	0	0	17	-690	0
288	2026-01-29	3000	-780	0	0	0	11	2220	1
284	2026-01-28	22000	-1262	-11000	0	0	16	9738	1
282	2026-01-27	15000	-1000	0	0	0	12	14000	1
281	2026-01-26	8000	-850	0	0	0	5	7150	1
280	2026-01-25	4000	-930	0	0	0	8	3070	1
279	2026-01-24	8000	-745	0	0	0	11	7255	1
278	2026-01-23	13000	-978	0	0	0	12	12022	1
277	2026-01-22	18000	-1003	0	0	0	11	16997	1
276	2026-01-21	33000	-1036	-10580	-815	-1246	15	19323	1
275	2026-01-20	4000	-874	0	0	0	17	3126	1
274	2026-01-19	6000	-876	0	0	0	0	5124	1
273	2026-01-18	12000	-882	0	0	0	0	11118	1
272	2026-01-17	40000	-910	-11900	0	0	0	27190	1
271	2026-01-16	2000	-2062	0	0	0	0	-62	0
270	2026-01-15	3000	-2063	0	-815	0	0	122	1
269	2026-01-14	10000	0	-8853	-801	-1245	0	-899	0

Der Vollständigkeit halber findet sich hier eine Übersicht der wichtigsten MySQL-Befehle. Darin gibt es auch Infos über Datentypen, eindeutigen Primärschlüssel, Indizes, Relationen etc.

Damit wird die Sinnhaftigkeit von Views schnell klar. Man erstellt 1 x eine View und profitiert lange von der exzellenten Datenbankleistung der serverseitigen Abfragen.

--------------------------- Viel Spass beim Ausprobieren ---------------------------

fuzzy1955

Teil 4: Sinnhaftigkeit und Erstellen von Stored Procedures und deren zeitgesteuerter Starts

Wozu soll man sich nun plagen und sich mit eigenen Tabellen samt Stored Procedures einlassen?
Nun, schauen wir mal, welche Datenmengen sich pro Tag ansammeln:

SELECT id, name, count(id) AS Anzahl
FROM iobroker.vw_number
WHERE DATE_FORMAT(tsDate, '%Y-%m-%d') = '2026-02-01'
GROUP BY id
ORDER BY count(id) desc;

--------------
Das Ergebnis:
--------------

Id	DP_name	AnzahlDS
195	alias.0.Hausstrom.Allgemein.StromverlustleistungWR	
149	alias.0.Hausstrom.Allgemein.StromleistungHaus	3028
167	alias.0.Hausstrom.Hausakku.BatterieLadestromIst	2753
120	alias.0.Hausstrom.Hausakku.BatterieSpannung	1308
119	alias.0.Hausstrom.Hausakku.BatterieLadezustand	1261
132	alias.0.Hausstrom.PvModule.PvPrognose	51
196	alias.0.Klimatisierung.Boiler.Boilerstrom	48
145	alias.0.Hausstrom.Allgemein.StrompreisJetzt	46
101	alias.0.Wetter.Windgeschwindigkeit	24
79	alias.0.Klimatisierung.Messungen.Aussentemperatur	24
77	alias.0.Klimatisierung.Messungen.Luftsolartemperatur	24
98	alias.0.Wetter.Luftfeuchtigkeit	24
76	alias.0.Klimatisierung.Messungen.Innentemperatur	19
121	alias.0.Hausstrom.Hausakku.BatterieTemperatur	18
197	Boilertemperatur	17
118	alias.0.Hausstrom.PvModule.PvErzeugung	15
115	alias.0.Hausstrom.Wallbox.Allgemein.EnergieGeladen	11
181	alias.0.Diagrammdaten.StromPV	1
183	alias.0.Diagrammdaten.StromHaus	1

Die meisten Werte brauchen wir ab dem nächsten Tag nur mehr tageweise verdichtet, etwa so:

Id	Tag	Pv	Haus	Eauto	Gen	Nots	batSOC	Gesamt	autark
295	2026-02-03	30000	-1215	-16000	0	0	15	12788	1
292	2026-02-02	32000	-1015	-14000	0	0	14	16985	1
291	2026-02-01	2000	-990	0	0	0	14	1010	1
290	2026-01-31	0	-1040	0	0	0	16	-1040	0
289	2026-01-30	0	-690	0	0	0	17	-690	0
288	2026-01-29	3000	-780	0	0	0	11	2220	1
284	2026-01-28	22000	-1262	-11000	0	0	16	9738	1
282	2026-01-27	15000	-1000	0	0	0	12	14000	1
281	2026-01-26	8000	-850	0	0	0	5	7150	1
280	2026-01-25	4000	-930	0	0	0	8	3070	1
279	2026-01-24	8000	-745	0	0	0	11	7255	1
278	2026-01-23	13000	-978	0	0	0	12	12022	1
277	2026-01-22	18000	-1003	0	0	0	11	16997	1
276	2026-01-21	33000	-1036	-10580	-815	-1246	15	19323	1
275	2026-01-20	4000	-874	0	0	0	17	3126	1
274	2026-01-19	6000	-876	0	0	0	0	5124	1
273	2026-01-18	12000	-882	0	0	0	0	11118	1
272	2026-01-17	40000	-910	-11900	0	0	0	27190	1
271	2026-01-16	2000	-2062	0	0	0	0	-62	0
270	2026-01-15	3000	-2063	0	-815	0	0	122	1
269	2026-01-14	10000	0	-8853	-801	-1245	0	-899	0

Um die Datenbank nicht übermäßig anwachsen zu lassen, lohnt sich also eine Datenverdichtung mit nachfolgendem Löschen der Detaildaten. Zunächst erstellen wir eine eigener Tabelle zur Datenverdichtung (Grundsätze siehe unter #3):

CREATE TABLE IF NOT EXISTS iobroker.strombilanz(  
	id INT NOT NULL AUTO_INCREMENT,
	tag DATE NOT NULL,
	pv INT NOT NULL,
	haus INT NOT NULL,
	eauto INT NOT NULL,
	gen INT NOT NULL,
	nots INT NOT NULL,
	akku INT NOT NULL,
	gesamt INT NOT NULL,
	autark INT NOT NULL,
PRIMARY KEY (id)) ENGINE = InnoDB;

CREATE UNIQUE INDEX IX_strombilanz01 ON iobroker.strombilanz(tag);

Und für die Loggingtabelle:

CREATE TABLE IF NOT EXISTS iobroker.strombilanz_log ( 
	id INT AUTO_INCREMENT PRIMARY KEY, 
	logtime DATETIME, 
	message VARCHAR(255) 
);

Jetzt kommt das Herzstück, die Stored Procedure. Diese umfasst folgende Funktionen:

• Logging der Schritte in die Tabelle „iobroker.strombilanz_log“
• Check der Datensatzzahl
• Anfügen der Tageswerte in die Tabelle „iobroker.strombilanz“
• Diverse Berechnungen
• Löschen der Detaildaten, die älter als 10 Tage sind.

DELIMITER $$
CREATE OR REPLACE PROCEDURE iobroker.sp_tagesbilanz()
-- CALL iobroker.sp_tagesbilanz();
BEGIN
	DECLARE intAnzahl INT;
    -- Fehlerbehandlung
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        INSERT INTO iobroker.strombilanz_log (logtime, message)
        VALUES (NOW(), 'SQLEXCEPTION in sp_strombilanz');
    END;
   
	SET SQL_SAFE_UPDATES = 0;
    
    DELETE FROM iobroker.strombilanz WHERE Tag = DATE_FORMAT(NOW(), '%Y-%m-%d');
  
    SET intAnzahl = (SELECT COUNT(val) 
	FROM iobroker.vw_number 
                    WHERE id IN (119,179,180,181,182,183) AND DATE_FORMAT(tsDate, '%Y-%m-%d') = DATE_FORMAT(NOW(), '%Y-%m-%d')
	GROUP BY DATE_FORMAT(tsDate, '%Y-%m-%d'));
  
    INSERT INTO iobroker.strombilanz (tag, pv, haus, eauto, gen, nots, akku, gesamt, autark)
    SELECT DATE_FORMAT(tsDate, '%Y-%m-%d'),
        IFNULL(CAST(MAX(CASE WHEN id = 181  THEN val END) AS INT) * 1000, 0) as pv,
        IFNULL(CAST(MAX(CASE WHEN id = 183 THEN val END) AS INT) * -1, 0) as haus,
        IFNULL(CAST(MAX(CASE WHEN id = 180 THEN val END) AS INT) * -1000, 0) as eauto,
        IFNULL(CAST(MAX(CASE WHEN id = 179 THEN val END) AS INT) * -1, 0) as gen,
        IFNULL(CAST(MAX(CASE WHEN id = 182 THEN val END) AS INT) * -1, 0) as nots,
        IFNULL(CAST(AVG(CASE WHEN id = 119 THEN val END) AS INT), 0) as akku, 0, 0
    FROM iobroker.vw_number
    WHERE id IN (119,179,180,181,182,183) AND DATE_FORMAT(tsDate, '%Y-%m-%d') = DATE_FORMAT(NOW(), '%Y-%m-%d')
     GROUP BY DATE_FORMAT(tsDate, '%Y-%m-%d');

    UPDATE iobroker.strombilanz
    SET gesamt = (pv + haus + eauto + gen + nots),
        autark = IF((pv + haus + eauto + gen + nots) > 0, 1, 0);

    -- Logging
    INSERT INTO iobroker.strombilanz_log (logtime, message)
    VALUES (NOW(), CONCAT('sp_strombilanz erfolgreich ausgeführt')); --  CAST(intAnzahl AS CHAR))

    
    DELETE 
	FROM iobroker.strombilanz_log
	WHERE CAST(logtime AS DATETIME) < CAST(now() - INTERVAL 10 DAY AS DATETIME);
  
    SET SQL_SAFE_UPDATES = 1;

END$$

DELIMITER ;

Diese Stored Procedure kann man im IOBroker – Javascript zeitgesteuert wie folgt einbauen:

    //-------- Tagesbilanz erstellen ----------------------------------------------
    async function sendToAsync() {
        return new Promise((resolve, reject) => {
            sendTo('sql.0', 'query', 'CALL iobroker.sp_tagesbilanz();', result => {
                if (result.error) {reject(result.error);} 
                else {resolve(result.result)}
            });
        });
    }

    try {
        const result = await sendToAsync()
        //console.log('Die sp_tagesbilanz hat ' + JSON.stringify(result) + ' Datensätze erstellt'); // ' + getAttr(JSON.parse(result),'AnzDS') + '
        await wait(10000);
     -------- weitere Verarbeitungsschritte einfügen -----

--------------------------- Das Ergebnis macht Freude ---------------------------

fuzzy1955

Teil 5: Verwenden der SQL-Daten in Diagrammen bzw. im VIS-2

Reserve!
Bitte hier nichts antworten! Thread ist noch in der Entstehung und nur zum Lesen!

fuzzy1955

Reserve!
Bitte hier nichts antworten! Thread ist noch in der Entstehung und nur zum Lesen!

fuzzy1955

Reserve!
Bitte hier nichts antworten! Thread ist noch in der Entstehung und nur zum Lesen!

Community Forum

NEWS

[Praxis] Einsatz der MySQL(MariaDB) im IOB

Support us

610

32.6k

82.2k

1.3m