在前面，我们讲了MySQL执行delete误删除数据恢复。但是这种方式没办法恢复drop相关的误操作。

这一节内容，我们来聊一下，基于复制的数据恢复方法。delete、update、drop等误操作，都可以恢复。

大致过程是：

误操作后，把上一次全备导入到新的MySQL，再把这个新的MySQL配置成误操作数据库的从库。
然后让SQL线程同步到误操作的前一个事务，这样，从库的数据就是误操作前一刻的数据了，比如我们误删除了某个库，就把这个库的数据导回到原来的数据库，就能完成恢复。

1 准备阶段

新建测试库表并写入数据

create database recover;use recover;
CREATE TABLE test_recover (id int NOT NULL AUTO_INCREMENT,a int NOT NULL,PRIMARY KEY (id)) ENGINE=InnoDB CHARSET=utf8mb4;
insert into test_recover values (1,1),(2,2);

创建备份用户

CREATE USER `u_xtrabackup`@`localhost` IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'Ijnbgt@123';GRANT SELECT, RELOAD, PROCESS, SUPER, LOCK TABLES,BACKUP_ADMIN ON *.* TO `u_xtrabackup`@`localhost`;

在源实例进行全量备份

cd /data/backupxtrabackup --defaults-file=/data/mysql/conf/my.cnf -uu_xtrabackup -p'Ijnbgt@123' --backup --stream=xbstream --target-dir=./ >/data/backup/xtrabackup.xbstream

2 写入增量并模拟误操作

模拟增量数据写入

use recoverinsert into test_recover values (3,3);

模拟误操作删库

drop database recover;

3 为误操作的MySQL配置一套从库

在另外的机器R上准备一个新的MySQL实例，跟误操作的MySQL版本一致（平时建议是在每个机房为每一个版本准备一个临时用于恢复的MySQL实例）。

把全备传到机器R上。

scp xtrabackup.xbstream 192.168.12.162:/data/backup/recover

关闭R上的MySQL实例

清空R上MySQL实例的数据目录和Binlog目录

rm /data/mysql/data/* -rfrm /data/mysql/binlog/* -rf

并把全备恢复到R上的MySQL中

cd /data/backup/recoverxbstream -x < xtrabackup.xbstreamxtrabackup --prepare --target-dir=./xtrabackup --defaults-file=/data/mysql/conf/my.cnf --copy-back --target-dir=./chown -R mysql.mysql /data/mysql

启动R上的MySQL。

将R上的MySQL配置成原实例的从库，但不要开启复制

stop slave;
reset slave;
change master tomaster_host='192.168.12.161',master_user='repl',master_password='Uid_dQc63',master_auto_position = 1;

这里一定要注意：不要启动！不要启动！不要启动！

只要我们不启动，三体人就无法定位到地球的具体位置，地球便可以相安无事。如果启动了 , 三体人将会定位地球的位置并进行入侵，占领我们的地球，后果不堪设想。

不对，搞串了~~

因为启动复制，会同步主库的所有操作，也包括误操作，这样，这个从库的数据，还是没有误删除库的数据。

4 确定误操作事务的GTID

找到回档时间点对应的 Binlog 文件

通过下面命令确定误操作事务的GTID

cd /data/mysql/binlogcp mysql-bin.000065 /data/backup/cd /data/backup/mysqlbinlog mysql-bin.000065 --start-datetime='2023-07-31 22:00:00'  --stop-datetime='2023-07-31 22:50:00'  --base64-output=decode-rows -v  >/data/backup/1.sql

再来查看/data/backup/1.sql里的内容：

......SET @@SESSION.GTID_NEXT= '3e58c925-b396-11ed-9d79-000c2965ac6b:14524559'/*!*/;......use `martin`/*!*/;......DROP TABLE `recover` /* generated by server */......

说明误操作事务的GTID为：3e58c925-b396-11ed-9d79-000c2965ac6b:14524559。

5 R上的MySQL同步到误操作前一个事务

先启动IO线程

start slave io_thread;

再启动SQL线程到误操作前一个事务

start slave sql_thread  until sql_before_gtids='3e58c925-b396-11ed-9d79-000c2965ac6b:14524559';

再来查看复制状态

show slave status\G

如果IO线程是Yes，SQL线程是No。就表示复制已经同步到误操作前一个事务了。

当然，需要我们确定一下，当前数据是否为误操作之前那个时间点的数据

select * from recover.test_recover;

这个也可以找业务一起来确定一下。

再清空复制关系

stop slave;reset slave;

6 数据恢复

备份R机器MySQL的数据，再导入原实例。操作如下：

mysqldump -u'root' -p --set-gtid-purged=off -B recover >recover.sqlscp recover.sql 192.168.12.161:/data/backup/

再到原来的实例，确定recover库是没有的，导入误删除的库

mysql -uroot -p <recover.sql

确定数据是否恢复，可以找某张表进行确定，比如：

select * from recover.test_recover;

这个例子中，如果查询的数据有3行，说明恢复成功。

到这里，整个恢复过程就完成了。

当然，这种数据恢复方案，还是挺耗时间的，在后面，我们会再写一篇恢复文章，通过延迟从库来恢复数据，这种方式，恢复速度会快很多。