ESP8266 - MySQL
This tutorial instructs you how to use ESP8266 to insert/update data to MySQL database, or read the data from MySQL database
Hardware Preparation
| 1 | × | ESP8266 NodeMCU | |
| 1 | × | Micro USB Cable | |
| 1 | × | Breadboard | |
| 1 | × | Jumper Wires | |
| 1 | × | (Optional) DC Power Jack | |
| 1 | × | (Optional) Screw Terminal Expansion Board for ESP8266 |
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.
ESP8266 - 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.
ESP8266 can interact with the MySQL database in two ways:
- Directly: ESP8266 connects directly to MySQL server and interacts with MySQL server using MySQL protocol
- Indirectly: ESP8266 connects indirectly to MySQL server via a web server using HTTP/HTTPS protocol.
Which one is the best for ESP8266? Let's find out!
ESP8266 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.
- ESP8266 MUST store and send MySQL queries to MySQL server ⇒ Need to write much ESP8266 code, and also exhaust ESP8266 resources (Memory and CPU usage).
- ESP8266 MUST process a complex MySQL response (very big size of data in some cases) ⇒ This can make ESP8266 run out of memory
- MySQL server must process the raw data ⇒ increases the complexity of MySQL script.
- Most of the MySQL libraries for ESP8266 do not support SSL/TLS ⇒ The data and username/password is sent without encryption ⇒ another security issue.
ESP8266 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: ESP8266 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 ESP8266
- 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 ESP8266 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 ESP8266 and MySQL server.
- PHP script can process the data much easier than the ESP8266 code and MySQL script ⇒ Simplify ESP8266 code and MySQL script
- PHP script can process the data and send only necessary data to ESP8266 (Step 4) to prevent ESP8266 from running out of memory.
- ESP8266 can make HTTPS request easily ⇒ the data is encrypted.
Note that the authentication between ESP8266 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.
ESP8266 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 ESP8266 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 ESP8266 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 ESP8266 and newbiely.com, respectively by coping the below commands and paste on Command Prompt:
CREATE USER 'ESP8266'@'localhost' IDENTIFIED BY 'newbiely.com';
GRANT ALL PRIVILEGES ON *.* TO 'ESP8266'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Command Prompt
MariaDB [(none)]> CREATE USER 'ESP8266'@'localhost' IDENTIFIED BY 'newbiely.com';
Query OK, 0 rows affected (0.005 sec)
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'ESP8266'@'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_esp8266 by typing the following command on Command Prompt:
CREATE DATABASE db_esp8266 CHARACTER SET = 'utf8' COLLATE = 'utf8_general_ci';
Command Prompt
MariaDB [(none)]> CREATE DATABASE db_esp8266 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_esp8266;
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_esp8266;
Database changed
MariaDB [db_esp8266]>
MariaDB [db_esp8266]> 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_esp8266]>
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 = "ESP8266";
$password = "newbiely.com";
$database_name = "db_esp8266";
// 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_esp8266]> SELECT * from tbl_temp;
+---------+------------+
| temp_id | temp_value |
+---------+------------+
| 1 | 26.2 |
+---------+------------+
1 row in set (0.001 sec)
MariaDB [db_esp8266]>
As you can see, the temperature of 26.2 is stored in the database. The next step is to write ESP8266 code that makes a HTTP Request to the web server on your PC.
7. Write ESP8266 code
The below ESP8266 code makes HTTP to your PC to insert a temperature of 30.5°C into the database
/*
* This ESP8266 NodeMCU code was developed by newbiely.com
*
* This ESP8266 NodeMCU code is made available for public use without any restriction
*
* For comprehensive instructions and wiring diagrams, please visit:
* https://newbiely.com/tutorials/esp8266/esp8266-mysql
*/
#include <ESP8266WiFi.h>
#include <ESP8266HTTPClient.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());
WiFiClient client; // Create a WiFiClient object
HTTPClient http;
// Use ::begin(WiFiClient, url) method
http.begin(client, 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 sent 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() {
// Your loop code here (if needed)
}
Detailed Instructions
- If this is the first time you use ESP8266, see how to setup environment for ESP8266 on Arduino IDE.
- Do the wiring as above image.
- Connect the ESP8266 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 ESP8266
- 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_esp8266]> SELECT * from tbl_temp;
+---------+------------+
| temp_id | temp_value |
+---------+------------+
| 1 | 26.2 |
| 2 | 30.5 |
+---------+------------+
2 rows in set (0.000 sec)
MariaDB [db_esp8266]>
As you can see, the temperature 30.5 is stored in database.
How ESP8266 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