Skip to content

Instantly share code, notes, and snippets.

@smadds smadds/ Secret
Last active Sep 25, 2017

What would you like to do?

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 **your_table_name_here** (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"]]}]

This comment has been minimized.

Copy link

cylew6 commented Sep 25, 2017

I have an issue, while uploading to mysql.
The timezone showed in the UTC 0,and I need to change it to UTC +8.
I have checked my system time zone which showed UTC +8.
Need some expert advice to resolve the issues.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.