Ingesting IoT data into MySQL using MQTT and EMQX | MQTT MySQL Integration
This tutorial will show you how to use MQTT to ingest IoT data into MySQL. We will be using the EMQX MQTT broker to publish and subscribe to messages. We will also be using the MySQL database to store the data.
The scenario of this demo is based on oil extraction, enabling remote monitoring of real-time production data such as oil pressure, casing pressure, back pressure, wellhead temperature, and production rate. It utilizes Grafana to provide corresponding real-time status visualization and historical trend analysis charts. This enables real-time monitoring and management of devices in the process of oil extraction.
Why MySQL?
MySQL can be used to store and manage large volumes of data generated by Internet of Things (IoT) devices. It effectively handles and organizes sensor data, device status, log information, and more, providing reliable data storage and access.
MySQL can be used to store and manage device attributes, configuration information, and metadata. By utilizing MySQL's querying and updating capabilities, device registration, querying, configuration updates, and management can be achieved.
| Name | Version | Description |
|---|---|---|
| EMQX Enterprise | 5.0.3+ | MQTT broker used for message exchange between MQTT clients and the MySQL. |
| MQTTX CLI | 1.9.3+ | Command-line tool used to generate simulated data for testing. |
| MySQL | 8.0.0+ | Oil extraction IoT data storage and management, as well as providing time aggregation and analysis capabilities for Grafana. |
| EMQX Exporter | 0.1 | Prometheus exporter for EMQX |
| Prometheus | v2.44.0 | Open-source systems monitoring and alerting toolkit. |
| Grafana | 9.5.1+ | Visualization platform utilized to display and analyze the collected data. |
- Please make sure you have installed the docker, and then running the following commands to start the demo:
docker-compose up -dYou can quickly init submodule and start the demo by using the
makecommand as well.
- Running the following commands to see the message from MQTTX:
docker logs -f mqttx- If you want to view the oil extraction data and EMQX Metrics in Grafana dashboard, you can open http://localhost:3000 in your browser, and login with
admin:public.
