MySQL審計插件使用

下載MySQL審計插件
https://github.com/mcafee/mysql-audit/releases
or
本文使用mcafee官網下載鏈接的軟件：
https://dl.bintray.com/mcafee/mysql-audit-plugin/

詳細的配置可以參考官方文檔:
https://github.com/mcafee/mysql-audit/wiki/Installation

解壓文件：
unzip audit-plugin-mysql-5.7-1.1.7-805-linux-x86_64.zip

將安裝包libaudit_plugin.so文件復制到 plugin_dir目錄：
root@localhost 10:07:  [(none)]>show global variables like 'plugin_dir';
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| plugin_dir    | /usr/local/mysql/lib/plugin/ |
+---------------+------------------------------+
1 row in set (0.00 sec)

cp audit-plugin-mysql-5.7-1.1.7-805/lib/libaudit_plugin.so /usr/local/mysql/lib/plugin/
chmod +x /usr/local/mysql/lib/plugin/libaudit_plugin.so

安裝插件：
root@localhost 10:18:  [(none)]>INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';
Query OK, 0 rows affected (0.73 sec)

確認：
root@localhost 10:18:  [(none)]>root@localhost 10:18:  [(none)]>SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%';
+-------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+-------------+---------------+
| AUDIT       | ACTIVE        |
+-------------+---------------+
1 row in set (0.00 sec)

審計默認不開啟，開啟審計：
set global audit_json_file=on;
set global audit_record_cmds='delete,update,create_table,create_db,drop_db,drop_table,alter_table,grant,truncate';

查看audit日志輸出名稱：
root@localhost 10:23:  [(none)]>show global variables like 'audit_json_log_file';
+---------------------+------------------+
| Variable_name       | Value            |
+---------------------+------------------+
| audit_json_log_file | mysql-audit.json |
+---------------------+------------------+
1 row in set (0.00 sec)

編輯配置文件添加：
[mysqld]
audit_json_file=on
audit_record_cmds='delete,update,create_table,create_db,drop_db,drop_table,alter_table,grant,truncate'

mysql-audit.json文件默認在datadir下面，查看審計日志內容：
tail -f mysql-audit.json
{"msg-type":"activity","date":"1552273694556","thread-id":"3","query-id":"13","user":"root","priv_user":"root","ip":"","host":"localhost","connect_attrs":{"_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"20105","_client_version":"5.7.24","_platform":"x86_64","program_name":"mysql"},"pid":"20105","os_user":"root","appname":"mysql","status":"0","cmd":"create_db","query":"create database app_store"}


常用參數說明：
1. audit_json_file=on|off
是否開啟audit功能
2. audit_json_log_file
審計文件的路徑和名稱信息
3. audit_record_cmds
audit記錄的命令,默認為記錄所有命令
可以根據需要設置為任意dml、dcl、ddl的組合：
例如：audit_record_cmds="select,insert,delete,update"
4. audit_record_objs
audit記錄操作的對象或表,默認為記錄所有對象
也可以指定為下面的格式
audit_record_objs="*.table_name,db_name.*,db_name.table_name"
5. audit_whitelist_users
可以設置白名單：
如set global audit_whitelist_users="root,admin";

如果你的audit審計的日志較多，可以考慮日志分割，設置日志壓縮和保留天數：

vi /etc/logrotate.d/mysql-audit
/data/mysql/data/mysql-audit.json {
daily
copytruncate
compress
rotate 7
dateext
dateformat .%Y-%m-%d
}

手動生成日志切割：
logrotate -vf /etc/logrotate.d/mysql-audit