Save data to MySQL

Setup a MySQL database, and changes the username/password to root/public, taking Mac OSX for instance:

$ brew install mysql
$ brew services start mysql
$ mysql -u root -h localhost -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'public';

Initiate MySQL table:

$ mysql -u root -h localhost -ppublic
create "test" database:
CREATE DATABASE test;
create "t_mqtt_msg" table:
USE test;

CREATE TABLE `t_mqtt_msg` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `msgid` varchar(64) DEFAULT NULL,
    `topic` varchar(255) NOT NULL,
    `qos` tinyint(1) NOT NULL DEFAULT '0',
    `payload` blob,
    `arrived` datetime NOT NULL,
    PRIMARY KEY (`id`),
    INDEX topic_index(`id`, `topic`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8MB4;

Create a rule:

Go to EMQX Dashboard (opens new window), select the “rule” tab on the menu to the left.

Select “message.publish”, then type in the following SQL:

SELECT * FROM "message.publish"

Bind an action:

Click on the “+ Add” button under “Action Handler”, and then select “Data to MySQL” in the pop-up dialog window.

Fill in the parameters required by the action:

Two parameters is required by action “Data to MySQL”:

1). SQL template. SQL template is the sql command you’d like to run when the action is triggered. In this example we’ll insert a message into mysql, so type in the following sql template:

insert into t_mqtt_msg(msgid, topic, qos, payload, arrived) values (${id}, ${topic}, ${qos}, ${payload}, FROM_UNIXTIME(${timestamp}/1000))

Before data is inserted into the table, placeholders like ${key} will be replaced by the corresponding values.

2). Bind a resource to the action. Since the dropdown list “Resource” is empty for now, we create a new resource by clicking on the “New Resource” to the top right, and then select “MySQL”:

Configure the resource:

Set “MySQL Database” to “test”, “MySQL Username” to “root”, “MySQL Password” to “public”, and “Description” to “MySQL resource to 127.0.0.1:3306 db=test”, and click on the “Testing Connection” button to make sure the connection can be created successfully, and then click on the “Create” button.

Back to the “Actions” dialog, and then click on the “Confirm” button.

Back to the creating rule page, then click on “Create” button. The rule we created will be show in the rule list:

We have finished, testing the rule by sending an MQTT message to emqx:

> Topic: "t/a"
>
> QoS: 1
>
> Payload: "hello"

Then inspect the MySQL table, verify a new record has been inserted: