需求：查出有哪些 IP 修改了表 sod_song_artist_relation。
方案：init_connect mysqlbinlog
步骤：
1. 建监控连接信息的表
代码语言：javascript复制use test;
create table accesslog(`thread_id` int primary key, `time` timestamp, `localname` varchar(40), `machine_name` varchar(40));2. 设置变量 init_connect（不需要重启 MySQL 服务器）
代码语言：javascript复制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. 分配用户权限
代码语言：javascript复制mysql> grant all on test.accesslog to songod;
Query OK, 0 rows affected (0.00 sec)4. 解析并过滤 binlog
代码语言：javascript复制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. 查询客户端信息
代码语言：javascript复制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)6. 还原（不需要重启 MySQL 服务器）
代码语言：javascript复制set global init_connect='';参考：
在MySQL中使用init-connect与binlog来实现用户操作追踪记录
