Super simpler ESP32 nach MySQL Datenlogger

Wer sich mit dem ESP32 beschäftigt, damit Sensoren abfragen möchte und zusätzlich etwas von Datenbanken versteht, der wird über kurz oder lang den Wunsch verspüren, die Sensordaten direkt in einer Datenbank zu speichern. Denn die damit verbundenen Möglichkeiten sind enorm!

Klar kann ich nur mit dem ESP32, einem Temperatursensor und einem Relais bewaffnet, eine Heizung temperaturgesteuert regeln. Aber was wäre, wenn ich für eine größere Steuerung einige zig Sensoren an unterschiedlichen Orten einsetzen möchte? Schon beim Thema Hausautomation stellt sich die Frage, ob man das nicht irgendwie systematischer angehen sollte?

Einige Vorteile:

Günstige Sensoren

Wenn wir annehmen, das ein ESP32 Board ca. 5,- Euro kostet und dann noch ein USB-Netzteil für ebenfalls 5,- Euro dazu kommt, kostet ein Sensor mit eingebautem Datenbankzugriff ungefähr 10,- Euro!

Vereinfachung und Standardisierung des Codes

Der Code für den ESP32 kann standardisiert werden. Es geht nur noch darum, irgendwie einen Wert von einem angelöteten Sensor zu bekommen und ihn zur Datenbank zu senden. Fertig! Ich werde Euch unten zeigen, mit wie wenig Code das möglich ist.

Alles auf einen Blick

Ihr habt eine zentrale Sammelstelle für Hunderte von Sensoren! Und die sind mit einem einzigen SQL-Select abgefragt! Wer sich mit Datenbanken etwas auskennt oder SQL kann, für den ist dies eine wahre Offenbarung!

Keine Middleware wie PHP notwendig

Bei randomnerdtutorials gibt es einen Beitrag, der zeigt, wie man die von mir genannten Ziele mit Hilfe einer Middleware wie PHP erreichen kann. Das ist viel zu viel Aufwand! Wenn der ESP32 selbst auf die Datenbank zugreifen kann, benötigt man keinen Webserver mit integrierter PHP-Unterstützung und muss auch keinen Extra PHP Code schreiben!

Stark vereinfachte Anwendungsentwicklung

Da die Daten jetzt in einer verbreiteten Datenbank vorliegen, kann man die zugehörige Anwendung in jeder Programmiersprache entwickeln, die einen Zugriff auf die Datenbank erlaubt. Egal ob Desktop- oder Webanwendung, alles ist möglich.

Super-simpler-Datenlogger

Das Beispiel, das ich Euch jetzt zeige, dient nur der Demonstration, wie einfach es tatsächlich sein kann. Die meisten von Euch werden wahrscheinlich eine aufwendigere Lösung entwickeln. Was auch sehr sinnvoll ist, denn ich lass jetzt einmal sämtliche ‘Korrektheit’ beiseite und gehe die Sache extrem minimalistisch an. Es geht nur darum, Eure Augen für einen neuen Weg zu öffnen.

Der Client kann natürlich um viele Fähigkeiten ergänzt werden. Wie zum Beispiel Webkonfiguration, ‘Deep Sleep’, Uhrzeitermittlung über WiFi, Ausgabe auf ein OLED-Display und vieles mehr.

Datenbank

Auch die Datenbank besteht erst einmal nur aus einer einzigen Tabelle. Hier sollten später mindestens noch ein, zwei weitere Tabellen dazu kommen. Eine zum Beispiel für Sensortypen und eine für die eingesetzten Sensoren selbst. So dass man beim Einfügen der Werte die ID des Sensors angeben kann.

Damit das Ganze funktioniert, müsst Ihr einen MySQL-Datenbankserver aufgesetzt haben. Meiner läuft auf einem Raspberry Pi, der sowieso das ganze Jahr rund um die Uhr online ist. Dort verwendet Ihr ein eventuell schon existierende Datenbankschema, oder Ihr legt Euch ein neues Schema an:

CREATE DATABASE `test`;

Jetzt benötigt Ihr noch einen User ‘testuser’, dem es erlaubt wird, mit dieser Datenbank zu arbeiten:

CREATE USER testuser@'%' IDENTIFIED BY 'password';

GRANT ALL ON test.* TO testuser@'%';

Mit diesem User muss sich der ESP32 bei der Datenbank anmelden, damit die SQL-Inserts auch angenommen werden. Und zum Schluss noch die Tabelle, die die einzelnen Datensätze aufnehmen wird:

CREATE TABLE `test`.`sensordata` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `TimeStamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Temperature` float NOT NULL,
  `Humidity` float NOT NULL,
  PRIMARY KEY (`ID`)
);

Und hier das SQL-Insert um neue Sensorwerte in die Tabelle einzufügen:

INSERT INTO sensordata (TimeStamp, Temperature, Humidity) VALUES ('2020-1-22 12:42:03', 12.5, 33.4);

Mit folgender Anweisung könnt Ihr Euch die bisher gesammelten Einträge anzeigen lassen:

SELECT * FROM sensordata t ORDER BY TimeStamp DESC;

Arduino-Code

#include <WiFi.h>
#include <SimpleDHT.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>

const char* SSID = "SSID";
const char* PASS = "Password";

int pinDHT22 = 17;
SimpleDHT22 dht22(pinDHT22);
WiFiClient client;
MySQL_Connection conn(&client);
MySQL_Cursor* cursor;

IPAddress server_addr(192, 168, 178, 20);
char user[] = "testuser"; 
char password[] = "password";

float temperature = 0;
float humidity = 0;

void setup() {
}

void loop() {
  WiFi.begin(SSID, PASS);
  delay (1000);
  dht22.read2(&temperature, &humidity, NULL);
  conn.connect(server_addr, 3306, user, password);
  delay (1000);
  cursor = new MySQL_Cursor(&conn);
  char statementChar[256];
  String statementStr = "INSERT INTO test.sensordata (Temperature, Humidity) VALUES  (" + String (temperature) + ", " + String (humidity) + ");";
  statementStr.toCharArray (statementChar, statementStr.length());
  cursor->execute(statementChar);
  conn.close();
  WiFi.disconnect();
  delay(5000);
}

Für das gezeigte Beispiel habe ich folgenden Sensor verwendet:

Den gezeigten Code habe ich kompiliert und auf dieses Board von Banggood hochgeladen. Um den Code zu kompilieren benötigt man zwei Bibliotheken:

Für die Abfrage des DHT22:

https://github.com/winlinvip/SimpleDHT

Für den Zugriff auf MySQL:

https://github.com/ChuckBell/MySQL_Connector_Arduino

Die könnt Ihr vermutlich am schnellsten über die in der Arduino IDE vorhandene Funktion

‘Sketch/Bibliotken einbinden->Bibliotheken verwalten…’

integrieren.

Achtet auf die richtige MySQL-Server IP! Wenn es nicht gleich funktioniert, so kontrolliert noch einmal alles und baut Euch mit ‘Serial.println’ Testausgaben ein, mit denen Ihr überprüfen könnt, wie weit das Programm kommt! Was man nicht so leicht sieht, sind eventuell Rechteprobleme von MySQL oder eine aktive Firewall! Also gewissenhaft den MySQL User einrichten und vielleicht erst einmal über den MySQL Query Browser testen!

MySQL Query Browser

Ich lasse die Datenbank auf einem Raspberry Pi mit der Version 5.5.43 von MySQL laufen. Kann also sein, dass es mit neueren Versionen Probleme gibt. Wenn Ihr MariaDB verwendet, so muss der Port vermutlich 3307 sein!

Der größte Nachteil sind die fest konfigurierten Einstellungen wie die SSID, der MySQL-User, die Passwörter dazu und die IP des Datenbankservers. Das sollte man später durch eine integrierte Webseite ergänzen, die es erlaubt die Einstellungen über einen Browser vorzunehmen. Zugegebenermaßen wird das dann wesentlich mehr Code. 🙂

Aber, hey, es funktioniert! Ich habe noch ein bisschen Code für Deep Sleep dazu gebaut, in das Board einen 18650er Akku gesteckt und das Board in einer Plastikflasche auf den Balkon gestellt. Dort sendet es mir schon seit ein paar Tagen Temperatur und Luftfeuchtigkeit in meine kleine Datenbank.

 

25 Kommentare

  1. Ein nächster sinnvoller Schritt wäre einen Webservice (z.B. REST) als Zwischenschicht vor die DB zu setzen. So wäre das Speichern von Daten auch über Firewallgrenzen hinweg, vor allem aber unabhängig von einer konkreten DB-Implementierung möglich. So ließe sich in Zukunft das Datenbackend umstellen/anpassen usw., ohne das es auf allen Loggern angepasst werden muss…

    Grüße

    1. Vielen Dank für Deinen Hinweis! Ja genau, so sollte man es machen. Ich dachte da gibt es schon Lösungen dafür, oder? Ansonsten wäre dass ja etwas, was für viele IoT Interessierte von Interesse wäre und unbedingt programmiert werden muss.

      1. Bibliotheken für die Erzeugung von Webservices (REST, SOAP usw.) gibt es für alle gängigen Programmiersprachen. Je nach Vorkenntnissen würde ich zu Beginn auf eine Scriptsprache wie PHP, Node.js, Python usw. setzen. Ich arbeite durch langjährige Erfahrungen mit Java gern mit dem Spring-Framework (JVM). Eine coole Lösung für schnelle Lösungen, die später aber noch beliebig skalieren können ist z.B. JHipster.

  2. Hallo,
    ich möchte diesen Code verwenden um meine Lichter zu steuern , jetzt ist mir eine Loop Zeit von 7 Sekunden viel zu lang, 100ms wären da optimaler, ist es möglich das ganze auch in 100ms durchzuführen und in loop laufen lassen? Oder ist das nicht möglich? Gibt es alternativen, sollte das nicht möglich sein?

    Mit freundlichen Grüßen
    Lorenz

  3. Hallo Lorenz,
    warum änderst Du nicht einfach die Wartezeit? Allerdings finde ich ein Abfragerate von 100 ms für das messen von Temperaturwerten in diesem Fall nicht so sinnvoll. So schnell ändert sich dei Temperatur bei diesem Sensor nicht.
    LG Thomas

    1. Hallo,
      danke für die Antwort, hab den Micro Controller noch nicht und klar kann ich einfach die Wartezeit ändern, aber ich hab mich gefragt ob das kleine ding das dann noch packt! In meinem Fall ist es kein Temperatur Sensor sondern ein Taster, der ein Licht betätigen soll!

  4. Aus Deinem Text werde ich nicht ganz schlau. Es hört sich so an, als ob Du gar keine Datenbank benötigst? Wenn es nur darum geht ein Licht über einen Taster einzuschalten, benötigst Du noch nicht einmal eine ESP32.

  5. Mein ESp32 kann keine Verbindung zu MYSQL aufbauen…
    Ich habe schon die Verbindung mit dem MYSQL Query Browser getestet und es funktioniert auf meinen PC, der sich da mit einem Ubuntu-PC verbunden hat, aber der ESP32 kommt nur ins WLAN, versucht es 3 mal und bricht dann erfolglos ab.
    Ich konnte herausfinden, dass ess mit dem “conn.connect(server , 3306, user, password);” zusammenhängt aber meine Daten stimmen meiner Meinung nach.

      1. Hallo,
        Ja der Benutzer ist richtig eingerichtet und ich hab es auch mit anderen Benutzern getestet.
        Ich hab jetzt das Gefühl dass er den MYSQL Server nicht erreicht, weil ich das :
        mysql -uuser -psecret –h192.168.2.114 –port=3306(https://github.com/ChuckBell/MySQL_Connector_Arduino/wiki/Troubleshooting)
        getestet habe und auch mit meiner Domain von außen(wobei ich den Port 3306 am Router eingestellt habe) und dabei das von außen funktioniert hat aber nicht mit der lokalen IP.
        Ich weiß noch nicht ganz genau woran das liegen könnte…
        LG Zniets

        1. Hallo Zniets,
          da kann ich Dir so nicht weiterhelfen. Da müsste ich davor sitzen und selbst alles gewissenhaft prüfen und ausprobieren um das Problem zu lösen. Hast Du einmal die Firewall kurzzeitig abgeschaltet und geprüft, ob es dann geht?
          LG Thomas

  6. Hallo,
    ich möchte mit einem ESP32 in eine Access Datenbank Schreiben, vllt gibt es da den ein oder anderen Tipp für mich 🙂
    Es soll eine feste Zuordnungsnummer, Uhrzeit/Datum und um welches Event(z.B. 1-4) es sich handelt in die Datenbank geschrieben werden.
    Überwacht werden sollen 2 Kabel, die Events sind das Einschalten und das Abschalten von 24v (also 4 verschiedene Events)
    Ich habe mit Visual Basic erste Gehversuche gemacht, bin aber ansonsten noch jungfräulich was die Programmiererei angeht.
    Welches Vorgehen würde sich jetzt für mich anbieten ?
    Mit welcher Programmiersprache kann ich den Zeit/Lernaufwand gering halten ?
    (Programmierbegabung eher so mittel 😀 )
    Vielen Dank schonmal für die Tipps

  7. Hallo Christian,
    mir ist nicht bekannt, das es mit dem ESP32 möglich ist, sich direkt mit einer Access Datenbank zu verbinden. Du könntest aber mit dem ESP32 einen Webserver ansprechen, der die Daten vom ESP32 entgegen nimmt, weiterverarbeitet und sie anschließend in eine Access Datenbank bringt. Da müsstest Du aber mit mindestens zwei Programmiersprachen arbeiten und das Konzept der Webprogrammierung begreifen. Das ist möglicherweise für einen Anfänger ein bisschen zu viel.
    LG Thomas

  8. Ich habe den Verdacht Du unterscheidest nicht zwischen SQL und MySQL, kann das sein? MySQL ist eine Datenbank wie Access auch und vermutlich lassen sich beide mit der Abfragesprache SQL (Structured Query Language) verwenden. Wenn es Dir also um eine Datenbank geht, kannst Du gleich bei MySQL bleiben und brauchst eigentlich kein Access. Oder gibt es zwingende Gründe für die Verwendung von Access?

    1. Die Datenbank, in die die errechneten Zeiten am Ende rein geschrieben werden sollen, ist in Access. Allerdings ist es ja bestimmt möglich die Daten aus der Mysql Datenbank als csv zu exportieren, somit spricht wenig dagegen es erst einmal in MySQL zu speichern.

  9. Moin, wie bereits Zniets am 13.April 21 habe ich dasselbe Problem:
    Serial Monitor:
    conn.connect(server_addr, 3306, user, password); // hier klemmt es !!
    conn.server (xx) ??? (mein println vor und hinter der Befehlszeile)
    …trying…
    …got: 0 retrying…
    …trying…
    …got: 0 retrying…
    …trying…
    …got: 0 retrying…
    conn.server (xx) ??? (mein println vor und hinter der Befehlszeile)
    geht dann aber weiter , bei cursor->execute(statementChar);
    kommt dann der Fehler ERROR: Class requires connected server.
    Sagt ja alles, aber warum?
    mit MySQL Query Browser / HeidiSQL und denselben Login Daten komme ich via lokalem Netzwerk an die Daten. Also ist Firewall und Co ausgeschlossen ?

    1. ja, hab mir im Script extra die Login Daten per serial.print ausgeben lassen, passt. Wie gesagt, mit denselben Daten komme ich per MySQL Query Broser rein. Eine MySQL Log Datei für Anmeldeversuche wäre hilfreich, habe aber nichts gefunden

  10. Ich bin kein Experte bzgl. Netzwerk- und Sicherheitstechnik und muss da meist herum probieren. Um jetzt das Problem einzukreisen müsste ich mich erst einmal wieder damit beschäftigen und das ist ein gewisser Zeitaufwand. Die von mir beschriebene Lösung funktioniert hier allerdings bis zum heutigen Tag. Also der ESP32 macht INSERT’s in die MySQL DB auf einem Raspberry. Es tut mir leid, jetzt hier nicht schnell helfen zu können.

  11. Problem gelöst 🙂
    zum wiederholten Mal neuen SQL User angelegt, natürlich mit ‘%’
    Bei Vergabe der Rechte für DB/Tabelle *.* angelegt, vorher habe ich immer die DB eingetragen. Funktionierte zwar mit SQL-Browser und HeidiSQL, aber nicht via ESP32.
    Danke für deine investierte Zeit und Hirnschmalz , LG Michael

    1. hast du einen Discord Account ich habe das selbe problem aber ich kann es nicht lösen (mariaDB) und das programm worüber ich die daten sehen HeidiSQL

  12. ich brauche Hilfe,

    …trying…
    …got: 0 retrying…
    …trying…
    …got: 0 retrying…
    …trying…
    …got: 0 retrying…
    ERROR: Class requires connected server.

    weiß jemand wie man diesen behebt.

    zu weiteren daten einfach fragen.

    LG Felix

Schreibe einen Kommentar zu Lorenz Antworten abbrechen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.