MySQL: Логирование запросов от определенных пользователей

Появилась необходимость логировать запросы к БД MySQL от определенных пользователей. Давайте разберемся как быстро реализовать данный функционал!

Самый простой и очевидный способ — просто включить general_log (лог ВСЕХ запросов — содержит информацию о подключениях и отключениях клиентов, и о всех SQL выполненных запросах). Сделать это можно с помощью следующего запроса:

SET GLOBAL general_log = 'ON';

Проблема состоит в том, что в лог попадает очень большое количество информации (а в высоконагруженных проектах вообще так делать не рекомендуется), лог быстро разрастается, и, чтобы не закончилось место на диске, приходится настраивать ротацию. Кроме того, анализ такого лога усложняется, и тут не обойтись без хитроумных конструкций и конвейеров с использованием sed/grep/awk…

В моем случае не нужно логировать запросы от пользователей root и prometheus (от имени второго выполняется мониторинг и сбор метрик — подробнее здесь и здесь).

Итак, приступим! Подключаемся к MySQL-серверу и указываем писать все логи в таблицы (а не в файлы, как было до этого):

SET GLOBAL log_output = 'TABLE';

Выключаем лог всех запросов (если включен) и изменяем движок (Engine) таблицы general_log на MyISAM:

SET GLOBAL general_log = 'OFF';
ALTER TABLE mysql.general_log ENGINE = MyISAM;

Включаем лог всех запросов:

SET GLOBAL general_log = 'ON';

Как я уже говорил, в таблицу general_log будет попадать очень много информации, убедиться в этом можно с помощью запроса:

SELECT * FROM mysql.general_log;

Для «фильтрации» и хранения только нужных данных нам потребуется еще одна таблица с аналогичной структурой. Смотрим запрос, с помощью которого можно будет ее создать:

SHOW CREATE TABLE mysql.general_log;

Создаем таблицу с именем custom_user_log по запросу из предыдущего шага (запрос копируем полностью меняем только имя и комментарий):

USE mysql;
CREATE TABLE `custom_user_log` (
  `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `user_host` mediumtext NOT NULL,
  `thread_id` bigint(21) unsigned NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumblob NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Custom user query log';

Теперь можем выбрать нужным нам записи из таблицы general_log и вставить в таблицу custom_user_log:

INSERT INTO mysql.custom_user_log SELECT * FROM mysql.general_log WHERE user_host NOT LIKE '%root%' AND user_host NOT LIKE '%prometheus%';

Таблицу general_log можно очистить:

TRUNCATE mysql.general_log;

Конечно же два последних шага лучше оформить в bash-скрипт, который будет выполняться по крону (cron) с заданной периодичностью. В моем случае скрипт выглядит так:

#!/bin/bash
#
# Скрипт копирует MySQL-запросы всех пользователей кроме root и prometheus
# в отдельную таблицу mysql.custom_user_log и чистит mysql.general_log
#
 
MYADMIN="/usr/bin/mysqladmin --defaults-file=/root/.my.cnf"
ping_output=`$MYADMIN ping 2>&1`; ping_alive=$(( ! $? ))
if [ $ping_alive = 0 ]; then
    exit
fi
 
OPTIONS=`cat /root/.mypass`
 
mysql ${OPTIONS} <<MY_QUERY
USE mysql;
INSERT INTO custom_user_log SELECT * FROM general_log WHERE user_host NOT LIKE '%root%' AND user_host NOT LIKE '%prometheus%';
TRUNCATE general_log;
MY_QUERY