Configuring MariaDB SQL on Siemens IOT2000/IOT2050 Gateways

The Siemens SIMATIC IOT2000 series (IOT2020, IOT2040, and IOT2050) are industrial-grade IoT gateways built around low-power Intel and ARM SoCs running a Yocto-based Linux. They expose a programmable edge platform on the plant floor, and one of the most common data-handling tasks is persisting process tags, alarms, and event logs into a structured relational store. This reference walks through deploying MariaDB directly on the gateway, mounting external USB storage for the database files, and consuming the service from Node-RED and native C/C++ clients. The procedure is verified against the IOT2000 Example Image 2.4.0 RC2 with mariadb-5.5.65-linux-i686 and the IOT2050 firmware line.

1. Platform Overview and Hardware Variants

The IOT2000 family shares the same SDK and Example Image layout, but the underlying silicon differs and dictates which MariaDB binary you can run.

Because the Quark and Atom cores are 32-bit x86, you must download a build compiled with the -m32 flag (or a generic i686 glibc binary). Attempting to run an x86_64 RPM or tarball on IOT2020/IOT2040 returns Exec format error. The IOT2050, in contrast, is 64-bit ARM and needs an aarch64 build from a Yocto recipe or a vendor-supplied .ipk.

Firmware prerequisites:

• SIMATIC IOT2000 Example Image 2.4.0 (or 2.4.0 RC2) with Node-RED pre-installed. Obtain from the official Siemens Industry Online Support portal (entry ID 109741657 for the IOT2000 SDK).
• Root shell access over serial console or SSH. The default user is root with no password on a freshly flashed image; change it before deployment.
• At least 1 GB free on the internal eMMC or on the attached USB stick that will hold /var/lib/mysql.

2. Architecture and Data Flow

The gateway collects field data from PLCs (S7-1200/1500 via S7 comm, Modbus TCP/RTU, OPC UA, or PROFINET), buffers it in Node-RED flows, and persists selected tags into a MariaDB schema. The database is queried by:

1. Node-RED dashboard flows for HMI visualization (using the node-red-node-mysql node, not the SQLite node).
2. Custom C/C++ programs compiled natively on the device, using the libmysqlclient API or the MariaDB Connector/C.
3. External SCADA or MES clients connecting over TCP/3306 through the optional firewall rule.

Logical topology:

3. Preparing Persistent Storage on USB

Writing InnoDB tables to the internal eMMC is discouraged. The flash wear-leveling budget of consumer-grade eMMC is exhausted quickly under sustained write workloads typical of process logging. Mount a USB stick (preferably an industrial-grade SLC or pSLC device) and relocate /var/lib/mysql onto it.

3.1 Identify the USB device

1. Insert the USB stick. Wait 3 s, then run:
   blkid
2. Note the /dev/sdX1 entry, e.g. /dev/sda1: UUID="4E1C-..." TYPE="ext4".
3. Create the mount point:
   mkdir -p /media/usb
4. Mount manually to verify:
   mount /dev/sda1 /media/usb
5. Copy the existing empty MySQL directory ownership template (we will let mysql_install_db populate it later):
   mkdir -p /media/usb/mysql

3.2 Make the mount persistent in /etc/fstab

Open /etc/fstab and append (use tab characters between fields, not spaces):

Validate with mount -a. The command must return silently. A non-zero exit code indicates a typo in the fstab line or an unsupported filesystem on the stick. The IOT2040 firmware supports ext2, ext3, and ext4 out of the box; NTFS and exFAT require extra ntfs-3g packages that are not in the standard image.

4. Installing MariaDB 5.5.65 (i686) on IOT2020/IOT2040

Validated procedure for the Example Image 2.4.0 RC2:

1. Download the generic Linux binary tarball:
   cd /tmp && wget https://archive.mariadb.org/mariadb-5.5.65/bintar-linux-i686/mariadb-5.5.65-linux-i686.tar.gz
2. Extract:
   tar xzf mariadb-5.5.65-linux-i686.tar.gz -C /opt/
3. Symlink for stable path:
   ln -s /opt/mariadb-5.5.65-linux-i686 /opt/mariadb
4. Add the bin directory to PATH for the current session and /etc/profile:
   export PATH=$PATH:/opt/mariadb/bin
5. Create the system user (idempotent):
   groupadd -g 27 mysql 2>/dev/null; useradd -u 27 -g mysql -d /media/usb/mysql -s /bin/false mysql 2>/dev/null
6. Initialise the data directory on the USB stick:
   /opt/mariadb/scripts/mysql_install_db --user=mysql --datadir=/media/usb/mysql --basedir=/opt/mariadb
7. Confirm directory ownership:
   chown -R mysql:mysql /media/usb/mysql

5. Configuring mysqld

Create /etc/my.cnf (or /opt/mariadb/my.cnf) with parameters tuned for an embedded edge device:

[mysqld]
user            = mysql
datadir         = /media/usb/mysql
socket          = /tmp/mysql.sock
port            = 3306
bind-address    = 0.0.0.0

# Memory budget for ~1 GB RAM
key_buffer_size         = 16M
max_allowed_packet      = 8M
thread_stack            = 192K
thread_cache_size       = 8
query_cache_size        = 0
tmp_table_size          = 16M
max_heap_table_size     = 16M
innodb_buffer_pool_size = 128M
innodb_log_file_size    = 32M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method     = O_DIRECT

[client]
socket          = /tmp/mysql.sock
port            = 3306
user            = root

Notes on the choices:

• bind-address = 0.0.0.0 allows SCADA/MES clients on the plant LAN. Restrict with a firewall rule (e.g. iptables -A INPUT -p tcp --dport 3306 -s 10.0.0.0/8 -j ACCEPT) if the device is on a routable network.
• innodb_buffer_pool_size = 128M is conservative for the 1 GB IOT2040; do not exceed 25% of total RAM or Linux OOM-killer behaviour will interfere with mysqld.
• innodb_flush_log_at_trx_commit = 2 trades durability (1 s loss window) for a 10× write throughput improvement — acceptable for trend logging, not for transactional billing.

6. Launch and Verify the Database

Start the daemon:

Wait 3 s, then verify:

/opt/mariadb/bin/mysqladmin -uroot ping
mysqld is alive

Open the monitor and set a strong root password:

/opt/mariadb/bin/mysql -uroot
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('StrongEdge!2024');
mysql> DELETE FROM mysql.user WHERE User='';
mysql> DROP DATABASE test;
mysql> FLUSH PRIVILEGES;

Create the application schema:

CREATE DATABASE iotdata CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'iotwriter'@'%' IDENTIFIED BY 'TagWrite#1';
GRANT SELECT,INSERT,UPDATE,DELETE ON iotdata.* TO 'iotwriter'@'%';
FLUSH PRIVILEGES;

USE iotdata;
CREATE TABLE process_log (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  ts          DATETIME(3)     NOT NULL,
  tag_name    VARCHAR(64)     NOT NULL,
  tag_value   DOUBLE          NOT NULL,
  quality     TINYINT         NOT NULL DEFAULT 192,
  PRIMARY KEY (id),
  KEY idx_tag_ts (tag_name, ts)
) ENGINE=InnoDB;

7. Connecting from Node-RED

1. In the Node-RED palette manager (http://<ip>:1880) install node-red-node-mysql.
2. Drag the mysql node into a flow and double-click to configure:

Example function-node payload feeding the mysql node:

msg.topic = "INSERT INTO process_log (ts, tag_name, tag_value, quality) VALUES (NOW(3), ?, ?, ?)";
msg.payload = ["Tank1.Level", msg.level, 192];
return msg;

A companion query flow for the dashboard can read the last 50 rows:

msg.topic = "SELECT ts, tag_value FROM process_log WHERE tag_name='Tank1.Level' ORDER BY ts DESC LIMIT 50";
return msg;

Bind the output of the query node to a ui_chart node from node-red-dashboard.

8. Connecting from Native C/C++

Two paths are viable on the IOT2000:

8.1 MariaDB Connector/C (recommended)

1. Download mysql-connector-c-6.1.11-linux-glibc2.12-i686.tar.gz from the MariaDB mirrors.
2. Extract to /opt/connector-c.
3. Compile a sample writer:
   gcc -I/opt/connector-c/include -L/opt/connector-c/lib -o taglog taglog.c -lmysqlclient -lpthread

Minimal writer taglog.c:

#include <mysql.h>
#include <stdio.h>

int main(int argc, char *argv[]) {
    MYSQL *con = mysql_init(NULL);
    if (con == NULL) { fprintf(stderr,"init failed\n"); return 1; }
    if (mysql_real_connect(con, "127.0.0.1", "iotwriter", "TagWrite#1",
                           "iotdata", 3306, NULL, 0) == NULL) {
        fprintf(stderr,"connect: %s\n", mysql_error(con)); return 2;
    }
    char q[256];
    snprintf(q, sizeof q,
        "INSERT INTO process_log (ts,tag_name,tag_value) VALUES (NOW(3),'%s',%f)",
        argv[1], atof(argv[2]));
    if (mysql_query(con, q)) {
        fprintf(stderr,"query: %s\n", mysql_error(con)); return 3;
    }
    mysql_close(con);
    return 0;
}

8.2 Wiring libmysqlclient for cross-compile from a Linux host

The Siemens IOT2000 SDK ships a Yocto SDK installer (iot2000-sdk-image-*.sh). Source the environment script, then compile with the cross toolchain so the resulting binary links against the on-device glibc and runs natively on the IOT2040.

9. Adapting the Procedure to IOT2050

The IOT2050 ships a different Yocto layer (based on the TISCI SDK) and is 64-bit ARM. The high-level steps remain valid, but you must:

1. Obtain the IOT2050 Advanced Example Image from Siemens support (firmware line 1.1.x and newer includes a working opkg feed).
2. Install MariaDB through the Yocto feed:
   opkg update && opkg install mariadb-server mariadb-client
3. Note that /var/lib/mysql defaults to internal eMMC on IOT2050; redirect via the same fstab procedure described in section 3, using the ext4 partition on a USB SSD (industrial-grade recommended).
4. The my.cnf tuning block from section 5 applies unchanged, but raise innodb_buffer_pool_size to 256M on the 2 GB SKU.

Both node-red-node-mysql and Connector/C for aarch64 are pre-built in the IOT2050 Advanced image, eliminating the manual download step.

10. systemd-style Auto-start (Optional)

The IOT2000 Example Image uses systemd. Create /lib/systemd/system/mariadb-iot.service:

[Unit]
Description=MariaDB on IOT2000
After=network.target local-fs.target
RequiresMountsFor=/media/usb

[Service]
Type=forking
User=mysql
Group=mysql
ExecStart=/opt/mariadb/bin/mysqld_safe --datadir=/media/usb/mysql --user=mysql
ExecStop=/opt/mariadb/bin/mysqladmin -uroot -pStrongEdge!2024 shutdown
Restart=on-failure
RestartSec=5

[Install]
WantedBy=multi-user.target

Enable and start:

systemctl daemon-reload
systemctl enable mariadb-iot.service
systemctl start mariadb-iot.service
systemctl status mariadb-iot.service

Watch the journal for mysqld: ready for connections within 3 s of the start.

11. Verification and Acceptance Test

After deployment, run the following acceptance checklist:

12. Troubleshooting Matrix

13. Security and Hardening Checklist

• Change the MariaDB root password immediately after mysql_install_db.
• Remove anonymous users and the test schema (commands in section 6).
• Create one application user per service with the minimum required GRANT.
• Restrict bind-address to 127.0.0.1 when only on-device consumers are used.
• Enable the Siemens firewall (iptables) and allow 3306 only from the plant LAN CIDR.
• Set up daily mysqldump cron to the USB stick for local rollback, and replicate to a central server with mysqldump --master-data if HA is required.
• Disable Node-RED admin API exposure to the plant LAN (settings.js: adminAuth enabled).

14. Performance Expectations

Indicative figures measured on the IOT2040 with Example Image 2.4.0 RC2, single client, 1 KB rows:

Use these as planning baselines; your workload will vary by row size, index count, and USB stick speed.

15. Summary

MariaDB 5.5.65 on the IOT2000 (and MariaDB 10.x on the IOT2050) provides a robust, locally hosted relational store for edge analytics on the SIMATIC gateway line. The combination of USB-mounted /var/lib/mysql, the node-red-node-mysql palette node, and MariaDB Connector/C covers the three primary consumption paths. Avoid the SQLite node — it is not build-compatible with the Quark/Atom baseline. Use the Example Image 2.4.0 RC2 or the IOT2050 Advanced image for a known-good starting point, validate with the acceptance table in section 11, and consult the official Siemens Industry Online Support portal for the latest firmware notes and security advisories.