mysql啟用審計功能

【前言】其實mysql資料庫本身並沒有審計功能的，在實際的工作中往往會需要用到這方面的功能，公司最近就出現了這麼一個讓人很煩人的場景：pos系統有六臺應用連線資料庫，且資料庫本身跟其他系統也有關聯，最近業務反饋偶爾會出現系統日結翻倍的情況，開發找到了導致日結出現問題的SQL語句，但是不能確認是從哪臺應用發起產生的，這時候感覺很無力，所以找到了資料庫管理員；

 

【方法】業務需要找到哪臺應用發出的SQL語句，透過查詢發現可以透過init-connect和binlog的方法進行mysql的審計；

思路如下：

【1】mysql binlog記錄了所有對資料庫實際執行的sql語句，及其執行時間和connection_id；

【2】init-connect記錄connection_id對應的詳細使用者資訊。

【3】找到binlog的語句後，根據connection_id便可找到對應的使用者連線資訊

 

【操作步驟】

【1】修改引數檔案，新增設定：init-connect='insert into accesslog.accesslog values(connection_id(),now(),user(),current_user());'

【2】建立存放資訊的資料庫

create database logDB;

【3】建立存放資訊的表

CREATE TABLE logDB.log (`id` int(11) primary key auto_increment, `time` timestamp, `localname` varchar(30), `matchname` varchar(30))

【4】所有需要審計的使用者進行檢查，不能具有Super使用者許可權

【5】重啟資料庫

 

【結合binlog進行排查的步驟】

【1】查詢時間範圍內修改過zdyjb_day表的SQL語句：

mysqlbinlog --start-datetime="2015-8-25 09:00:00" --stop-datetime="2015-9-1 09:00:00" mysql-bin.002320| grep 'zdyjb_day' -B 5  > ab.log

 

【2】查詢這個時間範圍內執行UPDATE的語句

BEGIN /*!*/; # at 33871395 #150901  8:37:54 server id 1  end_log_pos 33871572     Query    thread_id=99127    exec_time=0    error_code=0 SET TIMESTAMP=1441067874/*!*/; UPDATE zdyjb_day SET zbje='6000',xgrq='1441067874' --

 

【3】根據thread_id查詢使用者的資訊

mysql> select * from logDB.log where id=436 ; +-----+---------------------+---------------------+-----------+ | id  | time                | localname           | matchname | +-----+---------------------+---------------------+-----------+ | 436 | 2015-08-31 22:44:14 | ipos@192.168.0.15 | ipos@%    | +-----+---------------------+---------------------+-----------+ 1 row in set (0.00 sec)

透過日誌可以看到執行的使用者為ipos@%，從192.168.0.15這臺登入的，一下子整個問題排查的範圍少了很多，很快就能定位到問題的根源了；

 

【問題總結】

【1】啟用init-connect會不會降低伺服器的效能？

從原理上每個使用者的連線成功並往資料庫做變更的時候才會記錄，所以整個插入其實並不會消耗很大的資源，佔用的資源主要為IO和儲存空間，而且也只是在出現問題期間啟用；

 

【2】啟用init-connect的資料記錄全面嗎

不會記錄Super許可權的使用者，所以該記錄是部分的；