项目实战-物联网平台搭建-EMQ x数据转存mysql（二）

超、凢脫俗 2022-12-13 04:38 976阅读 0赞

首先安装mysql

**官网下载安装mysql-server**

\# wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm

\# rpm -ivh mysql-community-release-el7-5.noarch.rpm

\# yum install mysql-community-server

安装成功后重启mysql服务。

\# service mysqld restart

进入mysql：

mysql -u root

设置密码（密码为：password）：

set password for 'root'@'localhost' =password('password');

登陆：

mysql -u root –p

创建mqtt数据库：

create database mqtt;

### MySQL 设备在线状态表 ###

创建*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,
      `node` varchar(64) 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`),
      INDEX topic_index(`id`, `clientid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8MB4;

### MySQL 主题订阅表 ###

创建*mqtt\_cub表：*

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(180) DEFAULT NULL,
      `qos` tinyint(1) DEFAULT NULL,
      `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`),
      KEY `mqtt_sub_idx` (`clientid`,`topic`,`qos`),
      UNIQUE KEY `mqtt_sub_key` (`clientid`,`topic`),
      INDEX topic_index(`id`, `topic`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8MB4;

### MySQL 消息存储表 ###

创建mqtt\_msg表：

DROP TABLE IF EXISTS `mqtt_msg`;
    CREATE TABLE `mqtt_msg` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `msgid` varchar(64) DEFAULT NULL,
      `topic` varchar(180) NOT NULL,
      `sender` varchar(64) DEFAULT NULL,
      `node` varchar(64) DEFAULT NULL,
      `qos` tinyint(1) NOT NULL DEFAULT '0',
      `retain` tinyint(1) DEFAULT NULL,
      `payload` blob,
      `arrived` datetime NOT NULL,
      PRIMARY KEY (`id`),
      INDEX topic_index(`id`, `topic`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8MB4;

### MySQL 保留消息表 ###

创建mqtt\_retain表：

DROP TABLE IF EXISTS `mqtt_retain`;
    CREATE TABLE `mqtt_retain` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `topic` varchar(180) DEFAULT NULL,
      `msgid` varchar(64) DEFAULT NULL,
      `sender` varchar(64) DEFAULT NULL,
      `node` varchar(64) DEFAULT NULL,
      `qos` tinyint(1) DEFAULT NULL,
      `payload` blob,
      `arrived` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`),
      UNIQUE KEY `mqtt_retain_key` (`topic`),
      INDEX topic_index(`id`, `topic`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8MB4;

### MySQL 消息确认表 ###

创建mqtt\_acked表：

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

###  ###

## MySQL 数据存储 ##

修改配置文件: emqx\_backend\_mysql.conf

### 配置 MySQL 服务器 ###

支持配置多台 MySQL 服务器连接池:

## Mysql 服务器地址
    backend.mysql.pool1.server = 127.0.0.1:3306
    
    ## Mysql 连接池大小
    backend.mysql.pool1.pool_size = 8
    
    ## Mysql 用户名
    backend.mysql.pool1.user = root
    
    ## Mysql 密码
    backend.mysql.pool1.password = public
    
    ## Mysql 数据库名称
    backend.mysql.pool1.database = mqtt

# 目录结构 #

不同安装方式得到的 EMQ X 其目录结构会有所不同，具体如下:

<table> 
 <thead> 
  <tr> 
   <th>描述</th> 
   <th>使用 ZIP 压缩包安装</th> 
   <th>使用二进制包安装</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>可执行文件目录</td> 
   <td><code>./bin</code></td> 
   <td><code>/usr/lib/emqx/bin</code></td> 
  </tr> 
  <tr> 
   <td>数据文件</td> 
   <td><code>./data</code></td> 
   <td><code>/var/lib/emqx/data</code></td> 
  </tr> 
  <tr> 
   <td>Erlang 虚拟机文件</td> 
   <td><code>./erts-*</code></td> 
   <td><code>/usr/lib/emqx/erts-*</code></td> 
  </tr> 
  <tr> 
   <td>配置文件目录</td> 
   <td><code>./etc</code></td> 
   <td><code>/etc/emqx/etc</code></td> 
  </tr> 
  <tr> 
   <td>依赖项目录</td> 
   <td><code>./lib</code></td> 
   <td><code>/usr/lib/emqx/lib</code></td> 
  </tr> 
  <tr> 
   <td>日志文件</td> 
   <td><code>./log</code></td> 
   <td><code>/var/log/emqx</code></td> 
  </tr> 
  <tr> 
   <td>启动相关的脚本、schema 文件</td> 
   <td><code>./releases</code></td> 
   <td><code>/usr/lib/emqx/releases</code></td> 
  </tr> 
 </tbody> 
</table>

### 启用 MySQL 数据存储插件 ###

./bin/emqx_ctl plugins load emqx_backend_mysql

或者在mqtt web页面启动

插件：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1clFR_size_16_color_FFFFFF_t_70][]

之后就可已在mysql数据库表中查看信息了

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1clFR_size_16_color_FFFFFF_t_70]: /images/20221123/0fa5d370721343a0b835b95cef9a4dee.png