Ingest Data into 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` bigint 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(`topic`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8MB4;
Create a rule:
Go to EMQX Dashboard, 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 runwhen 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 ${id} will be replaced by the corresponding values.
If a placeholder variable is undefined, you can use the Insert undefined value as Null option to define the rule engine behavior:
false
(default): The rule engine can insert the stringundefined
into the database.true
: Allow the rule engine to insertNULL
into the database when a variable is undefined.
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 "Create" 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".
Click the "Test" button to make sure the connection can be created successfully, and then click the "Confirm" 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 shown 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: