ESP32 - MySQL

ESP32 can collect sensor data and store it on the MySQL database. ESP32 can also get data (command) from MySQL database and control LED, motor, actuator, device...

In this tutorial, we are going to learn:

Hardware Required

1×ESP-WROOM-32 Dev Module
1×Micro USB Cable
Please note: These are affiliate links. If you buy the components through these links, We may get a commission at no extra cost to you. We appreciate it.

ESP32 - MySQL

If you do not have much knowledge of the system architecture, two terms: MySQL Database and MySQL Server can be understood as the same. You will find the differences later when you know a lot about the system architecture.

There are two ways for ESP32 to interact with the MySQL database:

  • ESP32 interacts directly to MySQL Server via MySQL connection (called direct way)
  • ESP32 interacts indirectly to MySQL Server via HTTP connection (called indirect way)

Let's find the best one.

ESP32 interacts directly to MySQL Server

ESP32 web client

This sounds simpler but there are many disadvantages:

  • This allows a MySQL User account to remotely access MySQL database ⇒ This is dangerous from the security point of view, even if limited privileges were granted to the user account.
  • The data MUST be processed in ESP32 and/or MySQL server ⇒ This increases the complexity of ESP32 code and MySQL script. Especially, it consumes a lot of ESP32 resources (Memory and CPU usage).
  • MySQL server may return a very big amount of data to ESP32 in some cases ⇒ This can make ESP32 run out of memory.
  • Most of the available MySQL libraries do not support SSL/TLS. The data including username/password will be sent in plain text ⇒ another security issue.

ESP32 interacts indirectly to MySQL Server via HTTP/HTTPS

This indirect way solves all problems that the direct way has. Before seeing how the indirect way overcomes the disadvantages of the direct way, let's see how it works first

How it works

  • Step 1: ESP32 makes HTTP Request to Web Server
  • Step 2: Web Server runs PHP script
  • Step 3: PHP script gets data from HTTP Request, processes the data, and then interacts with MySQL database.
  • Step 4: PHP script processes the result and returns the result to ESP32 via HTTP Response
  • ESP32 MySQL HTTP

In this tutorial, the Web server, MySQL server will be installed on PC.

It looks complicated but not. Now Let's see how the indirect way overcomes the disadvantages of the direct way.

  • By installing the MySQL server and HTTP server in the same physical server, We can limit a MySQL User account to access localhost ONLY. Moreover, the username/password of the MySQL account is stored on Server (step 3), this makes the system more secure.
  • Data is processed by a PHP script(step 3 and step 4). This reduces the works and complexity for ESP32 and MySQL servers. Processing data using PHP code is much easier than the ESP32 code and MySQL script.
  • PHP script can process the data and send only necessary data to ESP32 (Step 4) to prevent ESP32 from running out of memory.
  • Most of Ethernet/WiFi libraries supports TLS/SSL that allows us to make HTTPS request. By using HTTPS, the data is encrypted and securely exchanged over the Internet.

In step 1, we can use another username/password to do authentication between ESP32 and Web Server. Please note that the HTTP username/password should be different from the MySQL username/password for security reasons.

With those advantages, the rest of this tutorial will present how to use ESP32 with MySQL via indirect way.

ESP32 - MySQL via HTTP/HTTPS

We need to do the following step:

  • Install MySQL server, Web server, and PHP on your PC
  • Enable MySQL and Web server
  • Create a MySQL User account
  • Create a MySQL database
  • Create a MySQL table
  • Write one or more PHP script files
  • Write ESP32 code

Now let's do it step-by-step.

1. Install MySQL server, Web server, and PHP on your PC

Fortunately, the XAMPP package includes all of these. We just need to install one time

After installing, you will see C:\xampp\htdocs folder on your PC. This is where you put PHP code (see later).

2. Enable MySQL and Web server

  • Open XAMPP Control Panel
  • Click Start button to enable MySQL and Web server (See the below image)
  • ESP32 XAMPP

3. Create a MySQL User account

We will create a MySQL account that can connect to the MySQL database from localhost only.

  • Even if username/password are revealed, the attackers still cannot access your MySQL database unless they take control of your PC.
  • Because PHP and MySQL are installed on the same PC, PHP can use this username/password to connect to the MySQL database.

Let's create a MySQL user account with username is ESP32 and password is esp32io.com:

  • Open Command Prompt on your PC. Do not close it until the end of the tutorial.
  • Type the following command on Command Prompt:
cd C:\xampp\mysql\bin
Command Prompt
C:\Users\youruser>cd C:\xampp\mysql\bin C:\xampp\mysql\bin>
  • By default, MySQL has root account without password. You should add password

(e.g. your-root-password) for root account by typing the following command on Command Prompt:

mysqladmin -u root password your-root-password
Command Prompt
C:\xampp\mysql\bin>mysqladmin -u root password your-root-password C:\xampp\mysql\bin>
  • Type the following command on Command Prompt:
mysql.exe -u root -p
  • Type your-root-password and press Enter
Command Prompt
C:\xampp\mysql\bin>mysql.exe -u root -p Enter password: ****************** Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 10.4.6-MariaDB mariadb.org binary distribution 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)]>
  • Create a MySQL user account with username is ESP32 and password is esp32io.com by coping the below commands and paste on Command Prompt:
CREATE USER 'ESP32'@'localhost' IDENTIFIED BY 'esp32io.com'; GRANT ALL PRIVILEGES ON *.* TO 'ESP32'@'localhost' WITH GRANT OPTION; FLUSH PRIVILEGES;
Command Prompt
MariaDB [(none)]> CREATE USER 'ESP32'@'localhost' IDENTIFIED BY 'esp32io.com'; Query OK, 0 rows affected (0.005 sec) MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'ESP32'@'localhost' WITH GRANT OPTION; Query OK, 0 rows affected (0.005 sec) MariaDB [(none)]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]>

Now you successfully created and MySQL user account. Memorize the username/password, It will be used in PHP script.

4. Create a MySQL database

Let's create a database named db_esp32 by typing the following command on Command Prompt:

CREATE DATABASE db_esp32 CHARACTER SET = 'utf8' COLLATE = 'utf8_general_ci';
Command Prompt
MariaDB [(none)]> CREATE DATABASE db_esp32 CHARACTER SET = 'utf8' COLLATE = 'utf8_general_ci'; Query OK, 1 row affected (0.003 sec) MariaDB [(none)]>

5. Create a MySQL table

Let's create a database named tbl_temp by coping the below commands and paste on Command Prompt:

USE db_esp32; CREATE TABLE tbl_temp ( temp_id INT UNSIGNED NOT NULL AUTO_INCREMENT, temp_value FLOAT DEFAULT 0.00, PRIMARY KEY (temp_id) );
Command Prompt
MariaDB [(none)]> USE db_esp32; Database changed MariaDB [db_esp32]> MariaDB [db_esp32]> CREATE TABLE tbl_temp ( -> temp_id INT UNSIGNED NOT NULL AUTO_INCREMENT, -> temp_value FLOAT DEFAULT 0.00, -> PRIMARY KEY (temp_id) -> ); Query OK, 0 rows affected (0.044 sec) MariaDB [db_esp32]>

6. Write one or more PHP files

Create a PHP file named insert_temp.php that gets temperature from HTTP Request and inserts it into the database.

<?php if(isset($_GET["temperature"])) { $temperature = $_GET["temperature"]; // get temperature value from HTTP GET $servername = "localhost"; $username = "ESP32"; $password = "esp32io.com"; $dbname = "db_esp32"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $sql = "INSERT INTO tbl_temp (temp_value) VALUES ($temperature)"; if ($conn->query($sql) === TRUE) { echo "New record created successfully"; } else { echo "Error: " . $sql . " => " . $conn->error; } $conn->close(); } else { echo "temperature is not set"; } ?>
  • Place this file inside C:\xampp\htdocs folder
  • Get your PC's IP address. If you do not know how to, google it.
  • Test PHP code by open a web browser (e.g. Chrome) and access this link: http://192.168.0.26/insert_temp.php?temperature=27.5 . Note that you need to replace the above IP address with your PC address.
  • The output on the web browser
  • MySQL Test
  • Check whether data is stored in database by typing the following command on Command Prompt:
SELECT * from tbl_temp;
Command Prompt
MariaDB [db_esp32]> SELECT * from tbl_temp; +---------+------------+ | temp_id | temp_value | +---------+------------+ | 1 | 27.5 | +---------+------------+ 1 row in set (0.001 sec) MariaDB [db_esp32]>

As you can see, the temperature of 27.5 is stored in the database. The next step is to write ESP32 that makes a similar HTTP Request to your PC.

7. Write ESP32 code

The below ESP32 code makes HTTP to your PC to insert a temperature of 29.1°C into the database

/* * Created by esp32io.com * * This example code is in the public domain * * Tutorial page: https://esp32io.com/tutorials/esp32-mysql */ #include <WiFi.h> #include <HTTPClient.h> const char WIFI_SSID[] = "YOUR_WIFI_SSID"; const char WIFI_PASSWORD[] = "YOUR_WIFI_PASSWORD"; String HOST_NAME = String HOST_NAME = String HOST_NAME = String HOST_NAME = tring HOST_NAME = ring HOST_NAME = ing HOST_NAME = ng HOST_NAME = g HOST_NAME = HOST_NAME = HOST_NAME = OST_NAME = ST_NAME = T_NAME = _NAME = NAME = AME = "ME = "hE = "ht = "htt= "http "http://192.168.0.26"; // change to your PC's IP address String PATH_NAME = "/insert_temp.php"; String queryString = "?temperature=29.1"; void setup() { Serial.begin(9600); WiFi.begin(WIFI_SSID, WIFI_PASSWORD); Serial.println("Connecting"); while(WiFi.status() != WL_CONNECTED) { delay(500); Serial.print("."); } Serial.println(""); Serial.print("Connected to WiFi network with IP Address: "); Serial.println(WiFi.localIP()); HTTPClient http; http.begin(HOST_NAME + PATH_NAME + queryString); //HTTP int httpCode = http.GET(); // httpCode will be negative on error if(httpCode > 0) { // file found at server if(httpCode == HTTP_CODE_OK) { String payload = http.getString(); Serial.println(payload); } else { // HTTP header has been send and Server response header has been handled Serial.printf("[HTTP] GET... code: %d\n", httpCode); } } else { Serial.printf("[HTTP] GET... failed, error: %s\n", http.errorToString(httpCode).c_str()); } http.end(); } void loop() { }

Quick Steps

  • If this is the first time you use ESP32, see how to setup environment for ESP32 on Arduino IDE
  • Connect the ESP32 board to your PC via a micro USB cable
  • Change IP address on the code by your PC's IP address
  • Compile and upload code to ESP32
  • Open Serial Monitor on Arduino IDE
  • How to open serial monitor on Arduino IDE
  • The result on Serial Monitor
  • COM6
    Send
    Connected to server HTTP/1.1 200 OK Date: Tue, 12 Jan 2021 07:52:22 GMT Server: Apache/2.4.39 (Win64) OpenSSL/1.1.1c PHP/7.3.8 X-Powered-By: PHP/7.3.8 Content-Length: 31 Connection: close Content-Type: text/html; charset=UTF-8 New record created successfully disconnected
    Autoscroll Show timestamp
    Clear output
    9600 baud  
    Newline  
  • Check whether data is stored in database by typing the following command on Command Prompt:
SELECT * from tbl_temp;
Command Prompt
MariaDB [db_esp32]> SELECT * from tbl_temp; +---------+------------+ | temp_id | temp_value | +---------+------------+ | 1 | 27.5 | | 2 | 29.1 | +---------+------------+ 2 rows in set (0.000 sec) MariaDB [db_esp32]>

As you can see, the temperature 29.1 is stored in database.

How ESP32 insert, update or get data to/from MySQL database

In the above example, we have learned how to insert data into the MySQL database. For updating and getting data from the database, it is similar. You only need to change MySQL queries on the PHP script. You can learn more from W3Schools

Advanced Usage

To increase the security

※ NOTE THAT:

To make a complete system with the highest security level, we need to do more (such as MySQL injection prevention, making HTTPS become REST API, using Json format for data ...). Howerver, this tutorial is dedicated for beginners to learn ESP32. We made it as simple as possible. After learning this tutorial, users can expand it.

Video Tutorial

We are considering to make the video tutorials. If you think the video tutorials are essential, please subscribe to our YouTube channel to give us motivation for making the videos.

※ NOTE THAT:

Note that this tutorial is incomplete. We will post on our Facebook Page when the tutorial is complete. Like it to get updated.

※ NOTICES

  • We are AVAILABLE for HIRE. See how to hire us to do your project
  • If this tutorial is useful for you, please support us to make more tutorials.
  • We spent a lot of time and effort to create the content for this tutorial, please respect our work! Please do not copy the content to share on other websites. Howerver, please feel free to share the link of this tutorial anywhere