linux 部署mosquitto外部连接到MySQL数据库记录mqtt消息

1.安装python3.7.x

2.pip3 install mysql-server python3-mysqldb

pip3 install 包名 -i https://mirrors.aliyun.com/pypi/simple/
用阿里镜像

3.pip3 install paho-mqtt -i https://mirrors.aliyun.com/pypi/simple/

4.修改配置文件

vim mosquitto.conf

#配置信息
persistence true
persistence_location /var/lib/mosquitto/
log_type all
log_dest file /var/log/mosquitto/mosquitto.log
include_dir /etc/mosquitto/conf.d

没有的文件夹创建一下。没有的文件创建一下

5.编写python脚本

vim mosquitto_mysql.py

import paho.mqtt.client as mqtt
import mysql.connector
from mysql.connector import Error

# MySQL数据库连接信息
db_config = {
    "host": "ip",
    "user": "root",
    "password": "密码",
    "database": "数据库"
}

# Mosquitto回调函数
def on_connect(client, userdata, flags, rc):
    print("Connected with result code " + str(rc))
    client.subscribe("#")  # 订阅所有主题

def on_message(client, userdata, msg):
    try:
        # 连接到MySQL数据库
        connection = mysql.connector.connect(**db_config)
        if connection.is_connected():
            cursor = connection.cursor()
            # 插入消息到数据库
            query = "INSERT INTO mqtt_messages (topic, payload) VALUES (%s, %s)"
            values = (msg.topic, msg.payload.decode("utf-8"))
            cursor.execute(query, values)
            connection.commit()
            print("Message saved to database: ", msg.topic, msg.payload)
    except Error as e:
        print("Error while connecting to MySQL", e)
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

# 创建Mosquitto客户端实例
client = mqtt.Client(client_id="主机id")
client.on_connect = on_connect
client.on_message = on_message

# 连接到Mosquitto代理（请替换为您的代理地址和端口）
client.username_pw_set("用户名", "密码")
client.connect("ip", 1883, 60)

# 开始监听消息
client.loop_forever()

6.python3 mosquitto_mysql.py
运行报错，缺少的依赖自己装一下