mysql行為審記

標籤：hub   schema   外掛程式   .com   ems   init   system   class   read   

外掛程式源碼地址
https://github.com/mcafee/mysql-audit
外掛程式安裝方法
https://github.com/mcafee/mysql-audit/wiki/Installation
外掛程式
https://bintray.com/mcafee/mysql-audit-plugin/release/1.0.9-585

1、查看mysql外掛程式存放目錄
mysql> SHOW GLOBAL VARIABLES LIKE ‘plugin_dir‘;
+---------------+-----------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------+
| plugin_dir | /usr/local/mysql/lib/plugin/ |
+---------------+-----------------------------------+
1 row in set (0.01 sec)

2、複製libaudit_plugin.so 至 mysql外掛程式目錄
mv libaudit_plugin.so /usr/local/mysql/lib/plugin/
chmod a+x /usr/local/mysql/lib/plugin/*
chown mysql:mysql /usr/local/mysql/lib/plugin/*

3、安裝外掛程式
初使使用線上安裝，但是安裝失敗
mysql> INSTALL PLUGIN AUDIT SONAME ‘libaudit_plugin.so‘;
ERROR 1524 (HY000): Plugin ‘AUDIT‘ is not loaded

然後使用修改配置，然後重啟資料庫，官方推薦使用修改配置方式安裝。
Note: On production systems, McAfee recommends using the plugin-load option for installing the audit plugin.

修改my.cnf檔案，添加載入審計外掛程式代碼
plugin-load=AUDIT=libaudit_plugin.so
audit_json_file=1
audit_force_record_logins=1
audit_json_file_sync=1
audit_whitelist_users=test2,test1 #不記錄行為的使用者

audit_whitelist_cmds =BEGIN,COMMIT,SELECT  #這個參數看需要要不要設，過濾掉查詢的

重啟資料庫
/etc/init.d/mysql.server restart

 

[[email protected] mysql]# tail mysql-audit.json
{"msg-type":"activity","date":"1490176712103","thread-id":"3","query-id":"0","user":"root","priv_user":"root","host":"localhost","cmd":"Connect","query":"Connect"}
{"msg-type":"activity","date":"1490176712106","thread-id":"3","query-id":"11","user":"root","priv_user":"root","host":"localhost","cmd":"select","query":"select @@version_comment limit 1"}
{"msg-type":"activity","date":"1490176724747","thread-id":"3","query-id":"12","user":"root","priv_user":"root","host":"localhost","cmd":"select","query":"SELECT DATABASE()"}
{"msg-type":"activity","date":"1490176724748","thread-id":"3","query-id":"13","user":"root","priv_user":"root","host":"localhost","cmd":"Init DB","objects":[{"db":"test","obj_type":"DATABASE"}],"query":"Init DB"}
{"msg-type":"activity","date":"1490176724754","thread-id":"3","query-id":"14","user":"root","priv_user":"root","host":"localhost","cmd":"show_databases","objects":[{"db":"information_schema","name":"/tmp/#sql_a6c_0","obj_type":"TABLE"}],"query":"show databases"}
{"msg-type":"activity","date":"1490176724769","thread-id":"3","query-id":"15","user":"root","priv_user":"root","host":"localhost","cmd":"show_tables","objects":[{"db":"information_schema","name":"/tmp/#sql_a6c_0","obj_type":"TABLE"}],"query":"show tables"}
{"msg-type":"activity","date":"1490176724771","thread-id":"3","query-id":"16","user":"root","priv_user":"root","host":"localhost","cmd":"show_fields","query":"show_fields"}
{"msg-type":"activity","date":"1490176753954","thread-id":"3","query-id":"17","user":"root","priv_user":"root","host":"localhost","cmd":"show_tables","objects":[{"db":"information_schema","name":"/tmp/#sql_a6c_0","obj_type":"TABLE"}],"query":"show tables"}
{"msg-type":"activity","date":"1490176769225","thread-id":"3","query-id":"18","user":"root","priv_user":"root","host":"localhost","cmd":"select","objects":[{"db":"test","name":"t1","obj_type":"TABLE"}],"query":"select * from t1"}
{"msg-type":"activity","date":"1490176779812","thread-id":"3","query-id":"19","user":"root","priv_user":"root","host":"localhost","cmd":"Quit","query":"Quit"}

 

mysql行為審記