SENSORS/ACTUATORS

INTERNET OF THING (IoT)

LIBRARIES

Arduino - MySQL

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

In this tutorial, we are going to learn:

The best way for Arduino to interact with MySQL database
How Arduino insert data to MySQL database
How Arduino update data to MySQL database
How Arduino get data from MySQL database

The tutorial provides the Arduino code for two cases:

Arduino Uno R4 WiFi
Arduino Uno/Mega with Ethernet Shield 2

Hardware Required

1	×	Arduino UNO R4 WiFi
1	×	USB Cable Type-C
1	×	(Recommended) Screw Terminal Block Shield for Arduino Uno
1	×	(Optional) Transparent Acrylic Enclosure For Arduino Uno

Alternatively if using Ethernet:

1	×	Arduino UNO or Genuino UNO
1	×	USB 2.0 cable type A/B
1	×	Arduino Ethernet Shield 2
1	×	Ethernet Cable
1	×	(Optional) 9V Power Adapter for Arduino
1	×	(Recommended) Screw Terminal Block Shield for Arduino Uno
1	×	(Optional) Transparent Acrylic Enclosure For Arduino Uno

Or you can buy the following sensor kit:

DIYables Sensor Kit 30 types, 69 units

Please note: These are Amazon affiliate links. If you buy the components through these links, We will get a commission at no extra cost to you. We appreciate it.

Arduino - 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 Arduino to interact with the MySQL database:

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

Let's find the best one.

Arduino interacts directly to MySQL Server

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 Arduino and/or MySQL server ⇒ This increases the complexity of Arduino code and MySQL script. Especially, it consumes a lot of Arduino resources (Memory and CPU usage).
MySQL server may return a very big amount of data to Arduino in some cases ⇒ This can make Arduino 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.

Arduino 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: Arduino 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 Arduino via HTTP Response

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 Arduino and MySQL servers. Processing data using PHP code is much easier than the Arduino code and MySQL script.
PHP script can process the data and send only necessary data to Arduino (Step 4) to prevent Arduino 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 Arduino 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 Arduino with MySQL via indirect way.

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

Download XAMPP from this link
Install it.

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)

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 Arduino and password is ArduinoGetStarted.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 Arduino and password is ArduinoGetStarted.com by coping the below commands and paste on Command Prompt:

CREATE USER 'Arduino'@'localhost' IDENTIFIED BY 'ArduinoGetStarted.com'; GRANT ALL PRIVILEGES ON *.* TO 'Arduino'@'localhost' WITH GRANT OPTION; FLUSH PRIVILEGES;

Command Prompt

MariaDB [(none)]> CREATE USER 'Arduino'@'localhost' IDENTIFIED BY 'ArduinoGetStarted.com'; Query OK, 0 rows affected (0.005 sec) MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'Arduino'@'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_arduino by typing the following command on Command Prompt:

CREATE DATABASE db_arduino CHARACTER SET = 'utf8' COLLATE = 'utf8_general_ci';

Command Prompt

MariaDB [(none)]> CREATE DATABASE db_arduino 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 table named tbl_temp by coping the below commands and paste on Command Prompt:

USE db_arduino; 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_arduino; Database changed MariaDB [db_arduino]> MariaDB [db_arduino]> 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_arduino]>

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 = "Arduino"; $password = "ArduinoGetStarted.com"; $dbname = "db_arduino"; // 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

Check whether data is stored in database by typing the following command on Command Prompt:

SELECT * from tbl_temp;

Command Prompt

MariaDB [db_arduino]> SELECT * from tbl_temp; +---------+------------+ | temp_id | temp_value | +---------+------------+ | 1 | 27.5 | +---------+------------+ 1 row in set (0.001 sec) MariaDB [db_arduino]>

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

7. Write Arduino code

We will use Arduino Uno and Ethernet Shield or Arduino Uno R4 WiFi for the test

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

Arduino Code for Arduino Uno R4 WiFi

/* * Created by ArduinoGetStarted.com * * This example code is in the public domain * * Tutorial page: https://arduinogetstarted.com/tutorials/arduino-mysql */ #include <WiFiS3.h> const char ssid[] = "YOUR_WIFI_SSID"; // change your network SSID (name) const char pass[] = "YOUR_WIFI_PASSWORD"; // change your network password (use for WPA, or use as key for WEP) WiFiClient client; int status = WL_IDLE_STATUS; int HTTP_PORT = 80; String HTTP_METHOD = "GET"; char HOST_NAME[] = "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); // check for the WiFi module: if (WiFi.status() == WL_NO_MODULE) { Serial.println("Communication with WiFi module failed!"); // don't continue while (true) ; } String fv = WiFi.firmwareVersion(); if (fv < WIFI_FIRMWARE_LATEST_VERSION) { Serial.println("Please upgrade the firmware"); } // attempt to connect to WiFi network: while (status != WL_CONNECTED) { Serial.print("Attempting to connect to SSID: "); Serial.println(ssid); // Connect to WPA/WPA2 network. Change this line if using open or WEP network: status = WiFi.begin(ssid, pass); // wait 10 seconds for connection: delay(10000); } // print your board's IP address: Serial.print("IP Address: "); Serial.println(WiFi.localIP()); // connect to web server on port 80: if (client.connect(HOST_NAME, HTTP_PORT)) { // if connected: Serial.println("Connected to server"); // make a HTTP request: // send HTTP header client.println(HTTP_METHOD + " " + PATH_NAME + queryString + " HTTP/1.1"); client.println("Host: " + String(HOST_NAME)); client.println("Connection: close"); client.println(); // end HTTP header while (client.connected()) { if (client.available()) { // read an incoming byte from the server and print it to serial monitor: char c = client.read(); Serial.print(c); } } // the server's disconnected, stop the client: client.stop(); Serial.println(); Serial.println("disconnected"); } else { // if not connected: Serial.println("connection failed"); } } void loop() { }

Arduino Code for Arduino Uno/Mega and Ethernet Shield

/* * Created by ArduinoGetStarted.com * * This example code is in the public domain * * Tutorial page: https://arduinogetstarted.com/tutorials/arduino-mysql */ #include <SPI.h> #include <Ethernet.h> // replace the MAC address below by the MAC address printed on a sticker on the Arduino Shield 2 byte mac[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED }; EthernetClient client; int HTTP_PORT = 80; String HTTP_METHOD = "GET"; char HOST_NAME[] = "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); // initialize the Ethernet shield using DHCP: if (Ethernet.begin(mac) == 0) { Serial.println("Failed to obtaining an IP address using DHCP"); while(true); } // connect to web server on port 80: if(client.connect(HOST_NAME, HTTP_PORT)) { // if connected: Serial.println("Connected to server"); // make a HTTP request: // send HTTP header client.println(HTTP_METHOD + " " + PATH_NAME + queryString + " HTTP/1.1"); client.println("Host: " + String(HOST_NAME)); client.println("Connection: close"); client.println(); // end HTTP header while(client.connected()) { if(client.available()){ // read an incoming byte from the server and print it to serial monitor: char c = client.read(); Serial.print(c); } } // the server's disconnected, stop the client: client.stop(); Serial.println(); Serial.println("disconnected"); } else {// if not connected: Serial.println("connection failed"); } } void loop() { }

Quick Steps

If using the Ethernet Shield, stack Ethernet Shield on Arduino Uno
Connect Ethernet Cable to Ethernet Shield
Connect Arduino Uno to PC via USB cable
Change IP address on the code by your PC's IP address
Compile and upload code to Arduino
Open Serial Monitor
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_arduino]> SELECT * from tbl_temp; +---------+------------+ | temp_id | temp_value | +---------+------------+ | 1 | 27.5 | | 2 | 29.1 | +---------+------------+ 2 rows in set (0.000 sec) MariaDB [db_arduino]>

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

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

You can change Arduino code to make HTTPS instead of HTTP. See Arduino - HTTPS
You can use a username/password to do authentication between Arduino and the Web server. See Basic access authentication

※ 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 Arduino. 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.

The Best Arduino Starter Kit

See the best Arduino kit for beginner

Arduino - MySQL

Hardware Required

Arduino - MySQL

Arduino interacts directly to MySQL Server

Arduino interacts indirectly to MySQL Server via HTTP/HTTPS

How it works

Arduino - MySQL via HTTP/HTTPS

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

2. Enable MySQL and Web server

3. Create a MySQL User account

4. Create a MySQL database

5. Create a MySQL table

6. Write one or more PHP files

7. Write Arduino code

Arduino Code for Arduino Uno R4 WiFi

Arduino Code for Arduino Uno/Mega and Ethernet Shield

Quick Steps

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

Advanced Usage

Video Tutorial

The Best Arduino Starter Kit

See Also