SENSORS/ACTUATORS

INTERNET OF THING (IoT)

ESP32 - MySQL

This tutorial instructs you how to use ESP32 to insert/update data to MySQL database, or read the data from MySQL database

Hardware Used In This Tutorial

1	×	ESP-WROOM-32 Dev Module
1	×	USB Cable Type-C
1	×	(Optional) DC Power Jack
1	×	Breadboard
1	×	Jumper Wires
1	×	(Recommended) Screw Terminal Expansion Board for ESP32
1	×	(Recommended) Power Splitter For ESP32

Or you can buy the following sensor kits:

1	×	DIYables Sensor Kit (30 sensors/displays)
1	×	DIYables Sensor Kit (18 sensors/displays)

Disclosure: Some of the links in this section are Amazon affiliate links, meaning we may earn a commission at no additional cost to you if you make a purchase through them. Additionally, some links direct you to products from our own brand, DIYables.

ESP32 - MySQL

There are two terms in MySQL that beginners usually get confused: MySQL Database and MySQL server. They are different. Howerver, if you are a beginner, you can assume that they are the same. Later, You will find the differencesin your learning process.

ESP32 can interact with the MySQL database in two ways:

Directly: ESP32 connects directly to MySQL server and interacts with MySQL server using MySQL protocol
Indirectly: ESP32 connects indirectly to MySQL server via a web server using HTTP/HTTPS protocol.

Which one is the best for ESP32? Let's find out!

ESP32 interacts directly to MySQL Server

Interacting with MySQL directly seems to be simple but there are a lot of drawbacks:

We have to grant remote access permissions to a MySQL user account ⇒ This is risky in the security aspect, even if we grant a limited privileges the user account.
ESP32 MUST store and send MySQL queries to MySQL server ⇒ Need to write much ESP32 code, and also exhaust ESP32 resources (Memory and CPU usage).
ESP32 MUST process a complex MySQL response (very big size of data in some cases) ⇒ This can make ESP32 run out of memory
MySQL server must process the raw data ⇒ increases the complexity of MySQL script.
Most of the MySQL libraries for ESP32 do not support SSL/TLS ⇒ The data and username/password is sent without encryption ⇒ another security issue.

ESP32 interacts indirectly to MySQL Server via HTTP/HTTPS

Interacting with MySQL indirectly via HTTP/HTTPS solves all problems of the direct access.

How it works

Step 1: ESP32 includes the data to HTTP/HTTPS request and send the request to the Web server
Step 2: Web server runs a PHP script that handles the request from ESP32
Step 3: PHP script extracts the data from the HTTP request, processes the data, and then interacts with MySQL database.
Step 4: PHP script processes the result and returns only the necessary result to ESP32 via HTTP response

We are going to install MySQL server, Web server, and PHP on the PC. In the fact, we can install it on a dedicated server or cloud service such as AWS EC2.

The below is how the indirect way solve the problems of the direct way.

We can install HTTP server and the MySQL server in the same physical server, We can give a limit access to a MySQL user account(e.g. localhost access ONLY) ⇒ secure
The username/password of the MySQL account is stored on the server ⇒ secure.
Data is processed by a PHP script ⇒ This reduces the load and complexity for ESP32 and MySQL server.
PHP script can process the data much easier than the ESP32 code and MySQL script ⇒ Simplify 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.
ESP32 can make HTTPS request easily ⇒ the data is encrypted.

Note that the authentication between ESP32 and Web Server should be independent with MySQL authentication. For example, the HTTP username/password should be different from the MySQL username/password.

Because of those advantages, This tutorial will use the indirect way.

ESP32 To MySQL via HTTP/HTTPS

The below are steps that we need to do:

Installing XAMPP package that includes MySQL server, Web server, and PHP on your PC
Creating a MySQL user account
Creating a MySQL database
Creating a MySQL table
Writing one or more PHP script files
Writing ESP32 code

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

Download and install the XAMPP from this link. After installation, check the C:\xampp\htdocs folder on your PC. This is where you put PHP code (see later).
Open XAMPP Control Panel
Click Start buttons to enable MySQL and Web server (as the below image)

2. Creating a MySQL User account

We will create a MySQL account with localhost access permissions only.

Even if attackers know the username/password, they cannot access your MySQL database unless they take control of your PC.
This username/password will be used by PHP to connect to the MySQL database.

This tutorial creates a MySQL user account with username and password are ESP32 and esp32io.com, respectively:

Open the 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\YOUR_USER>cd C:\xampp\mysql\bin C:\xampp\mysql\bin>

By default, MySQL has THE root account without password. It is highly recommend to set the password for the root account (e.g. YOUR_ROOT_PASSWORD) for the security reason. Type the below command on the 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 below command on Command Prompt to login to MySQL server:

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)]>

Let's create a MySQL user account with username and passowrd are ESP32 and esp32io.com, respectively 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)]>

You created and MySQL user account successfully. Write down the username/password. It will be used in PHP script.

3. Creating a MySQL database

Create a database 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)]>

4. Creating a MySQL table

Create a table 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 PHP script files

Create a insert_temp.php by using any text editor (e.g. Notepad/Notepad++). We will write the script in this file to extract the temperature value from HTTP Request and inserts the temperature value into the database.

<?php if(isset($_GET["temperature"])) { $temperature = $_GET["temperature"]; // get temperature value from HTTP GET $servername = "localhost"; $username = "ESP32"; $password = "esp32io.com"; $database_name = "db_esp32"; // Create MySQL connection fom PHP to MySQL server $connection = new mysqli($servername, $username, $password, $database_name); // Check connection if ($connection->connect_error) { die("MySQL connection failed: " . $connection->connect_error); } $sql = "INSERT INTO tbl_temp (temp_value) VALUES ($temperature)"; if ($connection->query($sql) === TRUE) { echo "New record created successfully"; } else { echo "Error: " . $sql . " => " . $connection->error; } $connection->close(); } else { echo "temperature is not set in the HTTP request"; } ?>

Put 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.19/insert_temp.php?temperature=26.2. Note that you need to replace the above IP address with your PC address.
The web browser shows as below:

Check if 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 | 26.2 | +---------+------------+ 1 row in set (0.001 sec) MariaDB [db_esp32]>

As you can see, the temperature of 26.2 is stored in the database. The next step is to write ESP32 code that makes a HTTP Request to the web server on your PC.

7. Write ESP32 code

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

/* * This ESP32 code is created by esp32io.com * * This ESP32 code is released in the public domain * * For more detail (instruction and wiring diagram), visit 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 = "http://192.168.0.19"; // change to your PC's IP address String PATH_NAME = "/insert_temp.php"; String queryString = "?temperature=30.5"; 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 Instructions

If this is the first time you use ESP32, see how to setup environment for ESP32 on Arduino IDE.
Do the wiring as above image.
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 if data is stored in database by using the following command on Command Prompt:

SELECT * from tbl_temp;

Command Prompt

MariaDB [db_esp32]> SELECT * from tbl_temp; +---------+------------+ | temp_id | temp_value | +---------+------------+ | 1 | 26.2 | | 2 | 30.5 | +---------+------------+ 2 rows in set (0.000 sec) MariaDB [db_esp32]>

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

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

The above example shows how to insert data into the MySQL database. It is similar for updating and reading data from the database. You just need to modify MySQL query on the PHP code. You can learn more on W3Schools.com

Video Tutorial

Making video is a time-consuming work. If the video tutorial is necessary for your learning, please let us know by subscribing to our YouTube channel , If the demand for video is high, we will make the video tutorial.

Learn More

※ OUR MESSAGES

As freelancers, We are AVAILABLE for HIRE. See how to outsource your project to us
If this tutorial is useful for you, please support us to make more tutorials.
Please feel free to share the link of this tutorial. However, Please do not use our content on any other websites. We invested a lot of effort and time to create the content, please respect our work!