Log MQTT to MySQL

Logs all or selected MQTT activity to a MySQL database.

By default logs everything - change the topic in the MQTT node to be more selective (e.g. "my\path#" or "my\topic")

MySQL table needs the following columns:

  • id (integer, autoincrement) (optional)
  • timestamp (date-time)
  • topic (string 200)
  • data (string 200)

Requires node-red-node-mysql and configuration of your MQTT and MySQL server details

[{"id":"a06d74db.919808","type":"mqtt in","z":"b194ff77.b24e2","name":"","topic":"#","qos":"2","broker":"","x":210,"y":460,"wires":[["98201759.e4d558"]]},{"id":"6825dd23.d4ee44","type":"debug","z":"b194ff77.b24e2","name":"","active":true,"console":"false","complete":"true","x":610,"y":420,"wires":[]},{"id":"98201759.e4d558","type":"function","z":"b194ff77.b24e2","name":"Create query in topic","func":"var out = \"INSERT INTO tcl_mqtt_log (timestamp,topic,data)\"\nout = out + \"VALUES ('\" + new Date().toISOString() + \"','\" \nout = out + msg.topic + \"','\" + msg.payload + \"');\"\n    \nmsg.topic=out;\n\nreturn msg;","outputs":1,"noerr":0,"x":400,"y":460,"wires":[["6825dd23.d4ee44","ddb3e588.199508"]]},{"id":"be41a506.f7f9f8","type":"comment","z":"b194ff77.b24e2","name":"Log everything","info":"","x":200,"y":420,"wires":[]},{"id":"4265dd53.5b18a4","type":"debug","z":"b194ff77.b24e2","name":"","active":true,"console":"false","complete":"false","x":750,"y":460,"wires":[]},{"id":"ddb3e588.199508","type":"mysql","z":"b194ff77.b24e2","name":"","x":610,"y":460,"wires":[["4265dd53.5b18a4"]]}]
smadds

Flow Info

created 3 months, 1 week ago

Node Types

Core
  • comment (x1)
  • debug (x2)
  • function (x1)
  • mqtt in (x1)
Other

Tags

  • MQTT
  • MySQL
  • Logging
Copy this flow JSON to your clipboard and then import into Node-RED using the Import From > Clipboard (Ctrl-I) menu option