Tag Archives: MQTT and MYSQL on Node Red

Pi Graphing Progress

Making great progress with the Raspberry Pi despite not knowing Linux from a hole in the ground a week ago.  We’re at our place in Spain for a short break with limited facilities (I’ve ordered a USB scope for the next time we’re here).

As of this morning, I’ve managed to get Node-Red running on the Pi 2 (well, the more important modules anyway including Twitter, email, ports and MQTT and I can turn the facility on and off as a service. A hell of a lot of the modules have dependencies that don’t seem to want to load).  I also have MYSQL in there.

I have just finished a PHP page running on Apache with JP-Graphs (free and wonderful) all on the Pi 2, taking data from MYSQL and plotting.


So what you’re looking at above is dummy data on the Pi, served on a Pi web page – the blue line is coming from the MYSQL database but after I’ve finished writing this and entering more dummy data all 3 will come from the database. The next stage will be to take in MQTT message from ESP8266 devices which will be sent to mysql every minute but logged less frequently until I find how more about how the Pi uses the SD memory – bearing in mind this stuff has limited write life. writing rules for temperature control will be a different matter as I’ve no gut feeling for the scripting language yet.

Coming along quite nicely – between that and the discovery that I can get NETIO to talk to Node-Red, I might not need OpenHab after all.

On the Arduino side we’re looking at making a development of what we call the Aiduino, an Atmega1284-based 5v board, taking it down to 3v3 and sticking an ESP-12 on the end – why? Because I have it on good authority that we’re near to getting some code to handle both MQTT and serial control for the ESPs… which makes matching them with Arduino good sense – the Arduino can handle the intensive stuff like fading serial LEDs without interfering with incoming MQTT messages.  Very exciting stuff.  

Don’t ask me how I installed Node-Red, it was bodged but before long the Node-Red site itself will have updated info for Pi 2 no doubt.

It did not help that the Node-red block “mysql” doesn’t exactly have millions of examples – but with a little help from the one of the guys maintaining it – I cracked that, too. Here’s an example of getting a value into a mysql database from MQTT.



So I have an MQTT node taking in from the topic “testtemperature”. It passes this through a function node which turns that into a SQL INSERT. Here’s the code in the “function” block…(with luck this will save some of you a lot of messing about)

var newMsg = { payload: msg.payload };
newMsg.topic="insert into readings (location,value) values (\"Kitchen\","+newMsg.payload+")";
return newMsg;

And the final bit the MQTT node – it is simply expecting the query in the incoming topic – it simply connects to the local mysql database and runs that query.

A few values added using mqtt-spy….

mqtt spy

Note that last one at 99… and the updated graph shows this! This is real and working, not a dummy.


I’m storing a title and a value in the database – so I can extract the latest values for each of 3 different inputs along with an automatic timestamp. I’ve not quite figured out how to ensure they all match up time-wise yet… but it’s a start….