☰

为什么要将general_log创建为表？

tag-icon • tag-icon mysql logging connection
为什么要将general_log创建为表？

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

我尝试使用常规日志记录，但它正在记录太多了我无法让它持续足够长的时间，日志文件增长得太快了。

有办法吗？谢谢

答案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 中讨论过很多次了。以下是我关于这个主题的一些帖子：

答案2

这是一个想法，但可能行不通。在一端
创建一个 FIFO 管道/var/log/mysql/named_pipe ，让 MySQL 使用它作为通用日志文件名。

在另一端，设置类似

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

您需要使用 grep 来从日志中找出您想要的那一行。

它可能工作.. 可能不行。

答案3

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

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

注意：如果用户以超级或 root 等权限连接，则不会捕获信息。

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

init_connect='CALL connectionlogging.usp_InsertUserConnection'

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

我使用这些信息来确定谁在使用加密连接、他们连接哪个数据库以及用户何时连接。

相关内容