Skip to main content

​
MySQL Overview

The MYSQL route stores MQTT messages in MySQL databases. It supports SSL connections, parameterized queries, and automatic reconnection for reliable data persistence.
MySQL is ideal for web applications and general-purpose data storage. Its wide ecosystem support makes it a reliable choice for most IoT data persistence needs.

​
Basic Syntax

DEFINE ROUTE SensorDB WITH TYPE MYSQL
    ADD SQL_CONFIG
        WITH SERVER "mysql.example.com"
        WITH PORT '3306'
        WITH DATABASE "iot_data"
        WITH USERNAME "iot_user"
        WITH PASSWORD "secure_password"
    ADD EVENT StoreSensorReading
        WITH SOURCE_TOPIC "sensors/+/reading"
        WITH QUERY "INSERT INTO readings (ts, sensor_id, value) VALUES (NOW(), '{sensor_id}', '{value.json}')"

​
Connection Configuration

​
SQL_CONFIG Parameters

​
SERVER
string
required
MySQL server hostname or IP address.
​
PORT
integer
MySQL port. Default: 3306.
​
DATABASE
string
required
Target database name.
​
USERNAME
string
required
Database username.
​
PASSWORD
string
required
Database password.
​
USE_SSL
boolean
Enable SSL connection. Default: false.
​
CONNECTION_TIMEOUT
integer
Connection timeout in seconds. Default: 30.

​
Event Configuration

​
EVENT Parameters

​
SOURCE_TOPIC
string
required
MQTT topic pattern that triggers the INSERT. Supports wildcards.
​
DESTINATION_TOPIC
string
MQTT topic to publish query result status.
​
QUERY
string
required
SQL INSERT statement with placeholders for dynamic values.

​
Query Placeholders

PlaceholderDescription
{value.json}Full JSON payload
{value.json.field}Specific field from JSON
{timestamp}Message timestamp
{source_topic}Original MQTT topic
{field}Field extracted from topic path

​
Complete Examples

Store sensor readings with timestamps:
DEFINE ROUTE TemperatureDB WITH TYPE MYSQL
    ADD SQL_CONFIG
        WITH SERVER "mysql.example.com"
        WITH PORT '3306'
        WITH DATABASE "iot_data"
        WITH USERNAME "iot_user"
        WITH PASSWORD "secure_password"
    ADD EVENT StoreTemperature
        WITH SOURCE_TOPIC "sensors/+/temperature"
        WITH QUERY "INSERT INTO temperature_readings (recorded_at, sensor_id, value) VALUES (NOW(), '{sensor_id}', {value.json.value})"

​
Table Schema Examples

-- Basic sensor readings
CREATE TABLE sensor_readings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    sensor_id VARCHAR(50),
    value DOUBLE
);

-- Event log
CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    topic VARCHAR(255),
    payload JSON
);

​
Troubleshooting

  • Verify SERVER and PORT are correct
  • Check MySQL is running and accepting connections
  • Verify firewall allows connections on port 3306
  • Check bind-address in MySQL configuration
  • Verify USERNAME and PASSWORD are correct
  • Check user has permissions on the DATABASE
  • Verify user can connect from your host
  • Check placeholder names match topic structure
  • Verify table and column names exist
  • Test query manually in MySQL client first

​
Next Steps

MariaDB Route

Configure MariaDB storage (MySQL-compatible).

Storage Overview

Compare all storage options.