mysql5.7的企业版自带审计功能
社区版可以使用开源版mysql Audit Pluging
这里使用mcafee的
生产环境不建议启用审计功能
github地址
[Release v1.1.6 · trellix-enterprise/mysql-audit (github.com)](https://github.com/trellix-enterprise/mysql-audit/releases/tag/v1.1.6)
1.下载与解压
wget https://github.com/trellix-enterprise/mysql-audit/releases/download/v1.1.6/audit-plugin-mysql-5.7-1.1.6-784-linux-x86_64.zip
unzip audit-plugin-mysql-5.7-1.1.6-784-linux-x86_64.zip
2.查看插件存放位置，并将其移动到该位置
show global variables like 'plugin_dir';
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| plugin_dir | /usr/local/mysql/lib/plugin/ |
+---------------+------------------------------+
cd /root/audit-plugin-mysql-5.7-1.1.6-784/lib
需要注意权限
chmod +x libaudit_plugin.so
chown mysql:mysql libaudit_plugin.so
cp -a libaudit_plugin.so /usr/local/mysql/lib/plugin/
3.安装插件
mysql -uroot -p
install plugin audit soname 'libaudit_plugin.so';
flush privileges;
报错McAfee Audit Plugin: Couldn't find proper THD offsets for: 5.7.44-log
原因：我使用的是5.7.44最新的补丁版，版本太新
解决方法：使用官网的脚本提取偏移量
参考资料：[Troubleshooting · trellix-enterprise/mysql-audit Wiki (github.com)](https://github.com/trellix-enterprise/mysql-audit/wiki/Troubleshooting)
wget https://raw.github.com/mcafee/mysql-audit/master/offset-extract/offset-extract.sh
chmod +x offset-extract.sh
yum -y install gdb
./offset-extract.sh /usr/sbin/mysqld
//offsets for: /usr/sbin/mysqld (5.7.44)
{"5.7.44","69317d5de2fd9a7a95b58a4ce2be0bd1", 7840, 7888, 3648, 4808, 456, 360, 0, 32, 64, 160, 544, 8004, 4376, 3656, 3664, 3668, 6088, 2072, 8, 7072, 7112, 7096, 13488, 148, 672, 0},
将这一部分偏移量写入到my.cnf的audit_offsets字段
[mysqld]
audit_offsets=7840, 7888, 3648, 4808, 456, 360, 0, 32, 64, 160, 544, 8004, 4376, 3656, 3664, 3668, 6088, 2072, 8, 7072, 7112, 7096, 13488, 148, 672, 0
最后还是不行，我用的5.7.44太新了，最后换5.7.20版本成功了
4.安装验证
show global status like 'audit_version';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| Audit_version | 1.1.6-784 |
+---------------+-----------+
show global variables like '%audit_json_file%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| audit_json_file | OFF |
| audit_json_file_bufsize | 1 |
| audit_json_file_flush | OFF |
| audit_json_file_retry | 60 |
| audit_json_file_sync | 0 |
+-------------------------+-------+
5.my.cnf初始化参数配置
（1）默认加载插件
plugin-load=AUDIT=libaudit_plugin.so
（2）打开日志记录
audit_json_file=on
audit_json_log_file=/usr/local/mysql/logs/mysql-audit.json
（3）需要审计的命令，如果不写则默认审计所有DDL DML，建议不写
audit_record_cmds='insert,delete,update,create,drop,alter,grant,truncate'
6.测试audit插件
mysql -uroot -p
create database wsdb;
use wsdb;
CREATE TABLE table01 (
id INT AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
age INT,
PRIMARY KEY (id)
);
INSERT INTO table01 (name, age) VALUES ('ws', 25);
update table01 set name='wangsheng' where name='ws';
drop table table01;
可以看到只记录了部分的命令比如insert，update，并不是非常好用
建议不设置audit_record_cmds，会审计所有命令