IRIV PiControl - Logging Data to MySQL With Node-RED

Hi and welcome back to another tutorial on using IRIV PiControl and Node-RED. In this tutorial, we'll learn how to upload sensor data on MySQL database.

What is MySQL?

MySQL is an open-source relational database management system (RDBMS) that uses Structured Query Language (SQL) for managing and accessing databases. It is widely used in web development and other applications where data storage, retrieval, and management are essential. MySQL is known for its reliability, scalability, and ease of use, making it a popular choice for many developers and organizations. It supports various operating systems and is often used in conjunction with programming languages like PHP, Python, and Java for building dynamic and data-driven applications.

Install MySQL & phpMyAdmin

If you want to use phpMyAdmin, you'll first need to install MySQL or MariaDB on your Raspberry Pi.

Here's how you can install MariaDB on your Raspberry Pi, open the terminal and use these commands:

• Update Package Lists:

sudo apt-get update

• Install MariaDB Server:

sudo apt-get install mariadb-server

• Secure MariaDB Installation:

After installing MariaDB, it's recommended to run the security script to set a root password and improve security settings:

sudo mysql_secure_installation

Follow the on-screen prompts to set up the root password and configure other security options.

• Start MariaDB Service:

If it's not started automatically after installation, start the MariaDB service:

sudo systemctl start mariadb

You can also enable it to start on boot:

sudo systemctl enable mariadb

To install phpMyAdmin on a Raspberry Pi (RPI) and access its web interface, you will need to follow these steps:

• Install Apache Web Server:

sudo apt-get install apache2 -y

• Install PHP:

Install PHP and required modules using the following command:

sudo apt-get install php php-mysql -y

• Install phpMyAdmin:

Install phpMyAdmin package using the command:

sudo apt-get install phpmyadmin -y

During the installation, you will be prompted to choose the web server to configure automatically. Select apache2 using the arrow keys and press Enter.

• Configure phpMyAdmin:

After installation, phpMyAdmin should be accessible via the browser. However, you may need to configure Apache to allow access.

Edit the Apache configuration file using the command:

sudo nano /etc/apache2/apache2.conf

Add the following line at the end of the file:

Include /etc/phpmyadmin/apache.conf

Save the file and exit the text editor (Ctrl + X, then Y, then Enter).

• Restart Apache:

Restart the Apache web server to apply the changes:

sudo systemctl restart apache2

• Access phpMyAdmin

Open a web browser on your computer. Enter the IP address of your Raspberry Pi followed by /phpmyadmin in the address bar (e.g., http://raspberry_pi_ip/phpmyadmin).

You should see the phpMyAdmin login page.

Get familiar With MySQL & Node-RED

To get the basic understanding of how MySQL and Node-RED work together, we will try to enter a single data from Node-RED flow to MySQL database. 

Start by creating a new database, give it a name, and remember it, so we can use it later on in Node-RED. 

Name the table, and adjust the column number to 2.

The first column will be used to display the timestamp, and the second column will be used to store the data that are coming from Node-RED. I’ll call it column1.

Now our database is ready. Let’s move back to Node-RED.

Firstly, we will need to install MySQL nodes from the palette manager.

After that, we will drag MySQL node to the flow. 

Open its properties, type in the user name and password, and include the database name that we have created before, which is test1.

Now drag a function node and open its properties.

Start by creating a new variable and let it store a certain number, so we can display that number in our database later on.

Next, use (msg.topic) to pass the data to MySQL. The code should be formatted like this, adjust the table and column names, then include the variable that is storing the data we want to pass to MySQL.

After that, drag an inject node, and connect all the nodes together this way, and hit Deploy, then hit the inject node.

Now, once we hit the inject node, the data will be sent to the database.

Send Sensor Data to The Database

In this example, we will send 2 kinds of data, the first one will be the readings that are coming from the temperature and humidity sensor, and the second one will be the readings from the power meter.

Firstly we will create a new database, I’ll name it IRIV_database.

Then I’ll create a table called (climate) with 3 columns to store the readings from the temperature and humidity sensor and display the timestamp.

After that, I’ll create another table called (powerdata) with 4 columns to store the voltage, current, and power readings, and also display the timestamp.

Now, Let’s build the program in Node-RED. 

I’ll begin to use an inject node, and activate the repeat feature, and choose how often you want your data to be recorded.

Next, I’m using a modbus-getter node to configure the sensors I’m using. For more information about how these nodes work, check out this tutorial.

Then I'll use a function node to pass the sensor readings to the database.

Inside the function node, I’ve created 2 variables, one for the humidity and the other for temperature. Then I’ve modified the msg.topic line with the right table and column names.

Next, we will drag MySQL node, and configure its settings, make sure to include the new database name.

Next, I’m using another modbus-getter node to configure the power meter with it, and I created a new function to pass the power meter readings to the database.

This function node has three variables, voltage, current, and power, each will be passed to the powerdata table in our database.

 
For more about information about how these nodes work, check out this tutorial.

Use the same MySQL node, and connect all these nodes together.

Now, once your hit deploy, IRIV will begin to collect the data and then send them to our database.

That’s all for this tutorial, thanks for reading, and stay tuned for more.