Как временно логировать запросы к MySQL без перезапуска сервера: способ с использованием tcpdump

При работе с крупными серверами MySQL нередко возникает необходимость временного логирования запросов без влияния на функционирование самого сервера. Это особенно важно, если сервер имеет высокий уровень доступности, был настроен другим специалистом, а вам необходимо выполнить единовременную задачу. Стандартные способы, такие как активация query_log, могут требовать перезагрузки сервиса, что не всегда допустимо. В данной статье рассматривается альтернативный подход к логированию запросов с использованием tcpdump, позволяющий избежать остановки сервера и одновременно получать IP-адреса клиентов.

Почему стандартная методика не всегда применима

Стандартный механизм логирования запросов в MySQL использует general_log. Его можно активировать через конфигурационные файлы или непосредственно в консоли:

SET GLOBAL general_log_file = '/var/log/mysql/mysql.log';
SET GLOBAL general_log = 'ON';

У данного подхода есть свои недостатки:

1. Логирование в файл не фиксирует IP-адреса клиентов.
2. Для записи данных в таблицу mysql.general_log необходим рестарт сервера, что может оказаться неприемлемым для высокопроизводительных систем.
3. Логирование в файл или таблицу может создавать дополнительную нагрузку на сервер, особенно при большом количестве запросов.

Альтернатива: логирование с помощью tcpdump

Если требуется временное логирование запросов с возможностью получения IP-адресов клиентов, можно воспользоваться утилитой tcpdump. Она предназначена для перехвата и анализа сетевого трафика. Обратите внимание, что данный метод эффективен лишь для незашифрованных соединений с MySQL, что обычно характерно для внутренней инфраструктуры.

Шаг 1: Перехват трафика с помощью tcpdump

Сначала начнем перехват трафика на интерфейсе, через который происходит подключение к MySQL-серверу. Например, если интерфейс называется ens18, выполните следующую команду:

tcpdump -i ens18 port 3306 -s 1500 -w tcpdump.out

Где:

• -i ens18 – указывает интерфейс для перехвата.
• port 3306 – фильтрация трафика по порту MySQL.
• -s 1500 – устанавливает размер перехваченного пакета.
• -w tcpdump.out – сохранение перехваченного трафика в файл.

Теперь все поступающие запросы будут записываться в файл tcpdump.out в формате pcap.

Шаг 2: Анализ перехваченного трафика

По завершении процесса перехвата можно приступить к анализу содержимого файла tcpdump.out. Для этого воспользуемся утилитой tshark, входящей в состав пакета Wireshark.

Установите tshark, если она ещё не установлена:

apt install tshark

Затем выполните команду для извлечения SQL-запросов из перехваченного трафика:

tshark -r tcpdump.out -d tcp.port==3306,mysql -T fields -e mysql.query > query_log.out

Где:

• -r tcpdump.out – указывает файл для анализа.
• -d tcp.port==3306,mysql – интерпретирует трафик как MySQL.
• -T fields -e mysql.query – извлекает только SQL-запросы.
• > query_log.out – сохраняет результаты в файл.

После выполнения команды в файле query_log.out окажутся только SQL-запросы. Возможно наличие пустых строк, которые можно удалить с помощью sed:

sed -i '/^$/d' query_log.out

Шаг 3: Упрощённый способ логирования запросов

Если нет необходимости сохранять трафик в формате pcap, можно сразу преобразовывать его в текстовый вид и фильтровать SQL-запросы. Для этого используется комбинация команд tcpdump, stdbuf, strings и grep:

tcpdump -i ens18 -s 0 -U -w - dst port 3306 | stdbuf -i0 -o0 -e0 strings | grep -iE --line-buffered "(SELECT|UPDATE|INSERT|DELETE|SET|SHOW|COMMIT|ROLLBACK|CREATE|DROP|ALTER)"

Где:

• -s 0 – захватывает весь пакет.
• -U – выводит данные в режиме реального времени.
• stdbuf -i0 -o0 -e0 strings – конвертирует двоичные данные в текстовый формат.
• grep -iE – фильтрует строки по ключевым словам SQL.

Чтобы добавить временные метки, используйте awk:

tcpdump -i ens18 -s 0 -U -w - dst port 3306 | stdbuf -i0 -o0 -e0 strings | grep -iE --line-buffered "(SELECT|UPDATE|INSERT|DELETE|SET|SHOW|COMMIT|ROLLBACK|CREATE|DROP|ALTER)" | awk -W interactive '{print strftime("%F %T")" "$0}'

Шаг 4: Получение IP-адресов клиентов

Если требуется соотнести запросы с IP-адресами клиентов, задача становится сложнее. Можно вывести содержимое пакетов вместе с информацией о получателе:

tcpdump -i ens18 -s 0 -A -Q in port 3306

Однако сопоставить IP-адреса с запросами вручную будет непросто, поскольку между ними могут находиться двоичные данные. В таком случае лучше использовать файл pcap и анализировать его в Wireshark, где можно визуально видеть и запросы, и IP-адреса клиентов.

Заключение

Применение tcpdump для логирования запросов к MySQL является гибким методом, позволяющим обходиться без перезагрузки сервера и получать дополнительные сведения, такие как IP-адреса клиентов. Тем не менее, этот метод требует определённых навыков работы с сетевыми инструментами и подходит исключительно для незашифрованных соединений. Если перед вами стоит задача быстрого и простого логирования запросов, этот способ окажется весьма полезным.

Если же ваша цель – постоянное логирование с фиксацией IP-адресов, следует рассмотреть настройку mysql.general_log с последующей перезагрузкой сервера либо применение специализированных инструментов для мониторинга SQL-запросов.