Php сервер на esp32

ESP32 — MySQL

Disclosure: some of these links are affiliate links. We may earn a commission on your purchase at no extra cost to you. We appreciate it.

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

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

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

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.

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:

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

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 tutorial creates a MySQL user account with username and password are ESP32 and esp32io.com , respectively:

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:

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;

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:

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

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.

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.

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

Источник