通过复制来恢复误删的库
在前面，我们讲了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行，说明恢复成功。
到这里，整个恢复过程就完成了。
当然，这种数据恢复方案，还是挺耗时间的，在后面，我们会再写一篇恢复文章，通过延迟从库来恢复数据，这种方式，恢复速度会快很多。
发表评论