emq与mysql_EMQ X 插件持久化系列 （五）MySQL MQTT 数据存储

本文以在 CentOS 7.2 中的实际例子来说明如何通过 MySQL 来存储相关的 MQTT 数据。

MySQL 属于传统的关系型数据库产品，其开放式的架构使得用户的选择性很强，而且随着技术的逐渐成熟，MySQL 支持的功能也越来越多，性能也在不断地提高，对平台的支持也在增多，此外，社区的开发与维护人数也很多。当下，MySQL 因为其功能稳定、性能卓越，且在遵守 GPL 协议的前提下，可以免费使用与修改，因此深受用户喜爱。

安装与验证 MySQL 服务器

读者可以参考 MySQL 官方文档 或使用 Docker 来下载安装 MySQL 服务器，本文章使用 MySQL 5.6 版本。

为方便管理操作，可下载使用官方免费图形化管理软件 MySQL Workbeanch。

如果读者使用的是 MySQL 8.0 及以上版本，MySQL 需按照EMQ X 无法连接 MySQL 8.0 教程特殊配置。

准备

初始化数据表

插件运行依赖以下几张数据表，数据表需要用户自行创建，表结构不可改动。

mqtt_client 存储设备在线状态

DROP TABLE IF EXISTS `mqtt_client`;

CREATE TABLE `mqtt_client` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`clientid` varchar(64) DEFAULT NULL,

`state` varchar(3) DEFAULT NULL, -- 在线状态 0 离线 1 在线

`node` varchar(100) DEFAULT NULL, -- 所属节点

`online_at` datetime DEFAULT NULL, -- 上线时间

`offline_at` datetime DEFAULT NULL, -- 下线时间

`created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (`id`),

KEY `mqtt_client_idx` (`clientid`),

UNIQUE KEY `mqtt_client_key` (`clientid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mqtt_sub 存储设备的主题订阅关系

DROP TABLE IF EXISTS `mqtt_sub`;

CREATE TABLE `mqtt_sub` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`clientid` varchar(64) DEFAULT NULL,

`topic` varchar(255) DEFAULT NULL,

`qos` int(3) DEFAULT NULL,

`created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (`id`),

KEY `mqtt_sub_idx` (`clientid`,`topic`(255),`qos`),

UNIQUE KEY `mqtt_sub_key` (`clientid`,`topic`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mqtt_msg 存储 MQTT 消息

DROP TABLE IF EXISTS `mqtt_msg`;

CREATE TABLE `mqtt_msg` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`msgid` varchar(100) DEFAULT NULL,

`topic` varchar(1024) NOT NULL,

`sender` varchar(1024) DEFAULT NULL,

`node` varchar(60) DEFAULT NULL,

`qos` int(11) NOT NULL DEFAULT '0',

`retain` tinyint(2) DEFAULT NULL,

`payload` blob,

`arrived` datetime NOT N