MySQL - registra todas as conexões

Question

Eu gostaria de monitorar um servidor MySQL 5.0.77 e registrar todas as conexões de entrada por um dia. Eu preciso saber quem está se conectando ao banco de dados.

Eu tentei com o registro geral, mas ele está registrando demais e não consigo mantê-lo ligado o tempo suficiente, o arquivo de log está crescendo muito rápido.

Existe uma maneira de fazer isso? Obrigado

    

Answer 1

Você deve usar o log geral, não como um arquivo de texto, mas como uma tabela MySQL.

Por que criar o general_log como uma tabela?

• Você pode executar o SQL nele.
• Você pode girar com muita facilidade.

Como você configura?

A partir do MySQL 5.1, a seguinte opção foi introduzida: log-output .

• Configurar log-output para FILE (padrão) grava entradas de log no arquivo de texto.
• Definir log-output para TABLE grava entradas de log em mysql.general_log .
• A configuração de log-output para TABLE,FILE grava entradas de log em mysql.general_log e no arquivo de texto.

Há uma coisa importante que deve ser mudada.

Vamos ver uma tabela padrão do 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) 

É um arquivo CSV? Que nojo !!! Quem tem tempo para isso? Não se preocupe, transforme-o em uma tabela MyISAM

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

Se você já iniciou o MySQL, faça assim:

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; 

Agora, a tabela de registro geral se parece com isso:

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)

Como você gira o log geral quando é uma tabela MyISAM?

Aqui está um exemplo de como eliminar 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; 

Aqui está um exemplo de como manter os últimos 3 dias de entradas:

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; 

CAVEAT

Esta funcionalidade está disponível no MySQL 5.1 / 5,5 / 5.6. Você deve atualizar do MySQL 5.0.77 para o mais recente

EPÍLOGO

Eu poderia escrever muito mais aqui, mas já discuti isso várias vezes no DBA StackExchange. aqui estão algumas das minhas postagens sobre este assunto:

• Feb 11, 2012 : MySQL general log
• Jan 07, 2012 : How to enable MySQL general log?
• Dec 27, 2011 : How to do MySQL User Accounting
• Feb 24, 2011 : Audit logins on MySQL database

Answer 2

Aqui está uma ideia, mas pode não funcionar.
Crie um pipe FIFO em /var/log/mysql/named_pipe Em uma extremidade, obtenha o MySQL para usá-lo como o nome do arquivo de arquivo de log geral.

No outro lado, configure algo como

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

Você precisará do grep para qualquer linha que esteja fora do log.

pode funcionar ... talvez não.

    

Answer 3

Eu precisava fazer a mesma coisa. No MySQL 5.6 e no MySQL 5.7, existe uma maneira de capturar as informações necessárias e armazená-las em uma tabela. Não tenho certeza se isso é possível com o MySQL 5.0 ou 5.5. Aqui está o que eu fiz:

1. Criamos um banco de dados para conter as tabelas necessárias.
2. Criado as tabelas necessárias
3. Criado um procedimento para capturar as informações e inserir nas tabelas
4. Modificado o arquivo de configuração do MySQL para que todas as conexões executem o procedimento armazenado
5. Deu a todos os usuários privilégios de execução no procedimento armazenado.

Nota: Isso não capturará informações se um usuário se conectar com privilégios de super ou root.

Na seção mysqld do arquivo de configuração do MySQL, eu adicionei: # *** O parâmetro abaixo registra as conexões do usuário

init_connect='CALL connectionlogging.usp_InsertUserConnection'

As informações que eu preciso capturar são userid, host da qual a solicitação de conexão está sendo feita, o banco de dados ao qual o usuário está se conectando, informações sobre a conexão e um registro de data e hora.

Eu uso essas informações para determinar quem está usando uma conexão criptografada, a quais bancos de dados eles estão se conectando e quando o usuário se conecta.