mysql5.7开启审计

环境

Centos 7.9
mysql 5.7.40

下载

1
2
wget "https://github.com/trellix-enterprise/mysql-audit/releases/download/v1.1.13/audit-plugin-mysql-5.7-1.1.13-1008-linux-x86_64.zip"
unzip audit-plugin-mysql-5.7-1.1.13-1008-linux-x86_64.zip

安装

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> show variables like "%plugin%";
+-----------------------------------------------+--------------------------+
| Variable_name                                 | Value                    |
+-----------------------------------------------+--------------------------+
| default_authentication_plugin                 | mysql_native_password    |
| plugin_dir                                    | /usr/lib64/mysql/plugin/ |
| replication_optimize_for_static_plugin_config | OFF                      |
+-----------------------------------------------+--------------------------+
3 rows in set (0.00 sec)
#查看插件目录，并将插件复制过去
cp audit-plugin-mysql-5.7-1.1.13-1008/lib/libaudit_plugin.so /usr/lib64/mysql/plugin/
#插件授权
chmod +x /usr/lib64/mysql/plugin/libaudit_plugin.so
chown mysql:mysql /usr/lib64/mysql/plugin/libaudit_plugin.so
mysql> INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';
ERROR 1123 (HY000): Can't initialize function 'AUDIT'; Plugin initialization function failed.
#这个时候会报错，配置 offset即可

配置offset

1
2
3
4
5
6
7
# which mysqld
/usr/sbin/mysqld
# bash audit-plugin-mysql-5.7-1.1.13-1008/utils/offset-extract.sh /usr/sbin/mysqld
//offsets for: /usr/sbin/mysqld (5.7.40)
{"5.7.40","024070f0adc00465dceb91f7ccd08073", 7832, 7880, 3640, 4800, 456, 360, 0, 32, 64, 160, 544, 7996, 4368, 3656, 3664, 3668, 6080, 2072, 8, 7064, 7104, 7088, 13480, 148, 672, 0},
# 将offset配置到my.cnf
audit_offsets = 7832, 7880, 3640, 4800, 456, 360, 0, 32, 64, 160, 544, 7996, 4368, 3656, 3664, 3668, 6080, 2072, 8, 7064, 7104, 7088, 13480, 148, 672, 0

配置其它参数

1
2
3
4
5
# 将其它参数配置到my.cnf
audit_json_file = on
plugin-load=audit = libaudit_plugin.so
audit_record_cmds = 'select'
audit_json_log_file = /var/lib/mysql/mysql_audit_json_file.log

查看是否生效

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
#查看插件是否启用
mysql> show plugins;
+----------------------------+----------+--------------------+--------------------+---------+
| Name                       | Status   | Type               | Library            | License |
+----------------------------+----------+--------------------+--------------------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
.............................................................................................
| AUDIT                      | ACTIVE   | AUDIT              | libaudit_plugin.so | GPL     |
+----------------------------+----------+--------------------+--------------------+---------+
 
#查看审计插件的版本
mysql>  show global status like '%audit%';
+------------------------+-------------+
| Variable_name          | Value       |
+------------------------+-------------+
| Audit_protocol_version | 1.0         |
| Audit_version          | 1.1.13-1008 |
+------------------------+-------------+
2 rows in set (0.01 sec)
 
 
#查看审计日志路径
mysql> SHOW GLOBAL VARIABLES LIKE 'audit_json_file';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| audit_json_file | ON    |
+-----------------+-------+
1 row in set (0.00 sec)

查看日志

1
tailf /var/lib/mysql/mysql_audit_json_file.log

卸载插件

1
2
需要在 my.cnf 中 [mysqld] 下添加 audit_uninstall_plugin=1，重启mysql。重启完毕后执行两次 UNINSTALL PLUGIN AUDIT; 即可卸载
卸载后删除audit_uninstall_plugin=1，重启

按天日志切割

1
2
3
4
5
6
7
8
9
10
11
12
13
14
cat > /etc/logrotate.d/audit << EOF
/var/lib/mysql/mysql_audit_json_file.log {
    create 600 mysql mysql
    missingok
    daily
    copytruncate
    rotate 180
    notifempty
    compress
    dateext
}
EOF
# 测试日志切割
logrotate -f /etc/logrotate.d/audit