Есть у меня старый NAS Synology DS110j. Как ясно из названия модели, ему не меньше 12 лет и его сердце в виде ARMv5 800MHz одноядерного CPU уже с большим трудом справляется с новыми вызовами. Но на Трансмишн и Москитто его вполне хватает. Правда поддержка старых моделей полностью прекращена, так что в менеджере дополнительных пакетов пусто, хотя с помощью небольших уловок можно поставить поддерживаемые версии пакетов.
Итак, раз есть Москитто, значит можно и сделать шлюз к БД для регулярного сохранения публикуемых данных. В качестве БД я выбрал MySQL (MariaDB), в частности для столь древнего устройства версии старше 5.5.47 не нашлось, но нам и этого хватит. Поиск по Интернету по поводу публикации топиков из Mosquitto в MySql дал два решения, одно на Node.js и второе на питоне. Я со змеями не дружу. А когда пакет с питоном 3.10 ставился на NAS более получаса... В итоге было принято решение использовать в качестве языка скрипта PHP, благо он входит в комплект ПО NAS, хотя и тоже преклонной версии 5.5.33.
Есть у меня проект со снятием показаний импульсных счетчиков воды и отправкой данных раз в сутки в MQTT и в Телеграм. И вот как раз на его примере я и покажу, как из MQTT в MySql малой кровью сохранять данные.
Разные счетчики публикуют топики, в конце которых для идентификации есть 3 октета MAC адреса ESP. Т.е. топик имеет вид /WaterCounter/XXYYZZ, а тело сообщения есть JSON пакет с данными вида {"Location":"Bathroom","Cold":130.08,"Hot":115.86,"Leak":false,"Battery":3.18,"RSSI":-46}.
Что же нам нужно? Нужна БД в MySql, для чего удобнее установить phpMyAdmin и дальше действовать в нем. Создадим новую БД, например mqtt, с двумя (в моем случае!) таблицами (хотя можно легко обойтись одной, но будет не так нудно :) ).
CREATE TABLE IF NOT EXISTS `devices` (
`id` char(6) NOT NULL,
`location` varchar(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `devices`
ADD PRIMARY KEY (`id`);
CREATE TABLE IF NOT EXISTS `measures` (
`when` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`device` char(6) NOT NULL,
`cold` decimal(10,2) NOT NULL,
`hot` decimal(10,2) NOT NULL,
`leak` tinyint(1) NOT NULL,
`battery` decimal(3,2) NOT NULL,
`rssi` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `measures`
ADD PRIMARY KEY (`when`,`device`);
Таблица devices содержит в себе записи об уникальных счетчиках с хвостом MAC адреса и расположением. Основная же таблица measures будет наполняться записями по мере публикации топиков.
В целях повышения безопасности лучше создать в MySql отдельного пользователя (например mqtt) и дать ему права на чтение/изменение/удаление данных в созданной нами ранее БД mqtt. В скрипте для подключения к БД будем использовать именно его, а не root.
Вот мы и дошли наконец до PHP... Изобретать велосипед не хотелось, так что библиотека для работы с MQTT также была найдена на просторах Интернета. Но вы же помните, что у меня PHP 5.5 без возможности обновиться на актуальные версии? Поэтому подошла только библиотека https://github.com/bluerhinos/phpMQTT и ее пришлось немного поправить. Автору библиотеки наш почет и уважение!
<?php
require('phpMQTT.php');
$mqtt_broker = 'localhost';
$mqtt_port = 1883;
$mqtt_user = '';
$mqtt_pswd = '';
$mqtt_client = 'MySqlitto';
$mqtt_topic = '/WaterCounter/#';
$mysql_server = 'localhost';
$mysql_user = 'mqtt';
$mysql_pswd = 'p@ssw0rd';
$mysql_db = 'mqtt';
$mqtt = new Bluerhinos\phpMQTT($mqtt_broker, $mqtt_port, $mqtt_client);
if (! $mqtt->connect(true, NULL, $mqtt_user, $mqtt_pswd)) {
echo "MQTT connection failed!\n";
exit(1);
}
$mysqli = new mysqli($mysql_server, $mysql_user, $mysql_pswd, $mysql_db);
if ($mysqli->connect_errno) {
echo "MySQL connection failed: " . $mysqli->connect_error . "!\n";
exit(1);
}
// $mqtt->debug = true;
$topics[$mqtt_topic] = array('qos' => 0, 'function' => 'onMsg');
$mqtt->subscribe($topics, 0);
echo "Waiting for MQTT messages...\n";
while ($mqtt->proc(true)) {}
$mysqli->close();
$mqtt->close();
function onMsg($topic, $msg) {
$t = explode('/', $topic);
$device = $t[count($t) - 1];
unset($t);
$payload = json_decode($msg, false);
if (! is_null($payload)) {
global $mysqli;
$stmt = $mysqli->prepare("REPLACE `devices` SET `id`=?, `location`=?");
$stmt->bind_param('ss', $device, $payload->Location);
if ($stmt->execute()) {
$stmt->close();
unset($stmt);
$leak = (int)$payload->Leak;
$stmt = $mysqli->prepare("INSERT INTO `measures` (`device`, `cold`, `hot`, `leak`, `battery`, `rssi`) VALUES (?, ?, ?, ?, ?, ?)");
$stmt->bind_param('sddidi', $device, $payload->Cold, $payload->Hot, $leak, $payload->Battery, $payload->RSSI);
if (! $stmt->execute()) {
echo "Insert new data failed!\n";
echo "Device ID: $device, Cold: $payload->Cold, Hot: $payload->Hot, Leak: $payload->Leak, Battery: $payload->Battery, RSSI: $payload->RSSI\n";
}
} else {
echo "Update location failed!\n";
echo "Device ID: $device, Location: $payload->Location\n";
}
$stmt->close();
} else {
echo "JSON payload parse failed!\n";
}
}
?>
Из имени топика получаем MAC как хвост имени, парсим JSON пакет с самим сообщением и добавляем запись в таблицу, используя поля объекта, полученного из JSON.
Отлично, скрипт на PHP готов. Осталось его запустить на постоянной основе. Т.е. что-нибудь вроде
php /path/MySqlitto.php &
Скрипт не универсален и рассчитан на решение конкретной задачи! MySql 5.5 не умеет работать с JSON напрямую, иначе проще было бы создать хранимую процедуру, в которой разбирать JSON пакет и сохранять совпадающие по имени поля в поля таблицы. Но как отправная точка должен сгодиться.