使用mysqlbinlog 回滚误删的数据

模拟环境，这里使用docker 进行测试 mysql 版本为5.7，
注意：这里的测试是使用了docker-compose 进行构建容器的 ，

需要下载 docker 和 docker-compose 

环境搭建

这里使用docker-compose 进行数据库的搭建

# 编写 docker-compose.yml 文件
version: '3'
services:
  mysql:
    image: mysql:5.7
    container_name: mysql
    restart: always
    environment:
      MYSQL_USER: admin
      MYSQL_PASSWORD: admin
      MYSQL_ROOT_PASSWORD: root123
      MYSQL_DATABASE: test
    volumes:
      - ./my.cnf:/etc/mysql/conf.d/my.cnf
      - /home/mysql5.7/data:/var/lib/mysql
    ports:
      - 3305:3306

编写mysql的 my.cnf 配置文件，在里面设置开启binlog日志

[mysqld]
log-bin=mysql-binlog
binlog-format=row
binlog-row-image=full
expire-logs-days=7
server-id=1

编写完成之后我们就有了两个文件

# 启动构建容器
docker-compose up -d  

# 下面是成功示例

开始模拟数据测试

# 进入数据库
[root@localhost mysql5.7]# docker exec -it f370c57ad343 mysql -uroot -proot123

# 选择数据库
mysql> use test;

# 创建一张表IAO
CREATE TABLE `demo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

# 新增两条数据
insert into demo values(null,"zhangsan",11),(null,"lisi",23);

 数据有了现在 开始模拟误删操作

# 删除整张表
mysql> drop table demo;
# 测试查询是否还能查到这个表 ，如果报错提示 ERROR 1146 (42S02): Table 'test.demo' doesn't exist 则代表删除成功
mysql> select * from demo;
# 查看mysql 当前binlog 日志 是写入 哪个binlog日志中 我这里是   mysql-binlog.000005
mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-binlog.000005
         Position: 1146
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

ERROR: 
No query specified
# 然后因为用于操作 mysql-binlog.000005 方便并且不影响 数据库的操作，我们刷新一下binlog日志的日志写入 文件
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
# 在查看一下 写入的文件是哪个 这里是： mysql-binlog.000006 说明刷新成功
mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-binlog.000006
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

ERROR: 
No query specified

 退出容器去解析binlog日志

# 退出
mysql> exit
Bye
您在 /var/spool/mail/root 中有新邮件
# 进入容器
[root@localhost mysql5.7]# docker exec -it f370c57ad343 bash
# 进入mysql binlog 日志文件目录下
root@f370c57ad343:/# cd /var/lib/mysql
# 查看目录下的文件
root@f370c57ad343:/var/lib/mysql# ls

# 使用命令查看 binlog文件 
root@f370c57ad343:/var/lib/mysql# mysqlbinlog -v mysql-binlog.000005 

#然后去找到我们 创建表时候的那个操作节点
=======================================================
# at 401
#230519  8:14:05 server id 1  end_log_pos 678 CRC32 0xe7022511 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1684484045/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
CREATE TABLE `demo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
/*!*/;

=========================================================
# 找到这个创建表的sql， 然后我们可以发现这个操作的节点是  401   # at 401  记住这个起始的节点

# 接下来 我们要找到的就是 误删表的那个sql 那个节点
=====================================================
# at 964
#230519  8:16:51 server id 1  end_log_pos 1029 CRC32 0xe405cef8 	Anonymous_GTID	last_committed=3	sequence_number=4	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 1029
#230519  8:16:51 server id 1  end_log_pos 1146 CRC32 0xe12f24c6 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1684484211/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
DROP TABLE `demo` /* generated by server */

===================================================
# 然后我们找到了这个误删的sql节点， 这个节点是 1029 ，但是我们要取删除节点往上一个 也就是 964  作为我们的截止节点 # at 964

 

 开始截取binlog日志去进行数据恢复

# 截取数据 mysqlbinlog binlog文件  --start-position=开始节点 --stop-position=截止节点 -vv > 输出的文件名

root@f370c57ad343:/var/lib/mysql# mysqlbinlog mysql-binlog.000005  --start-position=401 --stop-position=964 -vv > ./rollback 
# 登录数据库
root@f370c57ad343:/var/lib/mysql# mysql -uroot -proot123
# 执行刚刚截取的文件
mysql> source /var/lib/mysql/rollback

 到这里数据已经恢复成功了。