880
技術社區[雲棲]
mysql審計開啟--兩種方法
第一種方法：安裝插件的方式
這裏使用的是macfee的mysql audit插件，雖然日誌信息比較大，對性能影響大，但是如果想要開啟審計，那也應該忍受了。介紹幾個參考地址：
[root@iZ2zeh44pi6rlahxj7s9azZ data]# ls
audit-plugin-mysql-5.7-1.1.4-725-linux-x86_64.zip my3307 zzz
[root@iZ2zeh44pi6rlahxj7s9azZ data]# unzip audit-plugin-mysql-5.7-1.1.4-725-linux-x86_64.zip
在配置文件my.cnf的[mysqld]中加入
plugin-load=audit=libaudit_plugin.so
[root@iZ2zeh44pi6rlahxj7s9azZ /]# /data/audit-plugin-mysql-5.7-1.1.4-725/lib/libaudit_plugin.so
[root@iZ2zeh44pi6rlahxj7s9azZ lib]# mv libaudit_plugin.so /opt/mysql/lib/plugin/
[root@iZ2zeh44pi6rlahxj7s9azZ plugin]# chown -R mysql:mysql libaudit_plugin.so
[root@iZ2zeh44pi6rlahxj7s9azZ 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)root@test 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
[root@iZ2zeh44pi6rlahxj7s9azZ data]# chmod +x ./offset-extract.sh
[root@iZ2zeh44pi6rlahxj7s9azZ 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.
[root@iZ2zeh44pi6rlahxj7s9azZ data]# yum install gdb
[root@iZ2zeh44pi6rlahxj7s9azZ 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 |
+----------------------------+----------+--------------------+--------------------+---------+
開啟審計日誌：
root@test 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、創建審計用的庫表。
yuelei@(none) 04:36:31>create database db_monitor ;
Query OK, 1 row affected (0.00 sec)
yuelei@(none) 04:36:35>use db_monitor ;
Database changed
yuelei@db_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參數
yuelei@db_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)
yuelei@db_monitor 04:39:34>flush privileges;
Query OK, 0 rows affected (0.00 sec)
3、授予普通用戶對accesslog表的insert權限（在root用戶下）
root@(none) 04:38:34>create user demon@'%';
Query OK, 0 rows affected (0.00 sec)
root@(none) 04:38:34>grant insert on db_monitor.accesslog to demon@'%';
Query OK, 0 rows affected (0.00 sec)
進入具有insert（普通權限）的用戶demon中對數據庫進行一係列操作
demon@db_monitor 04:39:42>use test;
Database changed
demon@test 04:40:00>delete from test4 where id =10;
Query OK, 1 row affected (0.01 sec)
demon@test 04:40:16>flush privileges;
Query OK, 0 rows affected (0.00 sec)
進入具有高級權限的用戶下，查看表中的記錄，配合binlog日誌是否能追蹤到時哪個用戶,，結果是一目了然。
root@test 04:40:23>select * from db_monitor.accesslog;
+-----------+---------------------+-----------------+-----------------+
| thread_id | log_time | localname | matchname |
+-----------+---------------------+-----------------+-----------------+
| 9 | 2017-07-24 16:44:43 | demon@127.0.0.1 | demon@127.0.0.1 |
+-----------+---------------------+-----------------+-----------------+
1 row in set (0.00 sec)
[root@iZ2zeh44pi6rlahxj7s9azZ 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' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
總結：
第一種方法缺點：日誌信息比較大，對性能影響大。優點：對每一時刻每一用戶的操作都有記錄
第二種方法缺點：隻對有低級權限的用戶的操作有記錄，權限高的則沒有 。優點：日誌信息比較小，對性能影響小
最後更新：2017-07-27 16:03:17