MySQL -记录每个连接

Question

我想监视一个MySQL 5.0.77服务器，并记录每一个传入的连接一天。我需要知道谁在连接数据库。

我尝试了一般的日志记录，但是它的日志记录太多了，我不能保持足够长的时间，日志文件增长太快了。

有办法吗？谢谢

Answer 1

您应该使用常规日志，不是作为文本文件，而是作为一个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中已经讨论过很多次了。以下是我在这方面的一些帖子：

• Feb 11, 2012：MySQL通用日志
• Jan 07, 2012：如何启用MySQL通用日志？
• Dec 27, 2011：如何做MySQL用户计费
• Feb 24, 2011：对MySQL数据库的审计登录

Answer 2

这是个主意，但可能行不通。

在/var/log/mysql/named_pipe的一端创建一个FIFO管道，让MySQL使用它作为一般的日志文件文件名。

在另一端，设置这样的东西

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

无论什么行，您都需要grep，这正是您想要退出日志的原因。

可能有用..。可能不会。

Answer 3

我也需要做同样的事。在MySQL 5.6和MySQL 5.7中，有一种方法可以捕获所需的信息并将其存储在表中。我不确定在MySQL 5.0或5.5中这是可能的。以下是我所做的：

1. 创建了一个包含所需表的数据库。
2. 创建所需的表
3. 创建了一个过程来捕获信息并插入到表中。
4. 修改了MySQL配置文件，以便所有连接都执行存储过程
5. 赋予所有用户在存储过程上的执行权限。

注意:如果用户使用超级或根类特权连接，这将不会捕获信息。

在MySQL配置文件的mysqld部分，我在下面添加了：# ***参数记录用户连接

init_connect='CALL connectionlogging.usp_InsertUserConnection'

我需要捕获的信息是userid、连接请求的宿主、用户连接到的数据库、有关连接的信息以及时间戳。

我使用这些信息来确定谁在使用加密连接，他们连接到什么数据库(S)，以及用户何时连接。