通过延迟从库来恢复MySQL数据【转】

在前面，我们讲了，通过创建一个临时从库，再把数据同步到误操作的前一个事务，来恢复误删除的数据，可以点击跳转。

但是临时准备一套从库，会多花费很多时间，那有没有更快的办法呢？

这一篇文章，就讲一下通过延迟从库，来恢复误删除的数据。

比如平时这个从库都是延迟主库1小时，当主库出现误操作，从库直接同步到误操作前一个事务，这样从库的数据就是误操作前一刻的数据。

这样，再把数据导入到之前误操作的主库，完成恢复。

我们来开始实验。

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

传到从库

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

关闭从库的MySQL实例

/etc/init.d/mysql.server stop

清空目标实例数据目录和事务日志目录：

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

并把全备恢复到新的 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/etc/init.d/mysql.server start

登录从库MySQL

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

其中，master_delay表示从库延迟的秒数。

2 主库写入测试数据

create database recover1;use recover1;
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);

3 模拟主库误操作

drop database recover1;

4 确定误操作的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这个文件，搜索drop关键字，内容如下：

那误操作事务的GTID就是：3e58c925-b396-11ed-9d79-000c2965ac6b:14524559。

5 配置同步到误操作前一个事务

从库执行

stop slaveCHANGE MASTER TO MASTER_DELAY = 0;
start slave sql_thread  until sql_before_gtids='3e58c925-b396-11ed-9d79-000c2965ac6b:14524559';start slave io_thread;

也就是把从库同步到主库误操作前一个事务。

这样，数据就能恢复到误操作前一刻了。

6 导出误操作的数据在主库恢复

在从库备份主库误删除的库，操作如下：

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

再到原来的实例，确定recover库是没有的，如果没有，则导入从库传过来的备份：

mysql -uroot -p <recover1.sql

7 确定数据是否恢复

再来查询误删除库recover1里表的数据：

select * from recover1.test_recover;

如果能查到(1,1),(2,2)这两行数据，说明通过延迟从库恢复数据成功。

转自

通过延迟从库来恢复MySQL数据
https://mp.weixin.qq.com/s/nv8rvmkKSSR6kmAYLoEkkw