Arduino Nano ESP32 - MySQL
This tutorial provides instructions on how to use Arduino Nano ESP32 to insert/update data to MySQL database, or read the data from MySQL database
Hardware Preparation
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 provided in this section are Amazon affiliate links. We may receive a commission for any purchases made through these links at no additional cost to you.
Additionally, some of these links are for products from our own brand, DIYables.
Additionally, some of these links are for products from our own brand, DIYables.
Arduino Nano 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.
Arduino Nano ESP32 can interact with the MySQL database in two ways:
- Directly: Arduino Nano ESP32 connects directly to MySQL server and interacts with MySQL server using MySQL protocol
- Indirectly: Arduino Nano 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!
Arduino Nano 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.
- Arduino Nano ESP32 MUST store and send MySQL queries to MySQL server ⇒ Need to write much Arduino Nano ESP32 code, and also exhaust Arduino Nano ESP32 resources (Memory and CPU usage).
- Arduino Nano ESP32 MUST process a complex MySQL response (very big size of data in some cases) ⇒ This can make Arduino Nano ESP32 run out of memory
- MySQL server must process the raw data ⇒ increases the complexity of MySQL script.
- Most of the MySQL libraries for Arduino Nano ESP32 do not support SSL/TLS ⇒ The data and username/password is sent without encryption ⇒ another security issue.
Arduino Nano 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: Arduino Nano 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 Arduino Nano 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 Arduino Nano 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 Arduino Nano ESP32 and MySQL server.
- PHP script can process the data much easier than the Arduino Nano ESP32 code and MySQL script ⇒ Simplify Arduino Nano ESP32 code and MySQL script
- PHP script can process the data and send only necessary data to Arduino Nano ESP32 (Step 4) to prevent Arduino Nano ESP32 from running out of memory.
- Arduino Nano ESP32 can make HTTPS request easily ⇒ the data is encrypted.
Note that the authentication between Arduino Nano 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.
Arduino Nano 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 Arduino Nano 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 newbiely.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 newbiely.com, respectively by coping the below commands and paste on Command Prompt:
CREATE USER 'ESP32'@'localhost' IDENTIFIED BY 'newbiely.com';
GRANT ALL PRIVILEGES ON *.* TO 'ESP32'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Command Prompt
MariaDB [(none)]> CREATE USER 'ESP32'@'localhost' IDENTIFIED BY 'newbiely.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_nano_esp32 by typing the following command on Command Prompt:
CREATE DATABASE db_nano_esp32 CHARACTER SET = 'utf8' COLLATE = 'utf8_general_ci';
Command Prompt
MariaDB [(none)]> CREATE DATABASE db_nano_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_nano_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_nano_esp32;
Database changed
MariaDB [db_nano_esp32]>
MariaDB [db_nano_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_nano_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 = "newbiely.com";
$database_name = "db_nano_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_nano_esp32]> SELECT * from tbl_temp;
+---------+------------+
| temp_id | temp_value |
+---------+------------+
| 1 | 26.2 |
+---------+------------+
1 row in set (0.001 sec)
MariaDB [db_nano_esp32]>
As you can see, the temperature of 26.2 is stored in the database. The next step is to write Arduino Nano ESP32 code that makes a HTTP Request to the web server on your PC.
7. Write Arduino Nano ESP32 code
The below Arduino Nano ESP32 code makes HTTP to your PC to insert a temperature of 30.5°C into the database
/*
* This Arduino Nano ESP32 code was developed by newbiely.com
*
* This Arduino Nano ESP32 code is made available for public use without any restriction
*
* For comprehensive instructions and wiring diagrams, please visit:
* https://newbiely.com/tutorials/arduino-nano-esp32/arduino-nano-esp32-mysql
*/
#include <WiFi.h>
#include <HTTPClient.h>
const char* WIFI_SSID = "YOUR_WIFI_SSID"; // CHANGE IT
const char* WIFI_PASSWORD = "YOUR_WIFI_PASSWORD"; // CHANGE IT
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() {
}
Detailed Instructions
- If this is the first time you use Arduino Nano ESP32, see how to setup environment for Arduino Nano ESP32 on Arduino IDE.
- Do the wiring as above image.
- Connect the Arduino Nano ESP32 board to your PC via a USB cable
- Change IP address on the code by your PC's IP address
- Compile and upload code to Arduino Nano ESP32
- Open Serial Monitor on Arduino IDE
- The result on Serial Monitor
COM6
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
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_nano_esp32]> SELECT * from tbl_temp;
+---------+------------+
| temp_id | temp_value |
+---------+------------+
| 1 | 26.2 |
| 2 | 30.5 |
+---------+------------+
2 rows in set (0.000 sec)
MariaDB [db_nano_esp32]>
As you can see, the temperature 30.5 is stored in database.
How Arduino Nano 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