目录
2. 设置变量 init_connect（不需要重启服务器）
需求：查出有哪些 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)6. 还原（不需要重启 MySQL 服务器）
set global init_connect='';