Learn, How to save MQTT messages into a MySQL Database

This didn’t sound right to me and a lot of questions were arising in my mind,

  • There are so many open source and commercial brokers available, doesn’t any of them allow saving data to a database?
  • So many IOT platforms including AWS, Google & IBM support MQTT, How are they able to store the incoming MQTT messages?

The quest for a free MQTT broker which allows to save messages to a database :

This lead me into researching more into the open source MQTT brokers like Mosquitto, But even the most popular Mosquitto broker was also not supporting any customization to save data to a database, which is the most essential next step of concentrating the received data from IOT devices.

There i came across this awesome Open Source MQTT Broker called EMQ (Erlang MQTT Broker) defined as below on their site,

EMQ (Erlang MQTT Broker) is a distributed, massively scalable, highly extensible MQTT message broker written in Erlang/OTP. EMQ is fully open source and licensed under the Apache Version 2.0. EMQ implements both MQTT V3.1 and V3.1.1 protocol specifications, and supports MQTT-SN, CoAP, WebSocket, STOMP and SockJS at the same time. 

 

This broker is open source, and the code is hosted on GitHub. They also have enterprise version available (Everyone needs money to survive 🙂 )


First thing what i caught my attention is the word ERLANG, 

What is ERLANG ?

ERLANG is a programming language used for creating concurrent applications which need to be scaled massively and need high availaiblility. The banking software’s and ecommerce software’s are written in this.

For me, its weird and cryptic and doesn’t look similar to any programming language i have known. 


How is EMQ different?

But good thing about this EMQ broker is that it allows to write PLUGINS, These plugins can be used to tap the incoming MQTT messages as well as outgoing messages using the HOOKS. Hooks are some functions which are called when certain event occurs like,

Hook Description
client.connected Run when client connected to the broker successfully
client.subscribe Run before client subscribes topics
client.unsubscribe Run when client unsubscribes topics
session.subscribed Run After client(session) subscribed a topic
session.unsubscribed Run After client(session) unsubscribed a topic
message.publish Run when a MQTT message is published
message.delivered Run when a MQTT message is delivered
message.acked Run when a MQTT message is acked
client.disconnected Run when client disconnected from broker

 

These are the possible hooks we can use and our code gets called whenever these events occur, For example to save the incoming message to a MySQL database what i did is, I used the REST API endpoint created by using Dreamfactory (Open source REST backend) and posted the incoming MQTT messages to this endpoint as shown below, 
 

  on_message_publish(Message, _Env) -> io:format("publish ~s~n", [emqttd_message:format(Message)]),  MessageBin = element(12, Message), MessageStr = binary_to_list(MessageBin), inets:start(), Method = post, URL = "http://127.0.0.1/api/v2/mysql/_table/log", Header = [{"X-DreamFactory-Api-Key", "7d1a4baac442ed875b1545af24f9f4312a1cfdb3fad0db096db91cf2869d17f2"}], Type = "application/json", Body = MessageStr, HTTPOptions = [], Options = [], R = httpc:request(Method, {URL, Header, Type, Body}, HTTPOptions, Options), {ok, Message}.  

As you can see above the code is written in ERLANG and i have tapped the hook called on_message_publish() which gets called whenever a device/client publishes a message to the broker, So, I am posting all the  messages which are coming into the broker to the database using HTTP REST API. 
Here in this example, I am posting a JSON string which has values of latitude, longitude, time, Device ID

{“resource”:[{“lat”:”344″,”lon”:”123″,”time”:”2018-02-19 11:00:18″,”devid”:”Hello”}]}


Data saved on MySQL database fetched and viewed using Dreamfactory REST API : 

This whole string comes in a MQTT message and gets posted to the database as shown below, 

So, In this way i was able to bridge the gap between publishing messages to my MQTT broker to saving the messages to a MySQL database. I chose to do a REST API based post to database because ERLANG is very new to me and i don’t know how to directly post messages to MySQL database. I got help from good people at StackOverflow especially 7Stud and AlexL to implement this. Here is the link on the discussion.

Below is a demo video on how to install these two Open Source Softwares, EMQ and Dreamfactory side by side on a AWS server and link them to save MQTT messages to a database

Demo Video : 

 

 

2 thoughts on “Learn, How to save MQTT messages into a MySQL Database”

  1. Hello Ravi,
    Thank you for posting this MQTT to MySQL instructional video. I have learned a lot putting this together.
    There are a few problems though. Can you allow me to ask for assistance? If you have a policy not to assist, I understand completely. Just let me know…
    Here is the status of the setup I have in place.
    AWS ubuntu 18.04 t2.micro 30GB instance. Erlang version Erlang/OTP 22, PHP7.2
    The EMQ installation seemed to have some conflicts. So I have a newer version.
    sudo emqx start… returns… EMQ X Broker v4.0.1 is started successfully!
    There is a warning from sudo apt-get update…
    W: Conflicting distribution: https://repos.emqx.io/emqx-ce/deb/ubuntu bionic InRelease (expected bionic but got )

    Dreamfactory, nginx and mySQL install and setup went fine, exactly as it was shown in your video.
    The opening of the dreamfactory page is showing up with the login box, but does not allow login. (double checked user and password several times.)
    So my questions are;
    Could there be a version conflict between the different software packages?
    Even with all the newer version software, is there any reason should this setup would not work?
    Do you think deprecated features have designed out a free MQTT to mysql design?
    Any suggestions?
    Thank you, Christian

    1. Dreamfactory has made MySQL interface as paid subscription. you might need to use some other database which comes in free service. Also if you can switch to earlier version of dreamfactory available somewhere that could also work. But they took down old versions from github.

Leave a Comment

Your email address will not be published. Required fields are marked *