MySQL--使用通用日志实现数据库审计

1 MySQL通用日志是什么？

MySQL通用日志记录用户的连接和断开连接的行为，记录从客户端收到的每一台sql语句。通用日志里记录的信息包括连接的用户名和IP地址，语句执行的时间。在某些场景下可以用于数据库审计。
MySQL通用日志的主要问题是没有过滤或者选择选项，记录的信息量过大，可能会对存储空间造成压力和io造成压力，对数据库的性能也有一定的影响。当怀疑数据库内运行的语句有问题时，可以短时开启，定位到具体的语句、用户和IP地址。

2 怎样MySQL通用日志的开启？

通过设置变量general-log来开启MySQL通用日志，general-log是动态变量，可以在数据库运行时开启。在数据库启动时用命令行开启的命令如下：

mysqld_safe --user=mysql --general-log=on  --datadir=/mysqldata &

开启后general-log变量值如下

mysql> show variables like '%general%';
+------------------+----------------------------------------+
| Variable_name    | Value                                  |
+------------------+----------------------------------------+
| general_log      | ON                                     |
| general_log_file | /mysqldata/iZ2ze0t8khaprrpfvmevjiZ.log |
+------------------+----------------------------------------+
2 rows in set (0.00 sec)

从general_log_file变量的值可以看到，产生的通用日志（也称为为通用查询日志）一般也称为查询日志。位于操作系统的文件内，也可以查询日志存储在MySQL数据库的表内，设置以下变量即可。

mysql> set global log_output='TABLE';
  Query OK, 0 rows affected (0.00 sec)

设置了这个变量值，慢查询日志也会存储到MySQL数据库的表内，查询日志存储在mysql数据库下名为general_log的表内，

mysql> show tables like '%general%';
+-----------------------------+
| Tables_in_mysql (%general%) |
+-----------------------------+
| general_log                 |
+-----------------------------+
1 row in set (0.01 sec)

3 怎样查看产生的慢查询日志

mysql数据库下general_log表的信息如下

mysql> desc general_log;
+--------------+---------------------+------+-----+----------------------+--------------------------------+
| Field        | Type                | Null | Key | Default              | Extra                          |
+--------------+---------------------+------+-----+----------------------+--------------------------------+
| event_time   | timestamp(6)        | NO   |     | CURRENT_TIMESTAMP(6) | on update CURRENT_TIMESTAMP(6) |
| user_host    | mediumtext          | NO   |     | NULL                 |                                |
| thread_id    | bigint(21) unsigned | NO   |     | NULL                 |                                |
| server_id    | int(10) unsigned    | NO   |     | NULL                 |                                |
| command_type | varchar(64)         | NO   |     | NULL                 |                                |
| argument     | mediumblob          | NO   |     | NULL                 |                                |
+--------------+---------------------+------+-----+----------------------+--------------------------------+
6 rows in set (0.00 sec)

查询这个表即可获得数据库的查询日志

mysql> select * from general_log limit 10;
+----------------------------+-------------------------------+-----------+-----------+--------------+-------------------------------------------------------------------------------------------------+
| event_time                 | user_host                     | thread_id | server_id | command_type | argument                                                                                        |
+----------------------------+-------------------------------+-----------+-----------+--------------+-------------------------------------------------------------------------------------------------+
| 2022-09-14 14:47:00.066478 | root[root] @ localhost []     |         3 |         0 | Query        | select * from general_log                                                                       |
| 2022-09-14 14:49:00.988472 | root[root] @ localhost []     |         3 |         0 | Query        | GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' IDENTIFIED WITH 'mysql_native_password' AS '<secret>' |
| 2022-09-14 14:49:18.585143 | root[root] @ localhost []     |         3 |         0 | Query        | flush privileges                                                                                |
| 2022-09-14 14:53:15.055589 | [test] @  [100.104.224.3]     |         4 |         0 | Connect      | test@100.104.224.3 on information_schema using TCP/IP                                           |
| 2022-09-14 14:53:15.057256 | test[test] @  [100.104.224.3] |         4 |         0 | Query        | SET autocommit=true                                                                             |
| 2022-09-14 14:53:15.060085 | test[test] @  [100.104.224.3] |         4 |         0 | Query        | /* rds internal mark */ /* hdm internal mark */ SELECT /*+ MAX_EXECUTION_TIME(2000) */ 1        |
| 2022-09-14 14:53:15.061626 | test[test] @  [100.104.224.3] |         4 |         0 | Quit         |                                                                                                 |
| 2022-09-14 14:53:19.873779 | [test] @  [100.104.224.3]     |         5 |         0 | Connect      | test@100.104.224.3 on information_schema using TCP/IP                                           |
| 2022-09-14 14:53:19.875503 | test[test] @  [100.104.224.3] |         5 |         0 | Query        | SET autocommit=true                                                                             |
| 2022-09-14 14:55:12.879961 | test[test] @  [100.104.224.3] |         5 |         0 | Query        | /* rds internal mark */ /* hdm internal mark */ SELECT /*+ MAX_EXECUTION_TIME(2000) */ 1        |
+----------------------------+-------------------------------+-----------+-----------+--------------+-------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

4 怎样清理通用日志？

由于通用日志的存储量非常大，需要定期清理，释放存储存储空间，最简单的清理办法是使用truncate命令清空表。
使用truncate命令清空表之前表占用的空间

mysql> system ls -l /mysqldata/mysql/general*
    -rw-r----- 1 mysql mysql    35 Sep 14 14:38 /mysqldata/mysql/general_log.CSM
    -rw-r----- 1 mysql mysql 10264 Sep 14 14:44 /mysqldata/mysql/general_log.CSV
    -rw-r----- 1 mysql mysql  8776 Aug  9 14:07 /mysqldata/mysql/general_log.frm

运行truncate命令

mysql> truncate table general_log;
    Query OK, 0 rows affected (0.00 sec)