mysql 如何开启审计日志

1. 首先确认有审计库：(宝塔默认编译)

mysql> show variables like 'plugin_dir';
+---------------+-------------------------------+
| Variable_name | Value                         |
+---------------+-------------------------------+
| plugin_dir    | /www/server/mysql/lib/plugin/ |
+---------------+-------------------------------+

ls /www/server/mysql/lib/plugin/
-rwxr-xr-x. 1 root mysql 201784 Mar 27  2020 server_audit.so

2. 开启审计插件。(必须要mysql命令行)

mysql> INSTALL PLUGIN server_audit SONAME 'server_audit.so';
Query OK, 0 rows affected (0.00 sec)

mysql> show PLUGINS;
+----------------------------+----------+--------------------+-----------------+---------+
| Name         | Status | Type  | Library         | License |
+----------------------------+----------+--------------------+-----------------+---------+
| SERVER_AUDIT | ACTIVE | AUDIT | server_audit.so | GPL |

3. 审计配置, 通过数据库查询，如果要永久生效，需要编辑配置文件

查看默认配置：

show variables like '%audit%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| server_audit_events           |                       |
| server_audit_excl_users       |                       |
| server_audit_file_path        | server_audit.log      |
| server_audit_file_rotate_now  | OFF                   |
| server_audit_file_rotate_size | 1000000               |
| server_audit_file_rotations   | 9                     |
| server_audit_incl_users       |                       |
| server_audit_logging          | OFF                   |
| server_audit_mode             | 0                     |
| server_audit_output_type      | file                  |
| server_audit_query_log_limit  | 1024                  |
| server_audit_syslog_facility  | LOG_USER              |
| server_audit_syslog_ident     | mysql-server_auditing |
| server_audit_syslog_info      |                       |
| server_audit_syslog_priority  | LOG_INFO              |
+-------------------------------+-----------------------+

4. 修改mysql配置文件，开启审计，写入配置文件

vi /etc/my.cnf

### 审计 ###
server_audit_logging = ON                 #开启日志记录，默认是关闭
server_audit = FORCE_PLUS_PERMANENT       #防止插件被卸载
server_audit_file_path = server_audit.log #定义审计日志文件名
server_audit_file_rotate_now = ON         #是否强制切割审计日志
server_audit_file_rotate_size = 10485760  #切割审计日志的文件大小(B)
server_audit_file_rotations = 99           #定义审计日志的轮询个数，0为不轮询
#server_audit_incl_users = user
#server_audit_excl_users = root

注：默认配置文件： /www/server/data/server_audit.log

5. 审计日志解析

一行代表一条完成的审计日志，日志中的字段用逗号分隔。各种事件将产生不同的审计记录，这些记录的格式个各不相同。根据审计事件，主要分为3类：
-- 连接审计:主要审计连接数据库、断开连接、连接失败等操作，其日志格式如下：

[timestamp],[serverhost],[username],[host],[connectionid],0,CONNECT,[database],,0
[timestamp],[serverhost],[username],[host],[connectionid],0,DISCONNECT,,,0
[timestamp],[serverhost],[username],[host],[connectionid],0,FAILED_CONNECT,,,[retcode]

-- 查询审计: 即审计select语句，其日志格式如下:

[timestamp],[serverhost],[username],[host],[connectionid],[queryid],QUERY,[database],[object], [retcode]

-- 表相关审计: 如果审计开启TABLE事件，则会审计创建、删除、重命名表等操作，日志格式如下：

[timestamp],[serverhost],[username],[host],[connectionid],[queryid],CREATE,[database],[object],
[timestamp],[serverhost],[username],[host],[connectionid],[queryid],READ,[database],[object],
[timestamp],[serverhost],[username],[host],[connectionid],[queryid],WRITE,[database],[object],
[timestamp],[serverhost],[username],[host],[connectionid],[queryid],ALTER,[database],[object],
[timestamp],[serverhost],[username],[host],[connectionid],[queryid],RENAME,[database], [object_old]|[database_new].[object_new],
[timestamp],[serverhost],[username],[host],[connectionid],[queryid],DROP,[database],[object],