系统版本: Centos 7
MySQL版本: 5.7.19
架构: 主从架构
审计插件: audit-plugin-mysql-5.7-1.1.6-784-linux-x86_64.zip
1). 安装MySQL 5.7.19与线上版本同步;
2). 安装Audit审计插件;
3). 开启Audit审计插件功能;
4). 配置就算重启数据库后依然会自动开启Audit;
配置说明:
1). /usr/local/mysql/data/mysql-audit.json为数据库的数据存储(data)目录,名称默认为此;
一、安装数据库过程省略
二、安装Audit审计插件;
mysql> show variables like 'plugin_dir' +---------------+------------------------------+ | Variable_name | Value | +---------------+------------------------------+ | plugin_dir | /usr/local/mysql/lib/plugin/ | +---------------+------------------------------+ ~]# unzip audit-plugin-mysql-5.7-1.1.6-784-linux-x86_64.zip ~]# cd audit-plugin-mysql-5.7-1.1.6-784-linux-x86_64/lib lib]# cp libaudit_plugin.so /usr/local/mysql/lib/plugin/ lib]# cd /usr/local/mysql/lib/plugin/ lib]# chmod 755 libaudit_plugin.so mysql> install plugin AUDIT soname 'libaudit_plugin.so'; mysql> show plugins; .... 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.6-784 | +------------------------+-----------+
三、 开启Audit审计插件功能;
# 检查插件功能是否开启 mysql> show 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 | +-------------------------+-------+ # 开启插件服务 mysql> set global audit_json_file=1; # 再次检查 mysql> show variables like '%audit_json_file%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | audit_json_file | ON | | audit_json_file_bufsize | 1 | | audit_json_file_flush | OFF | | audit_json_file_retry | 60 | | audit_json_file_sync | 0 | +-------------------------+-------+ # 执行些SQL检查是否会记录; mysql> show databases; +--------------------+ | information_schema | | menagerie | | mysql | | performance_schema | | sys | +--------------------+ # 查看日志是否有所记录 ~]# cat /usr/local/mysql/data/mysql-audit.json { "msg-type": "activity", "date": "1598594856485", "thread-id": "5", "query-id": "37", "user": "root", "priv_user": "root", "ip": "", "host": "localhost", "connect_attrs": { "_os": "linux - glibc2 .12 "," _client_name ":" libmysql "," _pid ":" 21846 "," _client_version ":" 5.7 .19 "," _platform ":" x86_64 "," program_name ":" mysql "}," pid ":" 21846 "," os_user ":" root "," appname ":". / mysql "," rows ":" 5 "," status ":" 0 "," cmd ":" show_databases "," objects ":[{" db ":" information_schema "," name ":" / tmp / #sql_47c4_0 "," obj_type ":" TABLE "}]," query ":" show databases "}
四、 配置就算重启数据库后依然会自动开启Audit;
~]# vim /etc/my.cnf audit_json_file = on plugin-load=AUDIT=libaudit_plugin.so
五、 配置审计指定命令
~]# cd /app/audit-plugin-mysql-5.7-1.1.6-784/utils utils]# chmod +x offset-extract.sh # 执行计算偏移量的脚本前需安装此服务; utils]# yum -y install gdb # 计算偏移量; utils]# ./offset-extract.sh /usr/local/mysql/bin/mysqld ~]# vim /etc/my.cnf # 支持审计的SQL查看'http://bazaar.launchpad.net/~mysql/mysql-server/5.6/view/head:/sql/mysqld.cc#L3424' audit_record_cmds='insert,update,delete,drop_db,create_db,alter_db,grant,truncate' audit_offsets=7800, 7848, 3624, 4776, 456, 360, 0, 32, 64, 160, 536, 7964, 4352, 3648, 3656, 3660, 6048, 2072, 8, 7032, 7072, 7056, 13432, 148, 672