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

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

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

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

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

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

我们来开始实验。

 

1 配置延迟从库

在原实例进行全量备份

cd /data/backup
xtrabackup --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/* -rf
rm /data/mysql/binlog/* -rf

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

 

登录从库MySQL

stop slave;
reset slave;

change master to
master_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/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这个文件，搜索drop关键字，内容如下：

 

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

 

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

从库执行

stop slave
CHANGE 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.sql
scp 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