Hello Friends. In this instructable, I will show you how to use the Node-RED mysql node to make a simple CPU temperature logger. Let's get started.

This will also be a beginner's guide to learning:

Node-RED, its possibilities, and main nodes.

Installation of PHPMyAdmin and MySQL.

Javascript Function Node in Node-RED.

Sorry for the blurr picture.

Step 1: Things Needed

For this project I am using a Raspberry Pi Zero because it is small and cheap It can easily fit into your pocket. So your software travels along with you. But you can use any computer of your choice, as long as it is Debian Linux based.

You will also need an Internet Connection and local access to your computer via the terminal (or ssh).

If you are using Mac or Linux:-

$ ssh pi@your_pi's_ip_address

If you are using Windows, download and install Putty:-

https://www.chiark.greenend.org.uk/~sgtatham/putty...

Step 2: Login

Default Raspbian login :-

pi and raspberry.

Next, run these commands.

sudo apt-get update

sudo apt-get upgrade

sudo apt-get install nodejs npm nodered.

Note that you won't get nodered readily vailable if you are using an older version of Raspbian, or Ubuntu 18.04 LTS or Debian 9

In such cases you must manually run the Node-RED Install script :-

bash <(curl -sL https://raw.githubusercontent.com/node-red/raspbian-deb-package/master/resources/update-nodejs-and-nodered)

Step 3: Few More Commands

After you have everything installed run

sudo apt-get install -y mysql-server php-mysql phpmyadmin apache2

Online you can also find instructions on how to install phpmyadmin and mysql.

sudo nano /etc/apache2/apache2.conf

Go to the bottom and add

Include /etc/phpmyadmin/apache.conf

Save using Ctrl + O, Enter. Exit with CTrl + x

sudo /etc/init.d/apache2 restart

If phpmyadmin is installed successfully you should be able to login to localhost/phpmyadmin

Replace localhost with your Pi's IP.

Step 4: Node-RED and PHPMyAdmin

Node-RED lets you create graphically connected programs or flows. Learn more about Node-RED at https://nodered.org

For now, I will show you how to create a simple logger.

Go to http://raspberrypi.local:1880

Follow the pictures.

This is the JSON string

[{"id":"7c27ad7b.907564","type":"tab","label":"MySQL Test","disabled":false,"info":"This is a flow to demonstarte the MySQL node in Node-Red.\nWe measure the temperature of the Raspberry Pi's CPU and log it int the database."},{"id":"abb00580.da71b8","type":"inject","z":"7c27ad7b.907564","name":"Trigger","topic":"","payload":"","payloadType":"date","repeat":"2","crontab":"","once":false,"onceDelay":0.1,"x":120,"y":120,"wires":[["4b58b33f.21a98c"]]},{"id":"4b58b33f.21a98c","type":"exec","z":"7c27ad7b.907564","command":"vcgencmd","addpay":true,"append":"measure_temp","useSpawn":"false","timer":"","oldrc":false,"name":"Command","x":330,"y":180,"wires":[["aecee5b2.fe7388"],[],[]]},{"id":"aecee5b2.fe7388","type":"function","z":"7c27ad7b.907564","name":"Query","func":"/*\nIn this function I enter my Raspberry Pi's CPU\ntemperature using the command \"vcgencmd measure_temp\".\nI then store this temperature in the variable temp,\ndo the necessary formatting and then send a database\nquery to enter the temperature in a database named\nNode-RED-test and the field named Field in the table\nnamed test, every two seconds.\n*/\nvar temp = msg.payload.replace(\"temp=\", \"\").replace(\"'C\\n\", \"\"); //Only give the number\nquery = `INSERT INTO test(Field) VALUES(`+temp+`);`; //Creates query with the recorded temp value\nmsg.topic = query; //Put query into the msg.topic\nreturn msg; //Return the query","outputs":1,"noerr":0,"x":570,"y":220,"wires":[["e6bdfff5.ee1c4","f2028a4.d529f78"]]},{"id":"e6bdfff5.ee1c4","type":"debug","z":"7c27ad7b.907564","name":"Query String","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"topic","x":870,"y":420,"wires":[]},{"id":"f2028a4.d529f78","type":"mysql","z":"7c27ad7b.907564","mydb":"b916f977.ae8a78","name":"Database","x":740,"y":80,"wires":[["aca1d3b2.ce954"]]},{"id":"aca1d3b2.ce954","type":"debug","z":"7c27ad7b.907564","name":"Payload (Status)","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":850,"y":180,"wires":[]},{"id":"b916f977.ae8a78","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"Node-RED-test","tz":""}]

Copy and paste this string as it is in the place shown in the picture.

I've also posted the pictures showing the Entries in PHPMyAdmin.

Step 5: Note Few Things

A few important things to note are the IP address. It might be different. Also, you must first create a database named Node-RED-test, a table named test and name the field " Field ". You can achieve all this with the help of the PHPMyAdmin web tool. It makes handing databases a piece of cake. There are plenty of resources online to help you. You need to install vcgencmd on your computer. It is the main tool that allows you to view your system's information. It comesn pre-installed on Raspbian Stretch.

To run Node-RED:-

1)directly-

$ node-red-start

2) On every boot-

$ sudo systemctl enable nodered.service

Step 6: Your Very Own CPU_Temp_Logger!

Now if you browse the database in phpmyadmin as shown in the picture, you will notice entries added to your table with the temperatures of your CPU.

The function Node is whats the key here. It allows you to filter messages and sends the query along with the temp variable. I have explained it in the function node. Check it out. The entries are made every two seconds, but you can change the delay in the inject node.

Have Fun :)

Please like this project and post comments, cause they really help a lot. Also, be sure to point out any mistakes I may have made, and feel free to ask questions.

Thank You For Viewing this article.

Bye !!!