二、Ubuntu22.0安装EMQX5.0——采集MQTT设备端数据，并存入数据库mysql

原创已于 2025-04-03 09:59:03 修改 · 4.3k 阅读

53 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #物联网 #mysql #python

于 2023-11-26 23:14:23 首次发布

EMQX5.0 SSL MQTT 专栏收录该内容

3 篇文章

订阅专栏

本文详细描述了如何在Ubuntu服务器上搭建MQTT客户端，采集数据并通过Python脚本存入MySQL数据库，包括安装MySQL、创建数据库和表、编写数据采集脚本、设置自启动以及处理常见问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

说明：本文并没有什么创新的内容。只是作为小白，查阅了几十篇文章，没有看到一篇简单、详细、实用，适合菜鸟去开发这个东西的文章。同为天涯菜鸟，走了很多弯路，看了很多资料，依然很懵逼，经过不断测试，在此仅记录下实现过程，以便其他菜鸟学习参考，如有侵权，敬请谅解，如有必要，请联系本人删除。

上一篇讲了如何搭建平台，测试连接服务器，本讲就来说一下，如何采集数据，并且存入数据库。

整个过程分为如下几步：

一、建立Mysql数据库，建立需要的数据表；

二、在Ubuntu22.0服务器上模拟出一个MQTT客户端（上一章中，安装在系统中的是MQTT服务器），然后通过客户端订阅我们采集端发送的主题，然后解析主题，存入相应的数据表中；整个过程，我们采用的是python脚本来执行，当然您也可以采用其他方式，请自行测试。

三、将2中脚本设置成系统后台启动，保证每次开机可以自动启动；

下面具体来说。

一、建立Mysql数据库，建立需要的数据表

1. 安装数据库mysql服务器

sudo apt update
sudo apt install mysql-server

安装客户端

sudo apt install mysql-client

安装完毕，数据库自动运行，并且自己设置开机自启。查看Mysql运行情况：

systemctl status mysql.service

测试正常后，CTRL+Z返回。

接着运行MySQL初始化安全脚本

sudo mysql_secure_installation

mysql_secure_installation脚本设置的东西：更改root密码、移除MySQL的匿名用户、删除test数据库和重新加载权限。除了询问是否要更改root密码、禁止root远程登录，看情况是否需要更改，其余的问题都可以按Y，然后ENTER接受所有后续问题的默认值。使用上面的这些选项可以提高MySQL的安全。

安装完成后，登录数数据库：

mysql -u root -p

输入指令，要求输入密码，初次登录直接按enter键，然后初始化登录密码：（如果密码已设，请忽略此条）

alter user 'root'@'localhost' identified by '这里填你要的密码';

以下是数据库软件的常用设置，安装过程请忽略：

启动MySql数据库服务

sudo systemctl start mysql.service

停止MySQL数据库服务

sudo systemctl stop mysql.service

重启MySQL数据库服务

sudo systemctl restart mysql.service

设置MySql服务开机自启动

sudo systemctl enable mysql.service

停止MySql服务开机自启动：

sudo systemctl disable mysql.service

2. 创建数据库和数据表

首先要登录：

mysql -u root -p

输入密码，然后等待进入数据库，创建数据表（所谓数据表，就像一个excel表格一样），结构如下：（注：下图是phpMyAdmin软件，是专门管理数据库用的）

数据库创建：

CREATE DATABASE mqtt_data;

然后创建数据表，首先需要使用上述数据库：

USE mqtt_data;

再创建数据表，代码如下，其中fluence为表的名字：

CREATE TABLE fluence (
	id INT AUTO_INCREMENT PRIMARY KEY,
	InCOD float NOT NULL,
	InNH3N float NOT NULL,
	InTN float NOT NULL,
	InTP float NOT NULL,
	OutCOD float NOT NULL,
	OutNH3N float NOT NULL,
	OutTN float NOT NULL,
	OutTP float NOT NULL,
	Date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

我的MQTT设备客户端发来的主题格式如下：

{"进水TP":84.647797,"t":1700527982229,"进水TN":8.527695,"t":1700527982229,"进水NH3-N":77.915855,"t":1700527982229,"进水COD":214.986465,"t":1700527982229,"出水TP":9.923146,"t":1700527982229,"出水TN":0.193696,"t":1700527982229,"出水NH3-N":0.379000,"t":1700527982229,"出水COD":11.200000,"t":1700527982229}

因此，我做了如上的数据表。

大概解释一下这个结构：

好的，到这里，数据库，数据表创建结束。

二、采集数据，入库

1. 具体执行的代码如下，为python脚本语言：

# -*- coding: utf-8 -*-

#!/usr/bin/python
# -*- coding: utf-8 -*

import paho.mqtt.client as mqtt
import json
import pymysql
import time

def gettime():
    timevalue=time.strftime("%Y-%m-%d %H:%M:%S",time.localtime())
    return timevalue

# 服务器地址，请根据实际填写，也可以填写127.0.0.1
host = '127.0.0.1'
port = 1883
# mqtt客户端的用户名和密码，随便设置
username = 'xxxxxxxxx'
password = 'xxxxxxxxx'

# 订阅主题名，请自行更改
topic = 'dcm/QHH-JC'

# 连接后事件
def on_connect(client, userdata, flags, respons_code):
    if respons_code == 0:
        # 连接成功
        print('Connection Succeed!')
    else:
        # 连接失败并显示错误代码
        print('Connect Error status {0}'.format(respons_code))
    # 订阅信息
    client.subscribe(topic)


def sqlsave(jsondata):
      # 打开数据库连接，数据库的信息，请自行修改
    db = pymysql.connect(host="localhost",user="vinter",password="Fluence@123",database="mqtt_data",charset='utf8')
  # 使用cursor()方法获取操作游标 
    cursor = db.cursor()
    # SQL 插入语句
    try:
        sql = "INSERT INTO fluence (InCOD,InNH3N,InTN,InTP,OutCOD,OutNH3N,OutTN,OutTP,Date) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s');"%(jsondata['进水TP'],jsondata['进水TN'],jsondata['进水NH3-N'],jsondata['进水COD'],jsondata['出水TP'],jsondata['出水TN'],jsondata['出水NH3-N'],jsondata['出水COD'],gettime())
        cursor.execute(sql)
        db.commit()
        print("数据库保存成功！")
    except:
        print("数据库储存失败！")
        pass
    # 关闭数据库连接
    db.close()
    
# 接收到数据后事件
def on_message(client, userdata, msg):
    global dddd
    # 打印订阅消息主题
    # print("topic", msg.topic)
    # 打印消息数据
    jsondata=json.loads(msg.payload)
    print("msg payload",jsondata)
    sqlsave(jsondata)

def main():
    client = mqtt.Client()
    # 注册事件
    client.on_connect = on_connect
    client.on_message = on_message
    # 设置账号密码（如果需要的话）
    client.username_pw_set(username, password=password)
    # 连接到服务器
    client.connect(host, port=port, keepalive=60)
    # 守护连接状态
    client.loop_forever()

#MySQL保存



if __name__ == '__main__':
    main()

2. 在ubuntu中开启MQTT客户端

首先需要安装paho库，paho库用于完成mqtt通讯。在安装paho之前，需要安装python管理工具pip，安装如下：

在 Ubuntu 22.04 上为 Python 3 安装 pip，以 root 或者其他 sudo 用户身份在终端运行下面的命令：

sudo apt update
sudo apt install python3-pip

上面的命令将会安装用来构建 Python 模块所需要的所有依赖软件包。

当安装结束，验证安装过程，检查 pip 版本：

pip3 --version

安装完，接着安装paho客户端，如果安装报错，请多安装几次，或者百度找答案。

pip install paho-mqtt

如上，安装完成。

接着安装pymysql库，pymysql库用于完成mysql操作，安装不成功多安装几次，或百度找答案。

pip install pymysql

如上，完成安装。

3.创建脚本，在opt/install文件夹下创建mqtt_s.py文件，如果没有此文件夹，请创建，指令如下：

mkdir -p /opt/install

cd /opt/install
touch mqtt_s.py

打开mqtt_s.py,

vi mqtt_s.py

将1中的脚本中输入mqtt_s.py，也可以将mqtt_s.py通过其他工具导入到opt/install目录下，比如MobaXterm的ssh连接。请注意上述文本中的xxxx,需要根据您的实际情况设置。

测试执行mqtt_s.py文件：

python3 mqtt_s.py

测试如下：

若在接收数据后，出现如下错误：

这个问题是打开数据库Mysql的用户名权限不够，为此，解决步骤如下：

步骤：
找到 bind-address 修改值为 0.0.0.0，然后保存退出

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

新建一个用户：

sudo mysql -u root -p

输入密码登录，

use mysql;

创建用户：

CREATE USER '新的用户名'@'%' IDENTIFIED WITH mysql_native_password BY '你的密码';

密码策略限制必须要大小写加数字特殊符号！

给予权限：

GRANT ALL PRIVILEGES ON *.* TO '新建的用户'@'%' WITH GRANT OPTION;

退出：

quit;

将新建的用户名和密码填入mqtt_s.py文件，保存。再次运行测试执行mqtt_s.py文件：

cd /opt/install
python3 mqtt_s.py

运行正常：

三、开启自启

原理是将python脚本定义为一个系统启动项。

1.首先在opt/install新建一个svc-test.sh文件，具体如下：

执行指令：

cd /opt/install
touch svc-test.sh

打开svc-test.sh文件：

vi svc-test.sh

输入以下内容:

#!/bin/bash
P_HOME=$(cd "$(dirname "$0")";pwd)
echo "P_HOME=$P_HOME"
export PYTHONPATH=/usr/bin/python3
# export PATH=$PATH:
echo "PYTHONPATH=$PYTHONPATH"
cd $P_HOME
cd /opt/install
python3 mqtt_s.py

cd ../

输入完，按shift+z+z保存。

2.再在 /etc/systemd/system下新建svc-test.service文件，

执行指令：

cd /etc/systemd/system
touch svc-test.service

打开，

vi svc-test.service

输入以下内容：

[Unit]
Description=svc-test
After=network.target
StartLimitIntervalSec=0
[Service]
Type=simple
Restart=always
RestartSec=1
User=root
ExecStart=/usr/bin/bash /opt/install/svc-test.sh

[Install]
WantedBy=multi-user.target

输入完，按shift+z+z保存。

3.接下来，给这三个文件添加执行权限，提升这三个文件的权限：

cd 
sudo chmod 744 /opt/install/mqtt_s.py
sudo chmod 744 /opt/install/svc-test.sh

sudo chmod 664 /etc/systemd/system/svc-test.service

然后，重载系统服务

sudo systemctl daemon-reload

将服务注册为开机启动

sudo systemctl enable svc-test.service

重启系统，reboot

等待开机，检查运行情况：

sudo systemctl status svc-test

运行正常，可以通过如下指令查询python程序运行的情况：

ps -ef | grep python

4.以下为停止服务指令：

systemctl stop svc-test

以下为手动启动服务指令：

systemctl start svc-test

5.最后，我们来查询数据记录情况：

登录mysql，执行按日期检索：

到此，数据采集，存储，系统自启结束。下一章，从数据库取数据，并展示在网页上。