MySQL5.7 恢复delete数据

---

前提：开启binlog,关于如何开启这里就不多说了。
MySQL8操作原理一样，只是mysql需要独立安装mysqlbinlog命令。因为MySQL8默认是没有自带这个命令的。

1、创建测试表

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1004 DEFAULT CHARSET=utf8mb4;

2、模拟插入2条数据

INSERT INTO `user` (`id`, `name`) VALUES (1, 'shouzhi1');
INSERT INTO `user` (`id`, `name`) VALUES (2, 'shouzhi2');

3、模拟全表删除

DELETE from user;

4、查看binlog日志

–start-datetime binlog开始过滤的起始时间。记住上面删数据操作的大概时间。

• 查看有哪些binlog日志。
  

• 查看mysql_bin.000006

mysqlbinlog -vv --start-datetime='2024-03-27 16:46:19'  mysql_bin.000006

从上图可以得到：

–start-position= 18893

–stop-position= 19006

5、获取待恢复的有效日志

• position：大家可以理解为每次binlog记录都会分配一个递增Id从而来标识事件。
• 起始position 结束position

mysqlbinlog -vv --start-position=18893 --stop-position=19006 mysql_bin.000006 | grep ^"###" > test_bin_data

执行以上命令获取：test_bin_data文件。内容如下：

root@fc7fc836f0f4:/var/lib/mysql# cat test_bin_data 
### DELETE FROM `duan`.`user`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='shouzhi1' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
### DELETE FROM `duan`.`user`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='shouzhi2' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */

如果日志内容跨度比较大，起始和结束position不好找，可以用一下方式来过滤头和尾部。

查看起始：--start-position
mysqlbinlog -vv --start-datetime='2018-11-04 15:18:00' on.000004| head -1000 |more

查看尾部：--stop-position
mysqlbinlog -vv --start-datetime='2018-11-04 15:18:00' --stop-datetime='2018-11-04 15:20:00' on.000004| tail -1000 |more

6、将test_bin_data文件还原成INSERT

将以上获取的有效binlog文件：test_bin_data转换成insert待还原语句：test_bin.sql

cat test_bin_data | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' |sed -r 's/(@2.*),/\1;/g' | sed 's/@[1-9]=//g' | sed 's/@[1-9][0-9]=//g' > test_bin.sql

• 注意：
  上面的's/(@2.*),/\1;/g'中的2表示当前恢复的表的字段数量，当前我操作的表就2个字段所以这里写的是2，以实际字段为准。

• 执行以上语句获得：test_bin.sql文件，内容如下。

INSERT INTO `duan`.`user`
SELECT
 1 ,
 'shouzhi1' ;
INSERT INTO `duan`.`user`
SELECT
 2 ,
 'shouzhi2' ;

7、恢复test_bin.sql

• 方式1
  
  

• 方式2

-D ： 数据库名，这里就是duan数据库名。
mysql -h127.0.0.1 -P3306 -Dduan -uroot -p'your_password' -e "source /var/lib/mysql/test_bin.sql;"