mysql开启general log用于审计工作

1.适用场景

对于负载不高的mysql服务，可以开启general log用于审计，general log的开启不需要重启也不需要安装其他的工具。

2.general log日志的开启

查看是否已开启general 日志

mysql> show variables like '%general%';
+------------------+-------------------------------------------+
| Variable_name    | Value                                     |
+------------------+-------------------------------------------+
| general_log      | OFF                                       |
| general_log_file | /data/tmp/general_2022-02-17_10-37-34.log |
+------------------+-------------------------------------------+
2 rows in set (0.00 sec)

使用set global general_log=on开启general 日志

mysql> set global general_log=on;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%general%';
+------------------+-------------------------------------------+
| Variable_name    | Value                                     |
+------------------+-------------------------------------------+
| general_log      | ON                                        |
| general_log_file | /data/tmp/general_2022-02-17_10-37-34.log |
+------------------+-------------------------------------------+
2 rows in set (0.00 sec)

开启之后数据库所有运行的日志会记录在general_log_file中

general_log可以以库表形式或者以文件的形式记录，推荐以文件形式记录，相对于库表，对数据库的影响小，默认就是文件形式记录

mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

由于日志量巨大，需要定时对日志进行维护，下面是维护脚本下载链接

mysqlgenerallog处理脚本-MySQL文档类资源-CSDN下载

3.general log日志解读

[root@mysql-p tmp]# cat general_2022-02-17_10-37-34.log
/usr/local/mysql/bin/mysqld, Version: 8.0.27 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /data/mysql_data/mysql.sock
Time                 Id Command    Argument
2022-02-17T02:37:34.678514Z	   11 Quit	
2022-02-17T02:38:34.148358Z	   12 Connect	repl@10.9.40.7 on  using TCP/IP
2022-02-17T02:38:34.149002Z	   12 Query	SET NAMES utf8mb4
2022-02-17T02:38:34.207066Z	   12 Query	SHOW VARIABLES LIKE 'lower_case_%';
2022-02-17T02:38:34.209420Z	   12 Query	SHOW VARIABLES LIKE 'sql_mode';
2022-02-17T02:38:34.211305Z	   12 Query	SELECT COUNT(*) AS support_ndb FROM information_schema.ENGINES WHERE Engine = 'ndbcluster'
2022-02-17T02:38:34.221783Z	   12 Query	SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA
2022-02-17T02:39:56.795356Z	   13 Connect	root@localhost on  using Socket
2022-02-17T02:39:56.796012Z	   13 Query	select @@version_comment limit 1
2022-02-17T02:40:10.277651Z	   13 Query	CREATE USER 'test01'@'%' IDENTIFIED BY <secret>
2022-02-17T02:41:10.901378Z	   13 Query	grant all privileges on test.* to test01
2022-02-17T02:41:17.204514Z	   13 Query	flush privileges
2022-02-17T02:41:26.948826Z	   12 Quit	

time：时间戳

id：information_schema.PROCESSLIST的id字段

command：操作的类型

argument：具体的操作详情（repl@10.9.40.7 on  using TCP/IP表示用户从10.9.40.7使用relp账户登录）