MySQL 審計實戰：init-connect + Binlog 追蹤使用者操作全鏈路

方案架構

利用 init_connect 在每次連線建立時，自動記錄 thread_id、登入帳號、時間戳至審計表；配合 Row 格式 Binlog 的 thread_id 欄位，實現「誰、何時、執行何 SQL」的完整追溯。

部署步驟

1. 建立審計庫與表

CREATE DATABASE access_log;
USE access_log;
CREATE TABLE access_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    thread_id INT NOT NULL COMMENT '連線 thread_id，關鍵關聯鍵',
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    localname VARCHAR(30) COMMENT 'USER() 顯示的 使用者@主機',
    matchname VARCHAR(30) COMMENT 'CURRENT_USER() 實際認證帳號'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='使用者登入審計';

2. 配置 init_connect

編輯 /etc/my.cnf（或 /usr/local/mysql/my.cnf）：

[mysqld]
init_connect='INSERT INTO access_log.access_log VALUES(NULL, CONNECTION_ID(), NOW(), USER(), CURRENT_USER())'

注意：init_connect 對擁有 SUPER 權限的使用者不生效，故審計帳號不可授予 SUPER。

3. 建立審計帳號並授權

CREATE USER 'audit_user'@'%' IDENTIFIED BY 'cacti_user1603';
GRANT SELECT, INSERT, UPDATE, DELETE ON test.* TO 'audit_user'@'%';
GRANT SELECT, INSERT ON access_log.* TO 'audit_user'@'%';
FLUSH PRIVILEGES;

4. 清理匿名使用者（關鍵）

SELECT user, host FROM mysql.user WHERE user = '';
DROP USER ''@'localhost';
DROP USER ''@'db_server';

否則匿名使用者連線時無 access_log 權限，導致 init_connect command failed 連線中斷。

5. 驗證登入記錄

mysql -uaudit_user -p
SELECT * FROM access_log.access_log;
+----+-----------+---------------------+---------------------------+--------------+
| id | thread_id | log_time            | localname                 | matchname    |
+----+-----------+---------------------+---------------------------+--------------+
|  4 |        41 | 2014-07-28 16:19:37 | audit_user@localhost      | audit_user@% |
|  5 |        42 | 2014-07-28 16:20:32 | audit_user@localhost      | audit_user@% |
+----+-----------+---------------------+---------------------------+--------------+

Binlog 關聯追蹤實戰

1. 確保 Binlog 未過濾業務庫

# my.cnf 註釋掉
# binlog-ignore-db=test

重啟 MySQL 後重新測試寫入。

2. 解析 Binlog 定位 thread_id

/usr/local/mysql/bin/mysqlbinlog --base64-output=DECODE-ROWS mysql-bin.000018 -v > 3.log
grep -A 10 "thread_id=1" 3.log

輸出範例：

#140728 20:12:48 server id 72 end_log_pos 1175 CRC32 0xa323c00e Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1406549568/*!*/;
BEGIN
# at 1175
#140728 20:12:48 server id 72 end_log_pos 1229 CRC32 0xbb8ca914 Table_map: `test`.`t1` mapped to number 72
# at 1229
### INSERT INTO `test`.`t1` SET @1=5, @2='t5'
COMMIT/*!*/;

3. 關聯 processlist 獲取客戶端資訊

mysql> SHOW FULL PROCESSLIST;
+----+------------+-------------------+------+---------+------+-------+-----------------------+
| Id | User       | Host              | db   | Command | Time | State | Info                  |
+----+------------+-------------------+------+---------+------+-------+-----------------------+
|  1 | audit_user | 192.168.3.62:44657| test | Sleep   |  162 |       | NULL                  |
+----+------------+-------------------+------+---------+------+-------+-----------------------+

Host 欄顯示 IP:Port，Port 即為客戶端來源埠號。

4. 反查客戶端進程

# 客戶端機器上
netstat -antlp | grep 44657
# tcp 0 0 192.168.3.62:44657 192.168.1.12:3307 ESTABLISHED 6335/mysql

ps -ef | grep 6335
# tim  6335 25497  0 19:59 pts/1  00:00:00 mysql -uaudit_user -p -h192.168.1.12 -P3307

若為 Web 應用，同樣可透過 PID 追溯至具體服務進程。

避坑指南

• init_connect 失效 → 檢查匿名使用者、審計帳號權限、SQL 語法錯誤。
• Binlog 無業務 SQL → 檢查 binlog-ignore-db、binlog-do-db、replicate-* 過濾規則。
• Row 格式 Binlog 才記錄 thread_id，Statement 格式不支援。
• 高併發下審計表寫入可能成為熱點，建議定期歸檔或分區。