Skip to content

Instantly share code, notes, and snippets.

@smadds

smadds/README.md Secret

Last active Jan 22, 2021
Embed
What would you like to do?
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 **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"]]}]
@cylew6

This comment has been minimized.

Copy link

@cylew6 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.
Thanks.

@rdc-Green

This comment has been minimized.

Copy link

@rdc-Green rdc-Green commented Jan 22, 2021

Thank you for providing this example. Is there any way I can test the connection? I keep seeing "Error: Pool is closed" in the debug. The pi isn't connected to a sensor so not sure if its trying to do anything.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment