Реализация: запись логов в MySQL/MQTT

Урок 2 · Сценарии умного дома: режимы, состояния, приоритеты · 30 мин · theory

Архитектура системы логирования: MQTT + MySQL

Эффективная система логирования — это не просто запись событий в файл, а продуманная архитектура, обеспечивающая надежность, масштабируемость и удобство анализа. В рамках платформы HI мы применяем двухуровневый подход, основанный на связке MQTT и MySQL. Эта архитектура разделяет процесс генерации события и его сохранения, что кардинально повышает отказоустойчивость всей системы автоматизации.

Ключевая идея заключается в декаплинге (разделении) источника логов и хранилища. Любой сценарий, будь то управление освещением или система безопасности, не должен напрямую взаимодействовать с базой данных. Вместо этого он публикует стандартизированное сообщение в специальный MQTT-топик.

> 💡 Подсказка: Использование выделенного MQTT-топика для логов, например `hi/system/log`, позволяет легко отлаживать систему и управлять потоком данных, не смешивая его с командами управления устройствами. Вы можете временно подписаться на этот топик любым MQTT-клиентом для мониторинга событий в реальном времени, не затрагивая основную логику.

Преимущества двухуровневой архитектуры

Отказоустойчивость: Если сервер MySQL временно недоступен (например, во время обслуживания или перезагрузки), система автоматизации продолжает работать. Сообщения о событиях остаются в MQTT-брокере (если настроено персистентное хранение) или просто отбрасываются, но сам сценарий, сгенерировавший событие, не "зависает" в ожидании ответа от базы данных. Это критически важно для систем, где требуется высокий аптайм.

Масштабируемость: Нагрузка на запись в БД изолирована в одном месте. Если поток логов вырастет, вам потребуется оптимизировать только один флоу-обработчик, а не десятки сценариев по всему проекту. Кроме того, можно легко добавить альтернативные обработчики логов (например, отправку в Elasticsearch или облачную систему мониторинга), просто подписав их на тот же MQTT-топик.

Стандартизация: Все сообщения, передаваемые через топик `hi/system/log`, обязаны соответствовать единому "контракту логов", который мы рассматривали в предыдущем уроке. Это гарантирует, что в базу данных попадают структурированные и предсказуемые данные, что значительно упрощает их последующий анализ, фильтрацию и построение отчетов.

Общая схема потока данных

Процесс от возникновения события до его сохранения в базе данных выглядит следующим образом:

+-------------------+      +-----------------+      +--------------------+      +----------------+      +-------------------+
|     Источник      |      |   Формирование  |      |    MQTT-брокер     |      |    Обработчик    |      |    База данных    |
| (Сценарий в N-R)  | ---> |   сообщения     | ---> | (на контроллере HI)| ---> | (Флоу в N-R)     | ---> |  (MySQL на HI)    |
| e.g. Управление   |      | (Subflow Logger)|      | Топик:             |      | - Подписка на топик|      | Таблица:          |
|      светом       |      | - Добавить ts   |      | hi/system/log      |      | - Валидация        |      | `audit_log`       |
|                   |      | - Добавить source|      |                    |      | - SQL-запрос       |      |                   |
+-------------------+      +-----------------+      +--------------------+      +----------------+      +-------------------+

Эта схема четко разделяет обязанности:

Сценарии-продюсеры: генерируют события и отправляют их в сабфлоу-логгер.
Сабфлоу-логгер: форматирует событие согласно контракту и публикует в MQTT.
MQTT-брокер: выступает как транспортный буфер.
Флоу-консьюмер: подписывается на MQTT, валидирует данные и безопасно записывает их в MySQL.

---

Отправка логов с помощью сабфлоу

Чтобы избежать дублирования кода и стандартизировать отправку логов из любого сценария, мы используем универсальный сабфлоу `[SYS] MQTT Logger`. Его создание и внутренняя логика были подробно разобраны в уроке, посвященном сабфлоу (COURSE-07-M02-L03), как ключевой пример их практического применения.

Напомним, что этот сабфлоу решает следующие задачи:

Принимает на вход информацию о событии (`msg.payload`) и его источнике (`msg.topic`).
Обогащает сообщение стандартными полями: временной меткой `ts`, уровнем важности `priority` и т.д.
Формирует и публикует итоговое JSON-сообщение в центральный MQTT-топик `hi/system/log/{priority}/{source}`.

Таким образом, из любого сценария для отправки лога достаточно вызвать этот "готовый" сабфлоу.

Пример использования:

// msg объект, который мы отправляем в сабфлоу [SYS] MQTT Logger
{
    "topic": "SCN-SAFETY-011",
    "payload": {
        "event": "Датчик протечки сработал",
        "priority": "critical",
        "details": {
            "sensor_id": "WD-KITCHEN-01",
            "location": "Кухня, под мойкой"
        }
    }
}

Теперь, когда мы определили, как сообщения-логи попадают в MQTT, перейдем к их приему и сохранению в базу данных.

---

Практика: Подписка на MQTT и подготовка данных для БД

рактика: Подписка на MQTT и подготовка данных для БД

Теперь создадим отдельный, независимый флоу, который будет слушать наш лог-топик и складывать данные в базу.

> ⚠️ Внимание: Критически важно избегать циклов логирования. Если ваш флоу-обработчик логов сам генерирует ошибку и пытается записать ее в тот же MQTT-топик, вы рискуете создать бесконечный цикл, который парализует систему. Ошибки этого флоу-обработчика следует направлять в отдельный, более простой механизм (например, системный лог Linux через ноду `exec` или просто в файл через ноду `file`).

Шаг 1: Создание флоу-обработчика

Создайте новую вкладку (flow) в Node-RED и назовите ее, например, `[System] Log Collector`.

Шаг 2: Настройка ноды `mqtt in`

Это точка входа для всех логов системы.

Server: Выберите ваш MQTT-брокер.
Topic: `hi/system/log/#`. Символ `#` означает подписку на все подуровни топика, что позволит нам получать логи всех уровней важности.
QoS: `1`.
Output: `a parsed JSON object`. Это очень важно, так как Node-RED автоматически преобразует входящую JSON-строку в JavaScript-объект, что упростит дальнейшую обработку.
Name: `Подписка на логи`.

Шаг 3: Валидация входящего сообщения

Не все сообщения в топике могут быть корректными. Перед записью в БД мы должны убедиться, что сообщение соответствует "контракту лога". Для этого используем ноду `switch`.

Property: `msg.payload`
Настройте проверку на наличие ключевых полей. Пример для поля `source`:

* `has key`: `source`

Добавьте аналогичные проверки для полей `event` и `priority`.
В настройках ноды `switch` выберите `Checking all rules`. Нода будет иметь один выход для валидных сообщений и один для всех остальных.
Невалидные сообщения следует направить на ноду `debug` или в отдельный механизм логирования ошибок, как упоминалось в предупреждении выше.

[MQTT In] ---> [Switch: Валидация контракта] --(валидно)--> [Дальнейшая обработка]
                          |
                          '--(невалидно)--> [Debug: "Невалидный лог"]

Шаг 4: Подготовка данных для SQL-запроса

После валидации у нас есть корректный

Теория и Практика: Формирование и выполнение SQL-запроса

Для работы с базой данных MySQL на контроллере HI мы будем использовать популярную ноду `node-red-contrib-mysql`. Она обеспечивает простое и, что самое главное, безопасное взаимодействие с БД.

### Предварительные шаги: Создание таблицы в MySQL

Прежде чем записывать логи, нам нужна таблица для их хранения. Подключитесь к вашей MySQL-базе и выполните следующий SQL-запрос:

CREATE TABLE `audit_log` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `ts` DATETIME(3) NOT NULL,
  `priority` VARCHAR(20) NOT NULL,
  `source` VARCHAR(100) NOT NULL,
  `event` VARCHAR(255) NOT NULL,
  `value` VARCHAR(255) NULL,
  `unit` VARCHAR(20) NULL,
  `details` JSON NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_ts` (`ts`),
  INDEX `idx_priority_source` (`priority`, `source`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

`DATETIME(3)` используется для хранения временной метки с точностью до миллисекунд.
`details` имеет тип `JSON` для удобного хранения структурированных данных.
Индексы (`idx_ts`, `idx_priority_source`) ускорят будущую фильтрацию и поиск по логам.

### Безопасность: Предотвращение SQL-инъекций

> ⚠️ Внимание: Никогда не формируйте SQL-запрос путем конкатенации (склеивания) строк со значениями от пользователя или из внешних систем. Это прямой путь к уязвимости типа SQL-инъекция.

Нода `node-red-contrib-mysql` поддерживает параметризованные запросы. Это означает, что мы передаем шаблон SQL-запроса и значения для него раздельно. Драйвер базы данных сам безопасно подставляет значения, экранируя все спецсимволы.

Шаблон запроса с плейсхолдерами `?` передается в `msg.topic`.
Массив со значениями для плейсхолдеров передается в `msg.payload`.

### Практика: Настройка нод `function` и `mysql`

Дополним наш флоу `[System] Log Collector`.

... --(валидно)--> [Function: Подготовка SQL] ---> [MySQL: Запись в audit_log] ---> [Debug: "Успешная запись"]
                                                          |
                                                          +---(ошибка)--> [Debug: "Ошибка записи в БД"]

Нода `function: Подготовка SQL`

Эта нода берет валидированный JSON-объект и готовит `msg` для ноды `mysql`.

    // msg.payload содержит валидный объект лога
    const log = msg.payload;

    // 1. Формируем шаблон SQL-запроса и помещаем его в msg.topic
    // Порядок `?` должен строго соответствовать порядку колонок в INSERT
    // и порядку значений в массиве payload.
    msg.topic = "INSERT INTO audit_log (ts, priority, source, event, value, unit, details) VALUES (?, ?, ?, ?, ?, ?, ?)";

    // 2. Формируем массив со значениями для вставки.
    // Если details не объект, преобразуем его в JSON-строку.
    const detailsJson = (typeof log.details === 'object' && log.details !== null) 
                      ? JSON.stringify(log.details) 
                      : null;

    // Преобразуем value в строку, чтобы избежать ошибок типов в БД.
    const valueStr = log.value !== null ? String(log.value) : null;


    msg.payload = [
        log.ts,
        log.priority,
        log.source,
        log.event,
        valueStr,
        log.unit,
        detailsJson
    ];

    return msg;

Нода `mysql: Запись в audit_log`

* В настройках ноды создайте и сконфигурируйте подключение к вашей базе данных MySQL (хост, порт, пользователь, пароль). Сохраняйте эти учетные данные в `credentials` Node-RED, а не в коде!

* Нода имеет два выхода: верхний для успешного выполнения, нижний — для ошибок. Подключите к нижнему выходу ноду `debug` или другой механизм оповещения об ошибках.

Теперь полный цикл от получения сообщения из MQTT до записи в БД завершен.

---

Итоги: Полный цикл жизни записи в журнале и лучшие практики

В этом уроке мы реализовали надежную и масштабируемую систему журналирования событий, пройдя полный путь от теории до практического внедрения на платформе HI. Давайте еще раз закрепим ключевые моменты.

Полный цикл жизни записи в журнале:

Генерация: Сценарий автоматизации (например, `SCN-LIGHT-012`) обнаруживает событие (включился свет).

Формирование: Сценарий вызывает сабфлоу `[SYS] MQTT Logger`, передавая ему `msg.topic = "SCN-LIGHT-012"` и `msg.payload = { event: "Свет включен" }`.

Обогащение и Публикация: Сабфлоу добавляет timestamp, priority, форматирует сообщение в стандартный JSON и публикует его в топик `hi/system/log/info/SCN-LIGHT-012`.

Прием: Флоу `[System] Log Collector` получает это сообщение через ноду `mqtt in`.

Валидация: Нода `switch` проверяет, что в сообщении есть все обязательные поля.

Подготовка к записи: Нода `function` формирует параметризованный SQL-запрос в `msg.topic` и массив значений в `msg.payload`.

Сохранение: Нода `mysql` безопасно выполняет запрос, и в таблице `audit_log` появляется новая строка с полной информацией о событии.

> 🔗 Связанный материал: Закрепите понимание структуры сообщений, перечитав урок о "контракте" для логов (COURSE-07-M08-L02). Это фундамент, на котором строится вся система.

Ключевые преимущества реализованного подхода

Асинхронность и Декаплинг: Источник лога не ждет, пока данные запишутся в БД, что исключает блокировку и "зависание" основных сценариев автоматизации.
Отказоустойчивость: Временная недоступность MySQL не влияет на работу остальной системы. MQTT выступает надежным буфером.
Стандартизация и Централизация: Все логи приводятся к единому формату и обрабатываются в одном месте. Это упрощает поддержку, отладку и будущие доработки.

Важные рекомендации

Безопасность: Всегда используйте `credentials` для хранения учетных данных от MQTT и MySQL. Никогда не "хардкодьте" пароли в коде `function` нод или настройках.
Ротация данных: Таблица с логами может быстро расти. Запланируйте механизм ротации или архивации старых записей (например, раз в месяц переносить данные в архивную таблицу `audit_log_archive` с помощью SQL-скрипта, запускаемого по расписанию через ноду `cron-plus`).
Анализ данных: Для удобного анализа создавайте в MySQL представления (views). Например, можно создать `view`, которое показывает только критические события за последние 24 часа.

### Что дальше

В следующих уроках мы научимся использовать собранные логи для построения информативных дашбордов, систем оповещения о нештатных ситуациях и анализа работы системы автоматизации для ее дальнейшей оптимизации. Мы превратим "сырые" данные из журнала в ценные знания о поведении вашего умного объекта.