In this paper, the actual example in CentOS 7.2 is used to illustrate how to store the relevant MQTT data through MySQL

MySQL is a traditional relational database product. Its open architecture makes users highly selective. With the gradual maturity of technology, MySQL supports more and more functions, improves performance, and supports more platforms. In addition, the number of community developers and maintainers is also large. At present, MySQL is popular among users because of its stable function, excellent performance and free use and modification under the premise of complying with the GPL protocol.

Install and verify MySQL server

Readers can refer to MySQL Official documents Or use Docker To download and install MySQL server, this article uses MySQL version 5.6.

For the convenience of management and operation, the official free graphic management software can be downloaded and used MySQL Workbeanch.

Get ready

Initialize data table

The plug-in operation depends on the following data tables, which need to be created by the user, and the table structure cannot be changed.

Mqtt? Client storage device online status

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, -- Online status 0 offline 1 Online
  `node` varchar(100) DEFAULT NULL, -- Subordinate node
  `online_at` datetime DEFAULT NULL, -- On-line time
  `offline_at` datetime DEFAULT NULL, -- Offline time
  `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;

Topic subscription relationship of mqtt sub storage device

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 stores MQTT messages

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 NULL, -- Arrival or not( QoS > 0)
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Mqtt & Retain store Retain message

DROP TABLE IF EXISTS `mqtt_retain`;
CREATE TABLE `mqtt_retain` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `topic` varchar(200) DEFAULT NULL,
  `msgid` varchar(60) DEFAULT NULL,
  `sender` varchar(100) DEFAULT NULL,
  `node` varchar(100) DEFAULT NULL,
  `qos` int(2) DEFAULT NULL,
  `payload` blob,
  `arrived` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `mqtt_retain_key` (`topic`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Mqtt acked store client message acknowledgment

DROP TABLE IF EXISTS `mqtt_acked`;
CREATE TABLE `mqtt_acked` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `clientid` varchar(200) DEFAULT NULL,
  `topic` varchar(200) DEFAULT NULL,
  `mid` int(200) DEFAULT NULL,
  `created` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `mqtt_acked_key` (`clientid`,`topic`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Configure EMQ X server

For the EMQ X installed in RPM mode, the MySQL related configuration files are located in / etc / emqx / plugins / emqx ﹣ backend ﹣ mysql.conf. This paper only tests the function of MySQL persistence, and most configurations do not need to be changed. Fill in user name, password and database:

auth.mysql.server = 127.0.0.1:3306

auth.mysql.username = root

auth.mysql.password = 123456

auth.mysql.database = mqtt

Leave the rest of the configuration file unchanged, and then you need to start the plug-in. There are three ways to start plug-ins: command line, console and REST API. Readers can choose one of them.

Start from the command line

emqx_ctl plugins load emqx_backend_mysql

Launch from admin console

On the plug-in page of the EMQ X management console, locate the emqx ﹣ backend ﹣ MySQL plug-in and click start.

Start through REST API

Plug ins can be started using the PUT /api/v4/nodes/:node/plugins/:plugin_name/load API.

Client online status storage

When the client is online or offline, the plug-in will update the online status, online or offline time, and node client list to the MySQL database.

Configuration item

Open the configuration file and configure the Backend rule:

## hook: client.connected,client.disconnected
## action/function: on_client_connected,on_client_disconnected


## Client online and offline
backend.mysql.hook.client.connected.1 = {"action": {"function": "on_client_connected"}, "pool": "pool1"}

backend.mysql.hook.client.disconnected.1 = {"action": {"function": "on_client_disconnected"}, "pool": "pool1"}

Use example

The browser opens the http://127.0.0.1:18083 EMQ X management console, creates a new client connection in the tool - > websocket, specifies the client ID as the sub ﹣ client, and then clicks the connection. After the connection is successful, manually disconnect:

Click mqtt ﹣ client table in MySQL workbench to view, and a client up / down line record will be written / updated:

Client agent subscription

When the client goes online, the enclosure directly reads the preset to be subscribed list from the database, and the agent loads the subscription topic. In the scenario that the client needs to communicate (receive messages) through a predetermined topic, the application can set / change the proxy subscription list from the data level.

Configuration item

Open the configuration file and configure the Backend rule:

## hook: client.connected
## action/function: on_subscribe_lookup
backend.mysql.hook.client.connected.2    = {"action": {"function": "on_subscribe_lookup"}, "pool": "pool1"}

Use example

When a sub client device goes online, it needs to subscribe to two QoS 1 topics: sub client / upstream and sub client / downlink:

1. Initialize the insertion agent subscription topic information in the mqtt sub table:

insert into mqtt_sub(clientid, topic, qos) values("sub_client", "sub_client/upstream", 1);
insert into mqtt_sub(clientid, topic, qos) values("sub_client", "sub_client/downlink", 1);

1. On the WebSocket page of the EMQ X management console, create a new client connection with the client sub client and switch to the subscription page. It can be seen that the current client automatically subscribes to two QoS 1 topics: sub client / upstream and sub client / downlink:

1. Switch back to the WebSocket page of the management console, publish messages to the sub ﹣ client / downlink topic, and receive the published messages in the message subscription list.

Persistent publish message

Configuration item

Open the configuration file, configure the Backend rule, and support message filtering with the topic parameter. Here, use the wildcard to store any topic message:

## hook: message.publish
## action/function: on_message_publish

backend.mysql.hook.message.publish.1     = {"topic": "#", "action": {"function": "on_message_publish"}, "pool": "pool1"}

Use example

On the WebSocket page of the EMQ X management console, publish multiple messages to the topic upstream topic. EMQ X persists the message list to the mqtt MSG table:

Retain message persistence

Configuration item

Open the configuration file and configure the Backend rule:

## At the same time, open the following rules to enable retain persistence for three lifecycles

## When publishing a non empty retain message (store)
backend.mysql.hook.message.publish.2     = {"topic": "#", "action": {"function": "on_message_retain"}, "pool": "pool1"}

## Querying the retain message when the device subscribes to a topic
backend.mysql.hook.session.subscribed.2  = {"topic": "#", "action": {"function": "on_retain_lookup"}, "pool": "pool1"}

## When publishing an empty retain message (clear)
backend.mysql.hook.message.publish.3     = {"topic": "#", "action": {"function": "on_retain_delete"}, "pool": "pool1"}

Use example

After the connection is established in WebSocket page of EMQ X management console, issue the message and check keep:

Publish (message is not empty)

When a non empty retain message is published, EMQ X will use topic as the unique key to persist the message to the mqtt_retain table. Different retain messages will be sent for the same topic, and only the last message will be persisted:

Subscribe

After the client subscribes to the retain topic, EMQ X will query the mqtt ﹣ retain data table and perform the operation of delivering the retain message.

Publish (message is empty)

In MQTT protocol, publishing an empty retain message will empty the retain record, which will be deleted from the mqtt_retain table.

Message acknowledgment persistence

After the message acknowledgment (ACK) persistence is turned on, when the client subscribes to QoS 1 and QoS 2 level topics, EMQ X will initialize the ACK record in the database with the unique key of clientid + topic.

Configuration item

Open the configuration file, configure the Backend rule, and use the topic wildcard to filter the messages to be applied:

## Initialize ACK record on subscription
backend.mysql.hook.session.subscribed.1  = {"topic": "#", "action": {"function": "on_message_fetch"}, "pool": "pool1"}


## Update arrival status when message arrives
backend.mysql.hook.message.acked.1       = {"topic": "#", "action": {"function": "on_message_acked"}, "pool": "pool1"}

## Delete record lines on unsubscribe
backend.mysql.hook.session.unsubscribed.1= {"topic": "#", "action": {"sql": ["delete from mqtt_acked where clientid = ${clientid} and topic = ${topic}"]}, "pool": "pool1"}

Use example

After the connection is established in WebSocket page of EMQ X management console, subscribe to the topic with QoS > 0:

At this time, the mqtt_acked table will insert the initialization data row. Each time a message with QoS > 0 is published to the subject, the data row mid will increase by 1 automatically after the message arrives:

Custom SQL

In addition to plug-in built-in functions and table structures, emqx ﹣ backend ﹣ MySQL also supports user-defined SQL statements. By using template syntax such as ${clientid} to dynamically construct SQL statements, such as client connection history, update user-defined data tables and other operations can be realized.

SQL statement parameter description

hook	Available parameters	Example (in sql statement, ${name} represents the parameters that can be obtained)
client.connected	clientid	insert into conn(clientid) values(${clientid})
client.disconnected	clientid	insert into disconn(clientid) values(${clientid})
session.subscribed	clientid, topic, qos	insert into sub(topic, qos) values(${topic}, ${qos})
session.unsubscribed	clientid, topic	delete from sub where topic = ${topic}
message.publish	msgid, topic, payload, qos, clientid	insert into msg(msgid, topic) values(${msgid}, ${topic})
message.acked	msgid, topic, clientid	insert into ack(msgid, topic) values(${msgid}, ${topic})
message.delivered	msgid, topic, clientid	insert into delivered(msgid, topic) values(${msgid}, ${topic})

Example of client connection log

The structure of the design table is as follows:

CREATE TABLE `mqtt`.`connect_logs` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `clientid` VARCHAR(255) NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, -- Recording time
  `state` INT NOT NULL DEFAULT 0,  -- Record type: 0 offline 1 Online
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Custom SQL:

## Configure custom SQL in connected hook
## You can configure multiple SQL statements "SQL": ["SQL a", "SQL B", "SQL C"]

## Connection time
backend.mysql.hook.client.connected.3 = {"action": {"sql": ["insert into connect_logs(clientid, state) values(${clientid}, 1)"]}, "pool": "pool1"}

## When disconnected
backend.mysql.hook.client.disconnected.3 = {"action": {"sql": ["insert into connect_logs(clientid, state) values(${clientid}, 0)"]}, "pool": "pool1"}

When the client goes up or down the line, the scheduled SQL statement will be filled in and executed, and the connection record will be written to the connect ﹣ logs table.

Advanced options

backend.mysql.time_range = 5s

backend.mysql.max_returned_count = 500

summary

After understanding the data structure stored in MySQL and user-defined SQL, readers can expand relevant applications in combination with MySQL.