Is there a way to store Mosquitto payload into an MySQL database for history purpose?

Question

I am using Mosquitto MQTT broker and I would like to store every published message into a MySQL database without creating a client and subscribing to # wildcard.

I found this https://github.com/jpmens/mosquitto-auth-plug but my english is not good enough to understand it thoroughly... I think it is only to store persistent message for the broker to resend them later. Am I right ?

Thanks

Solution

The mosquitto-auth-plugin uses the database to store username/password and ACL information, not message content.

Writing some code to store messages in a database from any MQTT broker is relatively simple.

There are several approaches:

1. You could write a small java/python/nodejs application to do it using one of the MQTT libraries for a given language.
2. You could use something like Node-RED to create a flow that will write messages to the database.
3. You could use the HiveMQ broker instead of Mosquitto which has a plugin mechanism that supports storing messages in a database.

Comments

[Phoste]

That's what I thought about mosquitto-auth-plugin... My english isn't so rusty after all. I already wrote an application in java using paho library but as the article on HiveMQ said it has a few problems such as duplicate messages with persistent message. I will try HiveMQ instead of Mosquitto i believe... Thanks a lot

[Rajat Saxena]

@hardillb you really seem to have a lot of knowledge about MQTT. I've read several answers related to the topic written by you.

[Drasko DRASKOVIC]

@hardillb What do you mean exactly by 1) - to make changes in Mosquitto and recompile? I.e. make new functionality and compile it into Mosquitto? Is there any example how this is done?

[hardillb]

No, to write a standalone application that connects to the broker and subscribes to #

[Tristan]

Node-RED has no database output, only "file" output, which will store all messages into one single file. What would be the other tools ? And is there a real advantage using a tool over just writing a few lines of custom code (I know HiveMQ disagree on its blog, but do u agree with them ?)

[hardillb]

Node-RED has a perfectly good mysql node, it's just not in the "core" set that ship pre installed. And dragging 2 nodes on to a canvas and adding some​ credentials is a lot easier than writing "a few lines of code" for a beginner

[Tristan]

Ok, thanks, I've checked it. But it looks like it's only for non-programming guys trying to do some really quick test, but not for real deployment "By it's very nature it allows SQL injection". In the contrary, HiveMQ which you are quoting seems to have more real arguments against custom coding in a client (hivemq.com/blog/mqtt-sql-database). Do you think it is to sell their product or is it founded argument ?