Логирование запросов к MySQL при помощи tcpdump

• Проблема
• Варианты сбора статистики
• Условия задачи
• Решение
• Буферизация

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

Инциденты на продакшене это финансовые и репутационные потери, а возможно даже компенсации клиентам.

#Проблема

Рассматривая вопрос ограничения, разработчики поделились на 2 лагеря:

• те кто готов сдать доступ и действовать через новую централизованную систему миграций, невзирая на усложнение операций
• те кто был против, аргументируя острой необходимостью всегда иметь под рукой доступ на запись в БД

Вопросы частичного ограничения или "зачем разработчикам доступ в БД?" выходят за рамки статьи, потому что очень много зависит от ситуации, размера компании, устоявшихся процессов и персон участвующих в процессе.

Перед началом конкретных действий, хочется собрать статистику частоты запросов в БД на запись.

#Варианты сбора статистики

Есть встроенное в MySQL средство query_log. Однако, продовая БД для нас своего рода "Дом Господень", в котором даже верховный админ не позволяет себе лишний раз трогать сервис mysql.

Работа с MySQL сервером осуществляется только во внутренней VPN сети, приложения расположенные на серверах имеют статичные IP адреса, а у разработчиков динамический диапазон, заранее известный.

Взаимодействие с MySQL происходит по tcp, а у нас есть tcpdump (интересное руководство по tcpdump). Немного погуглив можно найти разные варианты, например вот, здесь и там.

Стоит заметить, что при использовании tcpdump мы не получим логин или session id пользователя, можно конечно поизвращаться с протоколом MySQL, однако, затея дурная.

#Условия задачи

• наши пользователи ходят в БД через VPN, а значит мы можем отфильтровать трафик по заранее подготовленным IP адресам
• нас интересуют только запросы, а значит только входящий трафик
• нам нужен только тип запроса (SELECT, INSERT, UPDATE ...), остальные детали запроса неважны
• можно еще время прикрутить

#Решение

В самом простом варианте выводим входящие пакеты на порт 3306:

$ sudo tcpdump -Q in -n -s 0 -A port 3306
22:52:25.555459 IP 192.168.0.105.42496 > 192.168.0.110.3306: Flags [P.], seq 45:139, ack 94, win 501, options [nop,nop,TS val 382606596 ecr 3615477414], length 94
E...*.@.@......i...n...........:...........
........Z....../* ApplicationName=DBeaver 23.3.3 - Main */ SELECT x.* FROM users x
LIMIT 0, 200

Уже хорошо, у нас есть IP адрес клиента и сам запрос - то что нам нужно. Теперь можно отфильтровать запросы и оставить только запросы от VPN + выводить только тип запроса. Еще одним пожеланием было выводить время запроса.

В итоге кропотливой работы, наш админ написал такой скрипт:

$ sudo tcpdump -i eth0 -s 0 -U -w - '(src host ip_vpn1 or src host ip_vpn2) and (dst port 3306)' \
| stdbuf -i0 -o0 -e0 strings \
| grep -oE --line-buffered "(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)" \
| awk -W interactive '{print strftime("%Y-%m-%d %T")" "$0}'

В данном случае strings используется для преобразования бинарных данных в текстовый формат чтобы grep мог производить поиск нужных слов, а awk для добавления времени в вывод.

Вывод будет такой:

2024-01-31 10:47:09 SELECT
2024-01-31 10:50:10 INSERT
2024-01-31 10:51:11 SELECT
2024-01-31 10:51:12 UPDATE
2024-01-31 10:51:15 SELECT
2024-01-31 10:55:15 DELETE
2024-01-31 10:57:15 SELECT

Выводим все это дело в файл >> myfile.txt и собираем статистику.

А если хочется увидеть весь запрос тогда нужно вместо grep -oE написать grep -E:

$ sudo tcpdump -s 0 -U -w - '(src host ip_vpn1 or src host ip_vpn2) and (dst port 3306)' \
| stdbuf -i0 -o0 -e0 strings \
| grep -E --line-buffered "(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)" \
| awk -W interactive '{print strftime("%Y-%m-%d %T")" "$0}'

2024-02-01 23:17:08 /* ApplicationName=DBeaver 23.3.3 - Main */ SELECT x.* FROM users x
2024-02-01 23:17:22 /* ApplicationName=DBeaver 23.3.3 - Main */ SELECT x.* FROM users x

#Буферизация

При буферазции каждая промежуточная команда, на пути к stdout, будет буферизировать/копить данные до определенного момента, и только потом выведет результат.

Поэтому при тестировании могут возникнуть проблемы, когда tcp пакеты есть, а вывода tcpdump нет, и нужно чтобы пакетов было больше, прежде чем появится хоть какой-то вывод.

Для решения этой проблемы нужно отключать буферизацию на каждом этапе:

• tcpdump -U
• stdbuf -o0 -i0 для strings (ссылка)
• grep --line-buffered (ссылка)
• awk -W interactive

В предыдущей главе это уже все есть, поэтому можно смело копипастить :)