二十九、mysqldump恢复案例

故障模拟恢复案例1

1、创建测试数据库

create database test;
use test;
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;

2、模拟在周二 23:00进行全备份

$ mysqldump -uroot -p123 -A  -R  --triggers --set-gtid-purged=OFF \ 
--master-data=2  \
--single-transaction|gzip > /backup/full_$(date +%F).sql.gz

参考资料：关于--set-gtid-purged参数

3、模拟周二23:00到周三10点之间的数据变化

use test;
insert into t1 values(11),(22),(33);
commit;
create table t2 (id int);
insert into t2 values(11),(22),(33);
commit;

4、故障场景模拟，开发不满上级压迫删库跑路

drop database test; 

因为是全备，只是test库被删除，如果直接恢复生产库可能会恢复很长时间，可以先在测试库恢复，再将恢复后的故障的表导出，再导入生产库即可

$ cd /backup/
$ gunzip full_2021-03-16.sql.gz

#大致检查备份是否完整，开头注释，sql语句等等
$ cat full_2021-03-16.sql | more

5、找到binlog起点跟终点方便截取日志进行恢复
找到全备记录的MASTER_LOG_POS号，这是记录的是全备后的binlog起点

找到终点

#终点为删库上一句的Pos号，这里假设是1519
mysql> show binlog events in 'mysql-bin.000002';

#开始截取二进制日志
$ mysqlbinlog --skip-gtids   --start-position=753 \
--stop-position=1519  /data/binlog/mysql-bin.000002 >/backup/bin.sql

#检查一下截取内容，是否在出故障前
$ vim bin.sql

6、恢复备份到临时库

#启动测试数据库
$ mysql -uroot -p456 -S /data/3307/mysql.sock
#关闭二进制日志记录
mysql> set sql_log_bin=0;
#恢复全备
mysql> source /backup/full_2021-03-16.sql
#恢复binlog数据
mysql> source /backup/bin.sql
mysql> set sql_log_bin=1;

7、最后一步，检查恢复后的表，没问题后将其导入生产库即可

#从测试库中导出出故障的数据库
$ mysqldump -S /data/3307/mysql.sock -B  test>/backup/bak.sql
#登录生产库
mysql -uroot -p123 
set sql_log_bin=0
source /backup/bak.sql;
set sql_log_bin=1；

故障模拟恢复案例2

使用GTID进行故障恢复

1、创建测试数据，此处具体命令同上一个案例

1、创建一个数据库 student
2、在student下创建一张表t1
3、插入5行任意数据
4、全备
5、插入两行数据，任意修改3行数据，删除1行数据
6、删除所有数据
7、再t1中又插入5行新数据，修改3行数据

需求，跳过第六步恢复表数据
注意每步操作之后都要comint一下，这样才能记录到GTID事务中

在第4步，全备后打开备份文件，记下GTID_PURGED，此时全备后的GTID号为1-11，也就是说使用此备份能恢复到1-11号事务，所以接下来恢复应从12号事务开始

从12号事务开始，跳过第6步删库操作导出binlog

#假设找到12-17号事务是第5步到第7步的操作，15号事务是删除操作
mysql> show binlog events in 'mysql-bin.000002';

#截取binlog，排除掉删库事务
$ mysqlbinlog --skip-gtids --include-gtids='aa231345-55e1-769f-000c2411b4b:12-17' \
--exclude-gtids='aa231345-55e1-769f-000c2411b4b:15' \
/data/binlog/mysql-bin.000002 > /backup/bin.sql

之后再进行恢复即可。

学习来自：郭老师博客，老男孩深标DBA课程 第七章 备份恢复-1