在 MySQL 中使用 init_connect 与 binlog 实现用户操作追踪记录

用户1148526

发布于 2024-05-01 23:54:15

18700

代码可运行

文章被收录于专栏：Hadoop数据仓库Hadoop数据仓库

运行总次数：0

代码可运行

需求：查出有哪些 IP 修改了表 sod_song_artist_relation。

方案：init_connect + mysqlbinlog

步骤：

1. 建监控连接信息的表

use test;
create table accesslog(`thread_id` int primary key, `time` timestamp, `localname` varchar(40), `machine_name` varchar(40));

2. 设置变量 init_connect（不需要重启 MySQL 服务器）

mysql> show variables like 'init%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| init_connect  |       |
| init_file     |       |
| init_slave    |       |
+---------------+-------+
3 rows in set (0.00 sec)
 
mysql> set global init_connect='insert into test.accesslog(thread_id,time,localname,machine_name) values(connection_id(),now(),user(),current_user());';
Query OK, 0 rows affected (0.00 sec)
 
mysql> show variables like 'init%';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                 |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| init_connect  | insert into dba.accesslog(thread_id,time,localname,machine_name) values(connection_id(),now(),user(),current_user()); |
| init_file     |                                                                                                                       |
| init_slave    |                                                                                                                       |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

3. 分配用户权限

mysql> grant all on test.accesslog to songod;
Query OK, 0 rows affected (0.00 sec)

4. 解析并过滤 binlog

cd /data/dblog/
mysqlbinlog mysql-bin.002349 --base64-output=decode-rows -v > a.log
grep -n -B15 "UPDATE \`songod\`.\`sod_song_artist_relation\`" a.log | grep thread_id | awk '{print $11}' | awk -F= '{print $2","}' | sort -n | uniq 

# 返回
276867518,
276867551,
277068047,

5. 查询客户端信息

mysql> select * from test.accesslog where thread_id in (276867518, 276867551, 277068047) order by thread_id;
+-----------+---------------------+---------------------+--------------+
| thread_id | time                | localname           | machine_name |
+-----------+---------------------+---------------------+--------------+
| 276867518 | 2024-04-29 18:12:19 | songod@172.18.8.134 | songod@%     |
| 276867551 | 2024-04-29 18:12:28 | songod@172.18.8.133 | songod@%     |
| 277068047 | 2024-04-30 10:43:12 | songod@172.18.8.134 | songod@%     |
+-----------+---------------------+---------------------+--------------+
3 rows in set (0.00 sec)