閱讀原文請點選
摘要: 第一種方法:安裝插件的方式 這裡使用的是macfee的mysql audit插件,雖然日志資訊比較大,對性能影響大,但是如果想要開啟審計,那也應該忍受了。介紹幾個參考位址: wiki首頁:https://github.
第一種方法:安裝插件的方式 這裡使用的是macfee的mysql audit插件,雖然日志資訊比較大,對性能影響大,但是如果想要開啟審計,那也應該忍受了。介紹幾個參考位址: wiki首頁: https://github.com/mcafee/mysql-audit/wiki 插件二進制包下載下傳: https://bintray.com/mcafee/mysql-audit-plugin/release/1.1.4-725#files [[email protected] data]# ls audit-plugin-mysql-5.7-1.1.4-725-linux-x86_64.zip my3307 zzz [[email protected] data]# unzip audit-plugin-mysql-5.7-1.1.4-725-linux-x86_64.zip 在配置檔案my.cnf的[mysqld]中加入 plugin-load=audit=libaudit_plugin.so [[email protected] /]# /data/audit-plugin-mysql-5.7-1.1.4-725/lib/libaudit_plugin.so [[email protected] lib]# mv libaudit_plugin.so /opt/mysql/lib/plugin/ [[email protected] plugin]# chown -R mysql:mysql libaudit_plugin.so [[email protected] plugin]# chmod +x libaudit_plugin.so 然後進行插件的加載: root(none) 04:17:18> INSTALL PLUGIN audit SONAME 'libaudit_plugin.so'; 解除安裝插件的方法: root(none) 04:17:55> uninstall plugin audit; 插件的加載出現問題(可在error-log中檢視): (1)[email protected] 05:15:37>INSTALL PLUGIN audit_log SONAME 'libaudit_plugin.so'; ERROR 1127 (HY000): Can't find symbol 'audit_log' in library 原因:是前面說的配置檔案中加入的plugin-load=audit=libaudit_plugin.so,中間的audit是對其的命名,如果加載用audit_log則會報錯 解決辦法:用一緻的名字 (2)ERROR 1123 (HY000): Can't initialize function 'audit'; Plugin initialization function failed. 問題:從報錯很明顯是因為加載時初始化出現了問題,可能是資料不一緻導緻的 解決辦法:用 offest-extract.sh解決,方法如下: Download the offset-extract.sh script from: https://raw.github.com/mcafee/mysql-audit/master/offset-extract/offset-extract.sh Note: The offest-extract.sh script changed as the plugin added additional offsets. If you are using a build earlier than 1.0.8-515, you will need to use the script from the 1.0.7 tag: https://raw.githubusercontent.com/mcafee/mysql-audit/v1.0.7/offset-extract/offset-extract.sh . Further more, if you are using a build earlier than 1.0.4-451, you will need to use the script from the 1.0.3 tag: https://raw.github.com/mcafee/mysql-audit/v1.0.3/offset-extract/offset-extract.sh
[[email protected] data]# chmod +x ./offset-extract.sh [[email protected] data]# ./offset-extract.sh /opt/mysql-5.7.19-linux-glibc2.12-x86_64/bin/mysqld ERROR: gdb not found. Make sure gdb is installed and on the path. [[email protected] data]# yum install gdb [[email protected] data]# ./offset-extract.sh /opt/mysql-5.7.19-linux-glibc2.12-x86_64/bin/mysqld //offsets for: /opt/mysql-5.7.19-linux-glibc2.12-x86_64/bin/mysqld (5.7.19) {"5.7.19","b4633eb887552a3bbb5db3a1eea76e48", 7800, 7848, 3624, 4776, 456, 360, 0, 32, 64, 160, 536, 7964, 4352, 3648, 3656, 3660, 6048, 2072, 8, 7032, 7072, 7056}, 在配置檔案my.cnf的[mysqld]中加入 audit_offsets=7800, 7848, 3624, 4776, 456, 360, 0, 32, 64, 160, 536, 7964, 4352, 3648, 3656, 3660, 6048, 2072, 8, 7032, 7072, 7056 然後再次加載插件就可以了。通過show plugins;驗證是否成功(最後一行) | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | | AUDIT | ACTIVE | AUDIT | libaudit_plugin.so | GPL | +----------------------------+----------+--------------------+--------------------+---------+ 開啟審計日志: [email protected] 04:48:57> SET GLOBAL audit_json_file=ON; 進入datadir目錄,看到mysql-audit.json的檔案即為審計日志檔案 ps:具體有關插件的參數含義見 https://github.com/mcafee/mysql-audit/wiki/Configuration
第二種:通過init-connect 1、建立審計用的庫表。 [email protected](none) 04:36:31>create database db_monitor ; Query OK, 1 row affected (0.00 sec)
[email protected](none) 04:36:35>use db_monitor ; Database changed [email protected]_monitor 04:37:17>CREATE TABLE accesslog -> ( thread_id int(11) DEFAULT NULL, #程序id -> log_time datetime default null, #登入時間 -> localname varchar(50) DEFAULT NULL, #登入名稱,帶詳細ip -> matchname varchar(50) DEFAULT NULL, #登入使用者 -> key idx_log_time(log_time) -> ) Query OK, 0 rows affected (0.02 sec) 2、配置init-connect參數 [email protected]_monitor 04:37:21>set global init_connect='insert into db_monitor.accesslog(thread_id,log_time,localname,matchname) values(connection_id(),now(),user(),current_user())'; Query OK, 0 rows affected (0.00 sec) [email protected]_monitor 04:39:34>flush privileges; Query OK, 0 rows affected (0.00 sec) 3、授予普通使用者對accesslog表的insert權限(在root使用者下) [email protected](none) 04:38:34>create user [email protected]'%'; Query OK, 0 rows affected (0.00 sec) [email protected](none) 04:38:34>grant insert on db_monitor.accesslog to [email protected]'%'; Query OK, 0 rows affected (0.00 sec) 進入具有insert(普通權限)的使用者demon中對資料庫進行一系列操作 [email protected]_monitor 04:39:42>use test; Database changed [email protected] 04:40:00>delete from test4 where id =10; Query OK, 1 row affected (0.01 sec) [email protected] 04:40:16>flush privileges; Query OK, 0 rows affected (0.00 sec) 進入具有進階權限的使用者下,檢視表中的記錄,配合binlog日志是否能追蹤到時哪個使用者,,結果是一目了然。 [email protected] 04:40:23>select * from db_monitor.accesslog; +-----------+---------------------+-----------------+-----------------+ | thread_id | log_time | localname | matchname | +-----------+---------------------+-----------------+-----------------+ | 9 | 2017-07-24 16:44:43 | [email protected] | [email protected] | +-----------+---------------------+-----------------+-----------------+ 1 row in set (0.00 sec) [[email protected] log]# /opt/mysql/bin/mysqlbinlog mysql-bin.000044; # at 3563 #170724 16:46:23 server id 1 end_log_pos 3624 GTID last_committed=17 sequence_number=18 rbr_only=no SET @@SESSION.GTID_NEXT= '90ad28b0-6d2b-11e7-8eb5-00163e06ff5b:347'; # at 3624 #170724 16:46:23 server id 1 end_log_pos 3699 Query thread_id=9 exec_time=0 error_code=0 SET TIMESTAMP=1500885983; BEGIN ; # at 3699 #170724 16:46:23 server id 1 end_log_pos 3798 Query thread_id=9 exec_time=0 error_code=0 use `test`; SET TIMESTAMP=1500885983; delete from test3 where id =9 ; # at 3798 #170724 16:46:23 server id 1 end_log_pos 3825 Xid = 65 COMMIT; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' ; DELIMITER ; # End of log file
總結: 第一種方法缺點:日志資訊比較大,對性能影響大。優點:對每一時刻每一使用者的操作都有記錄 第二種方法缺點:隻對有低級權限的使用者的操作有記錄,權限高的則沒有 。優點:日志資訊比較小,對性能影響小
閱讀原文請點選