【Arduino】 通过ESP8266将传感器采集到的数据通过mqtt传输永久化到MySQL

注：本文系湛江市第十七中学星火创客团队及岭南师范学院物联网俱乐部原创部分参赛项目，转载请保留声明

---

【Arduino】 通过ESP8266将传感器采集到的数据通过mqtt传输永久化到MySQL

在物联网应用开发中，采集到的数据通常需要拿来做数据归纳和分析，但是我们常常会陷于传感器采集到的数据没有一个规范的的存储的困境中，随着学习的进一步，笔者正好学习到这一步之中，让我们来看看如何实现通过mqtt来数据永久化

提示：实现这个部分有两种途径
1、用本地部署emqx 企业版，宝塔新建部署MySQL数据库

2、使用emqx cloud部署，在宝塔新建部署MySQL数据库（本文使用方法）

一、所需准备

硬件部分

硬件
控制板	WEMOS D1(ESP8266)
传感器	DHT11温湿度传感器
杜邦线	若干

• 接线

VCC	VCC
GND	GND
DATA	D7

软件部分

软件下载
Arduino IDE
MQTTX
EMQX
宝塔
MySQL

提示：点击超链接可获取资料

服务器

• 腾讯云 Ubuntu 20.4

二、宝塔

1、宝塔的安装

复制指令在终端中安装，详情可参考：-- 点这里

wget -O install.sh http://download.bt.cn/install/install-ubuntu_6.0.sh && bash install.sh

• 安装完成后会得到，记得保存：
  
  之后使用外网面板地址和账户密码进入宝塔面板

2、数据库的创建和安全端口的开放

创建自己的MySQL数据库

创建一个数据库表，等会用得上

创建温湿度表的SQL语句：

CREATE TABLE `temp_hum` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `up_timestamp` timestamp NULL DEFAULT NULL,
  `client_id` varchar(32) DEFAULT NULL,
  `temp` float unsigned DEFAULT NULL,
  `hum` float unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `up_timestamp_client_id` (`up_timestamp`,`client_id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4;

放行数据库端口

三、EMQX

1、账号注册与登陆

• 注册账户
  
• 登陆账号
  
• 试用服务
  
  直接选用180天免费，然后确认就可以了
  
  获得部署
  

设置资源和规则

• 创建资源
  输入你服务器的IP地址和数据库信息
  
• 创建规则

SELECT 

timestamp as up_timestamp, clientid as client_id, payload.temp as temp, payload.hum as hum  

FROM  

"temp_hum/emqx"  

• 响应动作

insert into temp_hum(up_timestamp, client_id, temp, hum) values (FROM_UNIXTIME(${up_timestamp}/1000), ${client_id}, ${temp}, ${hum}) 

具体可以参考官方文档，不多赘述了

2、连接到部署资源

打开MQTTX，将刚才获得的信息填入创建连接

3、Arduino 代码

#include <ESP8266WiFi.h>
#include <PubSubClient.h>
#include <dht.h>


// Update these with values suitable for your network.

const char* ssid = "--------"; //你的WiFi
const char* password = "--------------"; //你的WiFi密码
const char* mqtt_server = "------------"; //你的部署服务器地址

//订阅的topic
const char* pubTopic = "------------";  //你的topic

dht DHT;
#define DHT11_PIN D2
float temp, hum;

WiFiClient espClient;  //不用改
PubSubClient client(espClient);
long lastMsg = 0;
char msg[50];
int value = 0;

void setup_wifi() {  //不用改

  delay(10);
  // We start by connecting to a WiFi network
  Serial.println();
  Serial.print("Connecting to ");
  Serial.println(ssid);

  WiFi.begin(ssid, password);

  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }

  randomSeed(micros());

  Serial.println("");
  Serial.println("WiFi connected");
  Serial.println("IP address: ");
  Serial.println(WiFi.localIP());
}

void callback(char* topic, byte* payload, unsigned int length) {
  Serial.print("Message arrived [");
  Serial.print(topic);
  Serial.print("] ");
  for (int i = 0; i < length; i++) {
    Serial.print((char)payload[i]);
  }
  Serial.println();

  // Switch on the LED if an 1 was received as first character
  if ((char)payload[0] == '1') {
    digitalWrite(BUILTIN_LED, LOW);   // Turn the LED on (Note that LOW is the voltage level
    // but actually the LED is on; this is because
    // it is active low on the ESP-01)
  } else {
    digitalWrite(BUILTIN_LED, HIGH);  // Turn the LED off by making the voltage HIGH
  }

}

void reconnect() {
  // Loop until we're reconnected
  while (!client.connected()) {
    Serial.print("Attempting MQTT connection...");
    // Create a random client ID
    String clientId = "ESP8266Client-";
    clientId += String(random(0xffff), HEX);
    // Attempt to connect
    if (client.connect(clientId.c_str())) {
      Serial.println("connected");
      // Once connected, publish an announcement...
      client.publish("outTopic", "hello world");
      // ... and resubscribe
      client.subscribe("inTopic");
    } else {
      Serial.print("failed, rc=");
      Serial.print(client.state());
      Serial.println(" try again in 5 seconds");
      // Wait 5 seconds before retrying
      delay(5000);
    }
  }
}

void setup() {
  pinMode(BUILTIN_LED, OUTPUT);     // Initialize the BUILTIN_LED pin as an output
  Serial.begin(115200);
  setup_wifi();
  client.setServer(mqtt_server, 11215);
  client.setCallback(callback);
}

void loop() {

  if (!client.connected()) {
    reconnect();
  }
  client.loop();

  long now = millis();
  if (now - lastMsg > 2000) {
    Get_Dht11_data();
    lastMsg = now;
    ++value;
    snprintf (msg, 50,  "{\"temp\": \"%.1f\",\"hum\": \"%.1f\"}");  //json的数据格式
    Serial.print("Publish message: ");
    Serial.println(msg);
    client.publish("pubTopic", msg);
  }
}

void Get_Dht11_data() {
  int chk = DHT.read11(DHT11_PIN);
  
  temp = DHT.temperature;
  hum = DHT.humidity;
}

最终效果

MySQL：

topic接收情况：

总结

• [ 感觉有点凌乱，步骤应该可以优化，后续改改。]