mqtt服务器 mysql,如何将MQTT Mosquitto发布事件存储到MySQL中？

I've connected a device that communicates to my mosquitto MQTT server (RPi) and is sending out publications to a specified topic. What I want to do now is to store the messages published on that topic on the MQTT server into a MySQL database. I know how MySQL works, but I don't know how to listen for these incoming publications. I'm looking for a light-weight solution that runs in the background. Any pointers or ideas on libraries to use are very welcome.

解决方案

I've done something similar in the last days:

live-collecting weatherstation-data with pywws

publishing with pywws.service.mqtt to mqtt-Broker

python-script on NAS collecting the data and writing to MariaDB

#!/usr/bin/python -u

import mysql.connector as mariadb

import paho.mqtt.client as mqtt

import ssl

mariadb_connection = mariadb.connect(user='USER', password='PW', database='MYDB')

cursor = mariadb_connection.cursor()

# MQTT Settings

MQTT_Broker = "192.XXX.XXX.XXX"

MQTT_Port = 8883

Keep_Alive_Interval = 60

MQTT_Topic = "/weather/pywws/#"

# Subscribe

def on_connect(client, userdata, flags, rc):

mqttc.subscribe(MQTT_Topic, 0)

def on_message(mosq, obj, msg):

# Prepare Data, separate columns and values

msg_clear = msg.payload.translate(None, '{}""').split(", ")

msg_dict = {}

for i in range(0, len(msg_clear)):

msg_dict[msg_clear[i].split(": ")[0]] = msg_clear[i].split(": ")[1]

# Prepare dynamic sql-statement

placeholders = ', '.join(['%s'] * len(msg_dict))

columns = ', '.join(msg_dict.keys())

sql = "INSERT INTO pws ( %s ) VALUES ( %s )" % (columns, placeholders)

# Save Data into DB Table

try:

cursor.execute(sql, msg_dict.values())

except mariadb.Error as error:

print("Error: {}".format(error))

mariadb_connection.commit()

def on_subscribe(mosq, obj, mid, granted_qos):

pass

mqttc = mqtt.Client()

# Assign event callbacks

mqttc.on_message = on_message

mqttc.on_connect = on_connect

mqttc.on_subscribe = on_subscribe

# Connect

mqttc.tls_set(ca_certs="ca.crt", tls_version=ssl.PROTOCOL_TLSv1_2)

mqttc.connect(MQTT_Broker, int(MQTT_Port), int(Keep_Alive_Interval))

# Continue the network loop & close db-connection

mqttc.loop_forever()

mariadb_connection.close()