mysql追踪用户操作(增删改)记录 ---> binlog+init_connect

mysql数据库操作记录如果想找出哪个用户操作的，二进制日志记录了操作记录，线程号等信息，但是却没有记录用户信息，但是可以结合init_connect来实现追踪。

init_connect在每次连接的初始化阶段，记录下这个连接的用户，和connection_id信息。

1.1.查询init_connect变量

mysql> SHOW VARIABLES like 'init%';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| init_connect  | SET NAMES utf8 |
| init_file     |                |
| init_slave    |                |
+---------------+----------------+

1.2.创建accesslog库和accesslog表

-- 创建accesslog数据库
CREATE DATABASE `accesslog`;

-- 创建accesslog表
CREATE TABLE `accesslog` (
	`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
	`thread_id` INT ( 11 ) DEFAULT NULL,
	`log_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	`localname` VARCHAR ( 30 ) DEFAULT NULL,
	`matchname` VARCHAR ( 30 ) DEFAULT NULL,
PRIMARY KEY ( `id` ) 
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

1.3.配置init_connect变量

-- set修改
mysql> set global init_connect='insert into accesslog.accesslog values(connection_id(),now(),user(),current_user());';

-- 配置文件修改：
init-connect='insert into accesslog.accesslog values(null,connection_id(),now(),user(),current_user());'

重启mysql服务

1.4.授权用户对创建的库和表权限

-- 对test用户进行授权
mysql> grant insert,select,update on accesslog.* to 'test'@'%';
mysql> flush privileges;

注：创建普通用户，不能有super权限。init-connect对具有super权限的用户不起作用。同时此用户必须至少要有对accesslog.accesslog表的INSERT权限，如果没有，登录后的任何操作都会导致MySQL登录失败。

1.5.修改数据库数据

①修改数据库数据结合二进制日志定位到是哪个账户修改的数据库信息：

mysql> select * from diy_sequences where sequence_name='SEQ_TMP_CS_SEQ';
+----------------+-----------+------------+--------------+------------+------------+------------+-------------+
| sequence_name  | min_value | max_value  | increment_by | cycle_flag | order_flag | cache_size | last_number |
+----------------+-----------+------------+--------------+------------+------------+------------+-------------+
| SEQ_TMP_CS_SEQ |     10000 | 2147483647 |            1 | N          | N          |          0 |      222222 |
+----------------+-----------+------------+--------------+------------+------------+------------+-------------+
1 row in set (0.00 sec)

mysql> update diy_sequences set last_number=333333 where sequence_name='SEQ_TMP_CS_SEQ';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from diy_sequences where sequence_name='SEQ_TMP_CS_SEQ';
+----------------+-----------+------------+--------------+------------+------------+------------+-------------+
| sequence_name  | min_value | max_value  | increment_by | cycle_flag | order_flag | cache_size | last_number |
+----------------+-----------+------------+--------------+------------+------------+------------+-------------+
| SEQ_TMP_CS_SEQ |     10000 | 2147483647 |            1 | N          | N          |          0 |      333333 |
+----------------+-----------+------------+--------------+------------+------------+------------+-------------+
1 row in set (0.00 sec)

修改了一条记录数据。

②查询二进制日志：

二进制日志配置：

expire_logs_days=30
log-bin=/var/lib/mysql/mysql-bin
max_binlog_size=1G
binlog_format=row
binlog_row_image=full

查看二进制日志：

$ mysqlbinlog --no-defaults --base64-output=decode-row -vv mysql-bin.000012 > my-test-12.log

$ grep -C20 'diy_sequences'  my-test-12.log
# at 4217
#220214 11:22:08 server id 1  end_log_pos 4288 CRC32 0x5f30d408         Query   thread_id=26    exec_time=0     error_code=0
SET TIMESTAMP=1644837728/*!*/;
BEGIN
/*!*/;
# at 4288
#220214 11:22:08 server id 1  end_log_pos 4356 CRC32 0x20eba102         Table_map: `his`.`diy_sequences` mapped to number 135
# at 4356
#220214 11:22:08 server id 1  end_log_pos 4472 CRC32 0xed32489d         Update_rows: table id 135 flags: STMT_END_F
### UPDATE `his`.`diy_sequences`
### WHERE
###   @1='SEQ_TMP_CS_SEQ' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
###   @2=10000 /* INT meta=0 nullable=0 is_null=0 */
###   @3=2147483647 /* INT meta=0 nullable=0 is_null=0 */
###   @4=1 /* INT meta=0 nullable=0 is_null=0 */
###   @5='N' /* VARSTRING(3) meta=3 nullable=1 is_null=0 */
###   @6='N' /* VARSTRING(3) meta=3 nullable=1 is_null=0 */
###   @7=0 /* INT meta=0 nullable=0 is_null=0 */
###   @8=222222 /* INT meta=0 nullable=0 is_null=0 */
### SET
###   @1='SEQ_TMP_CS_SEQ' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
###   @2=10000 /* INT meta=0 nullable=0 is_null=0 */
###   @3=2147483647 /* INT meta=0 nullable=0 is_null=0 */
###   @4=1 /* INT meta=0 nullable=0 is_null=0 */
###   @5='N' /* VARSTRING(3) meta=3 nullable=1 is_null=0 */
###   @6='N' /* VARSTRING(3) meta=3 nullable=1 is_null=0 */
###   @7=0 /* INT meta=0 nullable=0 is_null=0 */
###   @8=333333 /* INT meta=0 nullable=0 is_null=0 */
# at 4472
#220214 11:22:08 server id 1  end_log_pos 4503 CRC32 0xf48a24f9         Xid = 2697
COMMIT/*!*/;

查看accesslog.accesslog

mysql> select * from accesslog.accesslog;
+----+-----------+---------------------+------------------------+--------------+
| id | thread_id | log_time            | localname              | matchname    |
+----+-----------+---------------------+------------------------+--------------+
| 10 |        26 | 2022-02-14 11:22:08 | test@172.17.0.3        | test@%       |
+----+-----------+---------------------+------------------------+--------------+
10 rows in set (0.00 sec)

其中thread_id=26的登录信息在accesslog.accesslog中记录，结合二进制日志可追溯到哪个用户对数据库进行的操作记录。