Назад | Перейти на главную страницу

MySQL - регистрировать все подключения

Я хотел бы отслеживать сервер MySQL 5.0.77 и регистрировать каждое входящее соединение в течение дня. Мне нужно знать, кто подключается к базе данных.

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

Есть ли способ сделать это ? Спасибо

Вы должны использовать общий журнал не как текстовый файл, а как таблицу MySQL.

Зачем создавать general_log в виде таблицы?

  • Вы можете запустить SQL против него.
  • Вы можете легко повернуть.

Как его настроить?

Начиная с MySQL 5.1, была представлена ​​следующая опция: лог-вывод.

  • Настройка log-output к FILE (по умолчанию) записывает записи журнала в текстовый файл.
  • Настройка log-output к TABLE записывает записи журнала в mysql.general_log.
  • Настройка log-output к TABLE,FILE записывает записи журнала в mysql.general_log и текстовый файл.

Необходимо изменить одну важную вещь.

Давайте посмотрим на стандартную таблицу mysql.general_log:

mysql> show create table mysql.general_log\G 
*************************** 1. row *************************** 
       Table: general_log 
Create Table: CREATE TABLE `general_log` ( 
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
      ON UPDATE CURRENT_TIMESTAMP, 
  `user_host` mediumtext NOT NULL, 
  `thread_id` int(11) NOT NULL, 
  `server_id` int(10) unsigned NOT NULL, 
  `command_type` varchar(64) NOT NULL, 
  `argument` mediumtext NOT NULL 
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log' 
1 row in set (0.09 sec)

Это файл CSV? Фу !!! У кого есть на это время? Не волнуйтесь, превратите его в таблицу MyISAM

ALTER TABLE mysql.general_log ENGINE = MyISAM; 
ALTER TABLE mysql.general_log ADD INDEX (event_time);

Если вы уже запустили MySQL, сделайте это так:

SET @old_log_state = @@global.general_log; 
SET GLOBAL general_log = 'OFF'; 
ALTER TABLE mysql.general_log ENGINE = MyISAM; 
ALTER TABLE mysql.general_log ADD INDEX (event_time); 
SET GLOBAL general_log = @old_log_state;

Теперь общая таблица журнала выглядит так:

mysql> show create table general_log\G
*************************** 1. row ***************************
       Table: general_log
Create Table: CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
      ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL,
  KEY `event_time` (`event_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='General log'
1 row in set (0.00 sec)

Как повернуть общий журнал, если это таблица MyISAM?

Вот пример того, как отключить mysql.general_log:

SET @old_log_state = @@global.general_log; 
SET GLOBAL general_log = 'OFF'; 
CREATE TABLE mysql.general_log_new LIKE mysql.general_log; 
DROP TABLE mysql.general_log; 
ALTER TABLE mysql.general_log_new RENAME mysql.general_log; 
SET GLOBAL general_log = @old_log_state;

Вот пример того, как сохранить записи за последние 3 дня:

SET @old_log_state = @@global.general_log; 
SET GLOBAL general_log = 'OFF'; 
CREATE TABLE mysql.general_log_new LIKE mysql.general_log; 
INSERT INTO mysql.general_log_new 
SELECT * FROM mysql.general_log WHERE event_time > NOW() - INTERVAL 3 DAY; 
DROP TABLE mysql.general_log; 
ALTER TABLE mysql.general_log_new RENAME mysql.general_log; 
SET GLOBAL general_log = @old_log_state;

ПРЕДОСТЕРЕЖЕНИЕ

Эта функция доступна в MySQL 5.1 / 5,5 / 5.6. Вам следует обновить MySQL 5.0.77 до последней версии.

Эпилог

Я мог бы написать здесь намного больше, но уже много раз обсуждал это в DBA StackExchange. вот несколько моих сообщений на эту тему:

Мне нужно было сделать то же самое. В MySQL 5.6 и MySQL 5.7 есть способ получить необходимую информацию и сохранить ее в таблице. Я не уверен, возможно ли это с MySQL 5.0 или 5.5. Вот что я сделал:

  1. Создал базу данных для хранения необходимых таблиц.
  2. Созданы необходимые таблицы
  3. Создал процедуру для сбора информации и вставки в таблицы
  4. Изменен файл конфигурации MySQL, чтобы все соединения выполняли хранимую процедуру.
  5. Всем пользователям предоставлено право на выполнение хранимой процедуры.

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

В разделе mysqld конфигурационного файла MySQL я добавил: # *** Параметр ниже регистрирует подключения пользователей

init_connect='CALL connectionlogging.usp_InsertUserConnection'

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

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

Вот идея, но она может не сработать.
Создайте канал FIFO в /var/log/mysql/named_pipe С одной стороны, заставьте MySQL использовать его как имя файла общего журнала.

С другой стороны, настройте что-то вроде

grep "login" < /var/log/mysql/named_pipe > /var/log/mysql/logins

Вам нужно будет найти любую строку, которую вы ищете, вне журнала.

Это мощь работать .. может и нет.