Thursday 26 January 2017

MQTT MySQL gateway

Previously I've made a couple of sensors that send data with MQTT. Of course that data should be stored in a database. Considering that I'm probably going to make more wireless sensors that send data for example from our heating system it's best to write a easily configurable MQTT to database gateway. This gateway should also be able to send new values from database to MQTT nodes for displaying, as some data is gathered in other ways and inserted directly to db, for example OWFS(1-wire) or homegrown pulse counters.

So the basic functionality would be:
  • Recieve data from MQTT, process and save it to MySQL
  • Follow MySQL binlog and send saved data to MQTT
For input I need to have a table that stores:
  • Subscribed topic.
  • How to get raw sensorid from topic, maybe regexp.
  • How to parse data from message.
  • Small script that processes the data so it's ready to store.
And a second table that's used to convert raw sensorid's to db sensorid's:
  • Rawid
  • Sensorid
For output I need a table that stores:
  • Sensorid
  • Topic to push data to
  • How to process data before sending
There are still some features to implement, like outputting data from db to MQTT, need to think how this should be done with some kind of plugin system, MySql will probably use binlog to read data, but other databases have other mechanisms.

There are depencies for Paho-MQTT clientpep3143daemon and SQLAlchemy. More detailed dependencies in requirements.txt

Code in GitHub https://github.com/mika-koivusaari/mqtt_db_gateway.

No comments:

Post a Comment