MySQL删库不用跑路了

悦专栏 2023-08-10

350

在前面，我们讲了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/backup
xtrabackup --defaults-file=/data/mysql/conf/my.cnf -uu_xtrabackup -p'Ijnbgt@123' --backup --stream=xbstream --target-dir=./ >/data/backup/xtrabackup.xbstream

2 写入增量并模拟误操作

模拟增量数据写入

use recover
insert 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/* -rf
rm 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线程

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.sql
scp recover.sql 192.168.12.161:/data/backup/

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

mysql -uroot -p <recover.sql

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

select * from recover.test_recover;

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

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

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

欢迎关注公众号

回复“高可用”，获取主流高可用方案；

回复“AI”，获取AI结合数据库的相关内容；

回复“社群”，加入MySQL交流群。

文章转载自悦专栏，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL删库不用跑路了

评论