陈拓 2021/04/17-2021/04/19
1. 概述
在《用C语言实现mosquitto MQTT订阅消息》
https://zhuanlan.zhihu.com/p/365190438
https://blog.csdn.net/chentuo2000/article/details/115747492
和《用C语言实现mosquitto MQTT订阅消息(异步)》
https://zhuanlan.zhihu.com/p/365483724
https://blog.csdn.net/chentuo2000/article/details/115786111
两篇文章中我们已经通过MQTT订阅收到了客户端的消息,本文介绍将其中的数据存储到MySQL数据库中。
在《MQTT服务器Mosquitto 2.x编译安装配置》
https://zhuanlan.zhihu.com/p/365103802
https://blog.csdn.net/chentuo2000/article/details/115731687
一文中我们下载了mosquitto 2.x源码,其中有个写MySQL数据库的例子:
在GitHub上mosquitto官方也有这个例子:
https://github.com/eclipse/mosquitto/blob/master/examples/mysql_log/mysql_log.c
2. 查看mysql_log.c代码
cat mosquitto-2.0.9/examples/mysql_log/mysql_log.c
见“附录:mysql_log.c源代码”。
3. 安装MySQL数据库
见《树莓派安装使用数据库MariaDB (MySQL)》
https://blog.csdn.net/chentuo2000/article/details/108702880
4. 创建数据表
- 查看MySQL进程
ps -ef | grep mysql
MySQL已启动。
- 创建MySQL用户
在《树莓派安装使用数据库MariaDB (MySQL)》
https://blog.csdn.net/chentuo2000/article/details/108702880
一文中我们创建了用户ct。
- 以用户ct登录MySQL
mysql -uct -pct
- 查看数据库
show databases;
在《树莓派安装使用数据库MariaDB (MySQL)》
https://blog.csdn.net/chentuo2000/article/details/108702880
一文中我们创建了数据库smarthome。
- 打开数据库
use smarthome;
- 查看数据库smarthome中的表
show tables;
- 查看表结构
describe temperature;
5. 修改C程序
- 再开一个终端窗口
- 进入mysql_log目录
cd mosquitto-2.0.9/examples/mysql_log/
- 修改C程序mysql_log.c
有关C语言对MySQL的操作请看《树莓派采集温度数据并存入数据库(C语言版)》
https://blog.csdn.net/chentuo2000/article/details/108779497
一文。
针对我的环境进行修改:
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <unistd.h>
#include <signal.h>
#include <mosquitto.h>
#include <mysql.h>
#define db_host "localhost"
#define db_username "ct"
#define db_password "ct"
#define db_database "smarthome"
#define db_port 3306
#define db_query "INSERT INTO temperature (deviceid, celsius_temp) VALUES (?,?)"
#define mqtt_host "localhost"
#define mqtt_port 1883
static int run = 1;
static MYSQL_STMT *stmt = NULL;
void handle_signal(int s)
{
printf("\n Capture sign no:%d\n", s);
run = 0;
}
void connect_callback(struct mosquitto *mosq, void *obj, int reason_code)
{
}
void message_callback(struct mosquitto *mosq, void *obj, const struct mosquitto_message *message)
{
printf("%s %d %s\n", message->topic, message->qos, (char *)message->payload);
MYSQL_BIND bind[2];
memset(bind, 0, sizeof(bind));
bind[0].buffer_type = MYSQL_TYPE_STRING;
bind[0].buffer = message->topic;
bind[0].buffer_length = strlen(message->topic);
// Note: payload is normally a binary blob and could contains
// NULL byte. This sample does not handle it and assume payload is a
// string.
bind[1].buffer_type = MYSQL_TYPE_STRING;
bind[1].buffer = message->payload;
bind[1].buffer_length = message->payloadlen;
mysql_stmt_bind_param(stmt, bind);
mysql_stmt_execute(stmt);
}
int main(int argc, char *argv[])
{
MYSQL *connection;
my_bool reconnect = true;
char clientid[24];
struct mosquitto *mosq;
int rc = 0;
signal(SIGINT, handle_signal); // 捕捉终端CTRL+c产生的SIGINT信号
signal(SIGTERM, handle_signal); // 程序结束(terminate)信号
mysql_library_init(0, NULL, NULL);
mosquitto_lib_init();
connection = mysql_init(NULL);
if(connection){
mysql_options(connection, MYSQL_OPT_RECONNECT, &reconnect);
connection = mysql_real_connect(connection, db_host, db_username, db_password, db_database, db_port, NULL, 0);
if(connection){
stmt = mysql_stmt_init(connection);
mysql_stmt_prepare(stmt, db_query, strlen(db_query));
memset(clientid, 0, 24);
snprintf(clientid, 23, "mysql_log_%d", getpid());
mosq = mosquitto_new(clientid, true, connection);
if(mosq){
mosquitto_connect_callback_set(mosq, connect_callback);
mosquitto_message_callback_set(mosq, message_callback);
mosquitto_username_pw_set(mosq, "ct", "1qaz2wsx");
rc = mosquitto_connect(mosq, mqtt_host, mqtt_port, 60);
mosquitto_subscribe(mosq, NULL, "#", 0);
while(run){
rc = mosquitto_loop(mosq, -1, 1);
if(run && rc){
sleep(20);
//mosquitto_reconnect(mosq);
}
}
mosquitto_reconnect(mosq);
}
mysql_stmt_close(stmt);
mysql_close(connection);
}else{
fprintf(stderr, "Error: Unable to connect to database.\n");
printf("%s\n", mysql_error(connection));
rc = 1;
}
}else{
fprintf(stderr, "Error: Unable to start mysql.\n");
rc = 1;
}
mysql_library_end();
mosquitto_lib_cleanup();
return rc;
}
说明:
(1) 通配符#的使用
mosquitto_subscribe(mosq, NULL, "#", 0);
在订阅中使用通配符可以收到多个主题的消息,通配符的详细用法可以看MQTT文档。
(2) CTRL+c正常结束程序
signal(SIGINT, handle_signal); // 捕捉终端CTRL+c产生的SIGINT信号
通过捕捉终端操作CTRL+c使程序能够正常退出以释放资源,而不是用CTRL+z强行终止程序的运行。
(3) 和《用C语言实现mosquitto MQTT订阅消息》
https://zhuanlan.zhihu.com/p/365190438
https://blog.csdn.net/chentuo2000/article/details/115747492
一文中调用无限循环的方法mosquitto_loop_forever(mosq, -1, 1);不同,这里采用等价的方法:
while(run){
rc = mosquitto_loop(mosq, -1, 1);
if(run && rc){
sleep(20);
//mosquitto_reconnect(mosq);
}
}
这种方法更具灵活性。
- 编辑mysql_log.c
nano mysql_log.c
6. 修改Makefile文件
- 例子中的Makefile文件
cat Makefile
- 修改为
CFLAGS=-Wall -ggdb
LDFLAGS=../../lib/libmosquitto.so.1 -lmariadbclient -lsqlite3
.PHONY: all clean
all : mosquitto_mysql_log
mosquitto_mysql_log : mysql_log.o
${CC} $^ -o $@ ${LDFLAGS}
mysql_log.o : mysql_log.c
${CC} -c $^ -o $@ ${CFLAGS} -I../../include -I/usr/include/mariadb
clean :
-rm -f *.o mosquitto_mysql_log
根据我的MySQL和mosquitto的头文件和库文件的位置修改。
- 头文件和库文件的位置
(1) MySQL头文件
ls -l /usr/include/mariadb/mysql.h
(2) MySQL库文件
ls -l /usr/lib/arm-linux-gnueabihf/libmariadbclient.so
ls -l /usr/lib/arm-linux-gnueabihf/libsqlite3.so
libmariadbclient.so和libsqlite3.so在默认库文件的目录/usr/lib中,不需要指出路径。
(3) mosquitto头文件
ls -l ../../include/mosquitto.h
(4) mosquitto库文件
ls -l ../../lib/libmosquitto.so.1
7. 编译
make
8. 本地测试
- 运行./mosquitto_mysql_log
订阅subscribe程序进入循环等待接收消息。
- 在另一个终端窗口中发布publish消息
订阅窗口收到消息,显示topic、qos、payload,并将topic和payload存入数据库,对用表temperature的deviceid和celsius_temp字段。
按CTRL+c可以正常推出程序,这样可以完成资源释放和回收工作。按CTRL+z可以强行中断程序运行,但跳过了资源释放语句的执行。
重新运行./mosquitto_mysql_log
- 再发布消息
订阅窗口收到消息:
可以看到,在mosquitto_subscribe(mosq, NULL, "#", 0);语句中使用了通配符后订阅程序可以接收不同主题的消息。在这里我们可以用主题temperature001和temperature002表示两个温度传感器。
- 查看数据库
在数据库窗口查看刚才收到并保存的数据:
select * from temperature where deviceid like 'temperature%';
9. 远程测试
- 用MQTT.fx测试
详细说明见《树莓派MQTT服务远程测试MQTT.fx》
https://zhuanlan.zhihu.com/p/363373024
https://blog.csdn.net/chentuo2000/article/details/115539377
点击Publish:
- 订阅测试窗口收到消息
- 查看数据库
附录:mysql_log.c源代码
#include <signal.h>
#include <stdio.h>
#include <string.h>
#ifndef WIN32
# include <unistd.h>
#else
# include <process.h>
# define snprintf sprintf_s
#endif
#include <mosquitto.h>
#include <mysql/mysql.h>
#define db_host "localhost"
#define db_username "mqtt_log"
#define db_password "password"
#define db_database "mqtt_log"
#define db_port 3306
#define db_query "INSERT INTO mqtt_log (topic, payload) VALUES (?,?)"
#define mqtt_host "localhost"
#define mqtt_port 1883
static int run = 1;
static MYSQL_STMT *stmt = NULL;
void handle_signal(int s)
{
run = 0;
}
void connect_callback(struct mosquitto *mosq, void *obj, int result)
{
}
void message_callback(struct mosquitto *mosq, void *obj, const struct mosquitto_message *message)
{
MYSQL_BIND bind[2];
memset(bind, 0, sizeof(bind));
bind[0].buffer_type = MYSQL_TYPE_STRING;
bind[0].buffer = message->topic;
bind[0].buffer_length = strlen(message->topic);
// Note: payload is normally a binary blob and could contains
// NULL byte. This sample does not handle it and assume payload is a
// string.
bind[1].buffer_type = MYSQL_TYPE_STRING;
bind[1].buffer = message->payload;
bind[1].buffer_length = message->payloadlen;
mysql_stmt_bind_param(stmt, bind);
mysql_stmt_execute(stmt);
}
int main(int argc, char *argv[])
{
MYSQL *connection;
my_bool reconnect = true;
char clientid[24];
struct mosquitto *mosq;
int rc = 0;
signal(SIGINT, handle_signal);
signal(SIGTERM, handle_signal);
mysql_library_init(0, NULL, NULL);
mosquitto_lib_init();
connection = mysql_init(NULL);
if(connection){
mysql_options(connection, MYSQL_OPT_RECONNECT, &reconnect);
connection = mysql_real_connect(connection, db_host, db_username, db_password, db_database, db_port, NULL, 0);
if(connection){
stmt = mysql_stmt_init(connection);
mysql_stmt_prepare(stmt, db_query, strlen(db_query));
memset(clientid, 0, 24);
snprintf(clientid, 23, "mysql_log_%d", getpid());
mosq = mosquitto_new(clientid, true, connection);
if(mosq){
mosquitto_connect_callback_set(mosq, connect_callback);
mosquitto_message_callback_set(mosq, message_callback);
rc = mosquitto_connect(mosq, mqtt_host, mqtt_port, 60);
mosquitto_subscribe(mosq, NULL, "#", 0);
while(run){
rc = mosquitto_loop(mosq, -1, 1);
if(run && rc){
sleep(20);
mosquitto_reconnect(mosq);
}
}
mosquitto_destroy(mosq);
}
mysql_stmt_close(stmt);
mysql_close(connection);
}else{
fprintf(stderr, "Error: Unable to connect to database.\n");
printf("%s\n", mysql_error(connection));
rc = 1;
}
}else{
fprintf(stderr, "Error: Unable to start mysql.\n");
rc = 1;
}
mysql_library_end();
mosquitto_lib_cleanup();
return rc;
}