技术分享 | 是谁删了表？

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

2.1 init_connect 参数

mysql> show variables like 'init_connect';+---------------+-------+| Variable_name | Value |+---------------+-------+| init_connect  |       |+---------------+-------+1 row in set (0.00 sec)

mysql> set global init_connect='insert into auditdb.accesslog(connectionID,ConnUser,MatchUser,LoginTime) values(connection_id(),user(),current_user(),now());';Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'init_connect';+---------------+-------------------------------------------------------------------------------------------------------------------------------+| Variable_name | Value                                                                                                                         |+---------------+-------------------------------------------------------------------------------------------------------------------------------+| init_connect  | insert into auditd.accesslog(connectionID,ConnUser,MatchUser,LoginTime) values(connection_id(),user(),current_user(),now()); |+---------------+-------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

2.2 连接信息表

mysql> create database auditdb charset utf8mb4;Query OK, 1 row affected (0.01 sec)
mysql> create table auditdb.accesslog (id int (10) unsigned not null primary key auto_increment,       Connectionid int(10) unsigned,ConnUser varchar (30) not null default '',       MatchUser varchar (30) not null default '',       Logintime datetime);Query OK, 0 rows affected (0.02 sec)

3.1 模拟误删除

[root@db ~]# mysql -u mindoc -p -h 172.18.1.76Enter password:
mysql> create table temp(id int,name varchar(32));Query OK, 0 rows affected (0.03 sec)
mysql> insert into temp values(1,'aa');Query OK, 1 row affected (0.01 sec)
mysql> insert into temp values(2,'aa');Query OK, 1 row affected (0.01 sec)
mysql> insert into temp values(3,'aa');Query OK, 1 row affected (0.01 sec)
mysql> delete from temp;Query OK, 3 rows affected (0.01 sec)

通过执行的 delete 语句与大概执行时间，确定是哪个用户连接（ thread_id=130 ）

[root@db ~]# tail -35 audit.log# at 49003#191120 13:02:18 server id 76  end_log_pos 49080 CRC32 0x73dc1dda     Query    thread_id=130    exec_time=0    error_code=0SET TIMESTAMP=1574226138/*!*/;BEGIN/*!*/;# at 49080#191120 13:02:18 server id 76  end_log_pos 49135 CRC32 0x360e7fe4     Table_map: `mindoc_db`.`temp` mapped to number 249# at 49135#191120 13:02:18 server id 76  end_log_pos 49194 CRC32 0xbbf0d78f     Delete_rows: table id 249 flags: STMT_END_F
BINLOG '2sjUXRNMAAAANwAAAO+/AAAAAPkAAAAAAAEACW1pbmRvY19kYgAEdGVtcAACAw8CgAAD5H8ONg==2sjUXSBMAAAAOwAAACrAAAAAAPkAAAAAAAEAAgAC//wBAAAAAmFh/AIAAAACYWH8AwAAAAJhYY/X8Ls='/*!*/;### DELETE FROM `mindoc_db`.`temp`### WHERE###   @1=1###   @2='aa'### DELETE FROM `mindoc_db`.`temp`### WHERE###   @1=2###   @2='aa'### DELETE FROM `mindoc_db`.`temp`### WHERE###   @1=3###   @2='aa'# at 49194#191120 13:02:18 server id 76  end_log_pos 49225 CRC32 0x277ece0b     Xid = 23721COMMIT/*!*/;SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;DELIMITER ;# End of log file/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

通过 ConnUser 字段可以看到是 172.18.1.99 这个地址使用 mindoc@’%’ 用户连接的 MySQL 数据库。

mysql> select * from auditdb.accesslog where Connectionid=130;+----+--------------+--------------------+-----------+---------------------+| id | Connectionid | ConnUser           | MatchUser | Logintime           |+----+--------------+--------------------+-----------+---------------------+|  1 |          130 | [email protected] | mindoc@%  | 2019-11-20 12:59:21 |+----+--------------+--------------------+-----------+---------------------+1 row in set (0.00 sec)