mysql利用audit审计日志
大体来说除了商业版的审计插件外，还有三类审计插件可以考虑：
- Percona 5.6.17首次提供的审计插件audit log
- MariaDB的审计插件server audit
- Mcafee的审计插件mysql-audit，已经在github上开源
[TOC]
Percona审计插件audit_log
安装插件
- 确认插件目录
mysql> SHOW GLOBAL VARIABLES LIKE 'plugin_dir';
- 确认插件文件（自带的）
ll /opt/mysql/lib/plugin/audit_log.so
- 安装插件
mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so';
- 确认审计功能已开启
mysql> show variables like 'audit%';
+-----------------------------+---------------+
| Variable_name | Value |
+-----------------------------+---------------+
| audit_log_buffer_size | 1048576 | #.指定审计日志的内存buffer
| audit_log_exclude_accounts | |
| audit_log_exclude_commands | |
| audit_log_exclude_databases | |
| audit_log_file | audit.log | #.审计日志文件，默认路径参考 show variables like 'datadir';
| audit_log_flush | OFF |
| audit_log_format | OLD | #.指定审计日志格式：OLD(xml属性)、NEW(xml标签)、JSON、CSV
| audit_log_handler | FILE |
| audit_log_include_accounts | |
| audit_log_include_commands | |
| audit_log_include_databases | testdb | #.筛选包含xxx的库
| audit_log_policy | ALL | #.核心参数：ALL(记录所有)、LOGIN(记录登录登出信息)、QUERY(只记录查询事件)、NONE(不记录任何事件)
| audit_log_rotate_on_size | 524288000 | #.单个轮训切分日志的大小，建议500MB
| audit_log_rotations | 10 | #.轮询切分日志的数量
| audit_log_strategy | ASYNCHRONOUS |
| audit_log_syslog_facility | LOG_USER |
| audit_log_syslog_ident | percona-audit |
| audit_log_syslog_priority | LOG_INFO |
+-----------------------------+---------------+
启用插件
方式一：在线启用插件
mysql> SET GLOBAL audit_log_policy=ALL;
方式二：配置文件加载插件，重启服务后永久生效
[mysqld]
plugin-load=audit_log.so
########################## audit ##########################
audit_log_policy=ALL
audit_log_format=JSON
audit_log_file=/data/mysql_3406/log/audit.log
audit_log_rotate_on_size=500M
audit_log_rotations=10
筛选日志
- 筛选日志：取值逗号分隔，如果设置include或者exclude，则另一个变量不能设置任何值
mysql> SET GLOBAL audit_log_include_accounts = 'user_test@%'; #.包含xxx用户
mysql> SET GLOBAL audit_log_exclude_accounts = 'root@localhost'; #.排除xxx用户
mysql> SET GLOBAL audit_log_include_databases = 'test,mysql,db1'; #.包含xxx库
mysql> SET GLOBAL audit_log_exclude_databases = NULL; #.排除xxx库
mysql> SET GLOBAL audit_log_include_tables = 't1,test'; #.包含xxx表
mysql> SET GLOBAL audit_log_exclude_tables = NULL; #.排除xxx表
mysql> SET GLOBAL audit_log_include_commands = NULL; #.包含xxx命令类型
mysql> SET GLOBAL audit_log_exclude_commands= 'set_option,create_db'; #.排除xxx命令类型
mysql> SELECT name FROM performance_schema.setup_instruments WHERE name LIKE "statement/sql/%" ORDER BY name;
- 无效的参数
# SET GLOBAL audit_log_format=JSON; #.只读变量,无法修改
# SET GLOBAL audit_log_file=/data/mysql_3406/log/audit.log; #.语法错误
# SET GLOBAL audit_log_include_tables = 'student'; #.未知变量,无效了
停用插件
- 停用审计及卸载审计插件
mysql> SET GLOBAL audit_log_policy=NONE;
mysql> UNINSTALL PLUGIN audit_log;
审计场景实践
场景1：2020.09.07.审计xxx库的数据变更（insert、update、delete），验证ok。
SET GLOBAL audit_log_policy=QUERIES;
SET GLOBAL audit_log_include_accounts = NULL;
SET GLOBAL audit_log_rotate_on_size=500*1024*1024;
SET GLOBAL audit_log_rotations=10;
SET GLOBAL audit_log_include_databases = NULL;
SET GLOBAL audit_log_exclude_databases = NULL;
SET GLOBAL audit_log_include_databases = 'kidstools_tenant';
SET GLOBAL audit_log_include_commands='insert,update,delete';
MariaDB审计插件server_audit
安装插件
- mysql社区版没有审计插件，可以在测试机器上安装mariadb，再找出server_audit.so包。也可以直接去mariadb官网下载一个二进制包找。
find / -name server_audit.so
# wget -c http://iso.sqlfans.cn/mysql/plugin/server_audit.so -P /opt
- 确认插件目录
mysql> SHOW GLOBAL VARIABLES LIKE 'plugin_dir';
- 将 server_audit.so 复制到mysql插件的路径下
cp /opt/server_audit.so /opt/mysql/lib/plugin/
- 安装插件
mysql> INSTALL PLUGIN server_audit SONAME 'server_audit.so';
- 确认审计功能已开启
mysql> show variables like 'server_audit%';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| server_audit_events | QUERY_DML_NO_SELECT | #.审计事件类型
| server_audit_excl_users | |
| server_audit_file_path | audit.log | #.审计日志文件，默认路径参考 show variables like 'datadir';
| server_audit_file_rotate_now | OFF | #.on表示强制进行日志轮替
| server_audit_file_rotate_size | 524288000 | #.设置server_audit.log大小达到限制后进行轮替，建议500MB
| server_audit_file_rotations | 10 | #.日志文件数量
| server_audit_incl_users | |
| server_audit_loc_info | |
| server_audit_logging | ON | #.核心参数：ON(已开启审计)、OFF(未开启审计)
| server_audit_mode | 1 |
| 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 |
+-------------------------------+-----------------------+
server_audit_events，审计事件类型包括：
- CONNECT：连接、断开连接和失败的连接，包括错误代码
- QUERY：以纯文本形式执行的查询及其结果，包括由于语法或权限错误而失败的查询
- TABLE：受查询执行影响的表
- QUERY_DDL：与QUERY相同，但只筛选DDL类型的查询（create、alter、drop、rename和truncate语句，create/drop[procedure/function/user]和rename user除外（它们不是DDL）
- QUERY_DML：与QUERY相同，但只筛选DML类型的查询（do、call、load data/xml、delete、insert、select、update、handler和replace语句）
- QUERY_DCL：与QUERY相同，但只筛选DCL类型的查询（create user、drop user、rename user、grant、revoke和set password语句）
- QUERY_DML_NO_SELECT：与QUERY_DML相同，但不记录SELECT查询（do、call、load data/xml、delete、insert、update、handler和replace语句）
启用插件
方式一：在线启用插件
mysql> SET GLOBAL server_audit_logging=on;
方式二：配置文件加载插件，重启服务后永久生效
[mysqld]
########################## Mariadb Audit ##########################
plugin_load = server_audit=server_audit.so #载入审计插件
server_audit = FORCE_PLUS_PERMANENT #防止插件被卸载
server_audit_file_path = audit.log #定义审计日志文件名
server_audit_file_rotate_now = OFF #是否强制切割审计日志
server_audit_file_rotate_size = 524288000 #定义切割审计日志的文件大小500M
server_audit_file_rotations = 10 #定义审计日志的轮询个数，0为不轮询
server_audit_logging = ON #开启日志记录
server_audit_output_type = file #指定日志输出类型，可为SYSLOG或FILE
server_audit_query_log_limit = 1024
server_audit_syslog_facility = LOG_USER
server_audit_syslog_ident = mysql-server_auditing
server_audit_syslog_priority = LOG_INFO
停用插件
mysql> SET GLOBAL server_audit_logging='OFF';
审计场景实践
场景1：2020.09.07.审计xxx库的数据变更（insert、update、delete），验证ok。
SET GLOBAL server_audit_file_path='/data/mysql_3306/log/audit.log';
SET GLOBAL server_audit_file_rotate_size=500*1024*1024;
SET GLOBAL server_audit_file_rotations=10;
SET GLOBAL server_audit_file_rotate_now=on;
SET GLOBAL server_audit_events='QUERY_DML_NO_SELECT';
SET GLOBAL server_audit_logging=on;
建议：若要审计确认xx表为何被修改，建议再添加如下2个字段，以配合确认。
ALTER TABLE kidstools_tenant.student
ADD COLUMN create_time timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '插入时间',
ADD COLUMN update_time timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间';
再确认下是否有event、外键、触发器
show variables like 'event_scheduler';
select * from information_schema.events where event_schema not in ('mysql','information_schema','performance_schema','sys') limit 10;
select * from information_schema.triggers where trigger_schema not in ('mysql','information_schema','performance_schema','sys') limit 10;
select * from information_schema.table_constraints where constraint_type = 'FOREIGN KEY' and table_name = 'student' limit 10;
分析结果：查看对 【xx库.xx表.xx字段】 的 【增、删、改】 操作记录
cat /data/mysql_3306/log/audit.log* | grep kidstools_tenant | grep student | grep school_id | grep -v parent | more
结合binlog分析：
mysqlbinlog --no-defaults -vv --base64-output=decode-rows --skip-gtids=true --start-datetime='2020-09-14 05:00:00' -d kidstools_tenant /data/mysql_3306/binlog/mysql-bin.04320* | more
遇到的坑：不要根据某一条被修改的记录（容易被误导）去定位审计日志，也许是 update left join set 批量修改的，比如：
20200922 06:01:02,u-7361-mysql,web_pub,192.168.10.174,2695773,222032218,QUERY,kidstools_attendance,'update student_leave_day_record r left join kidstools_tenant.student s on s.id=r.student_id set s.class_id=r.class_id , s.school_id=r.class_id , r.`status`=s.`status` where r.record_date=\'2021-09-22\' and (s.class_id<>r.class_id or s.school_id<>r.class_id or r.`status`<>s.`status`)',0