mysql：通过复制来恢复误删的库

在前面，我们讲了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);

创建备份用户

1 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`;

在源实例进行全量备份

1	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 写入增量并模拟误操作

模拟增量数据写入

1	use recoverinsert into test_recover values (3,3);

模拟误操作删库

1	drop database recover;

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

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

把全备传到机器R上。

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

关闭R上的MySQL实例

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

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

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

cd /data/backup/recover
xbstream -x < xtrabackup.xbstream
xtrabackup --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 to
master_host='192.168.12.161',
master_user='repl',
master_password='Uid_dQc63',
master_auto_position = 1;

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

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

不对，搞串了~~

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

4. 确定误操作事务的GTID

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

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

cd /data/mysql/binlog
cp 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线程

1	start slave io_thread;

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

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

再来查看复制状态

1	show slave status\G

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

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

1	select * from recover.test_recover;

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

再清空复制关系

1 2	stop slave; reset slave;

6. 数据恢复

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

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

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

1	mysql -uroot -p <recover.sql

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

1	select * from recover.test_recover;

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