Mysql 的逻辑备份恢复案例

2021-02-16 | 129 浏览

Mysql的逻辑备份恢复案例

1.Mysql的客户端mysqldump

1.自带工具mysqldump
#客户端参数
-u -p -h -S -P  
#备份参数：  
-A  -B  
#备份附加参数：
-R  
--triggers  
--events 
--master-data=2
--single-transaction
扩展:
--set-gtid-purged=0
1.常规备份需要添加
2.拿备份来恢复从库时,不需要加
mysqlbinlog 	--skip-gtids
2.压缩备份并添加时间戳
2.1例子：
mysqldump -uroot -p123 -A  -R  --triggers --master-data=2  --single-transaction|gzip > /backup/full_$(date +%F).sql.gz
mysqldump -uroot -p123 -A  -R  --triggers --master-data=2  --single-transaction|gzip > /backup/full_$(date +%F-%T).sql.gz
3.mysqldump备份的恢复方式（在生产中恢复要谨慎，恢复会删除重复的表）
set sql_log_bin=0;
source /backup/full_2018-06-28.sql
gip压缩工具
gunzip full_2018-10-17-11\:34\:51.sql.gz 
gzip -d full_2018-10-17.sql.gz 
4.注意

1、mysqldump在备份和恢复时都需要mysql实例启动为前提。

2、一般数据量级100G以内，大约15-30分钟可以恢复，数据量级很大很大的时候（PB、EB）

3、mysqldump是覆盖形式恢复的方法。

一般我们认为，在同数据量级，物理备份要比逻辑备份速度快.

4.1逻辑备份的优势:

1、可读性强
2、压缩比很高

2.企业故障恢复案例

1.背景环境：

正在运行的网站系统，mysql-5.7.20 数据库，数据量50G，日业务增量1-5M。

2.备份策略：

每天23:00点，计划任务调用mysqldump执行全备脚本

3.故障时间点：

年底故障演练:模拟周三上午10点误删除数据库，并进行恢复.

7.4 思路：
1、停业务，避免数据的二次伤害
2、找一个临时库，恢复周三23：00全备
3、截取周二23：00 --- 周三10点误删除之间的binlog，恢复到临时库
4、测试可用性和完整性
5、方法一：直接使用临时库顶替原生产库，前端应用割接到新库
6、方法二：将误删除的表导出，导入到原生产库
7、开启业务

处理结果：经过20分钟的处理，最终业务恢复正常

3.故障模拟演练

1.准备数据：

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

1.清理backup目录

rm -rf /backup/*

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

3.模拟周二 23：00到周三 10点之间数据变化

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

4.模拟故障,删除表(只是模拟，不代表生产操作)

drop database backup;

2.恢复过程
1.准备临时数据库（多实例3307）

systemctl start mysqld3307

2.准备备份
2.1准备全备：

cd /backup
gunzip full_2018-10-17.sql.gz

2.3截取二进制日志

mysqlbinlog --skip-gtids --include-gtids='3ca79ab5-3e4d-11e9-a709-000c293b577e:6-7' /data/binlog/mysql-bin.000036 >/backup/bin.sql

3.恢复备份到临时库

mysql -S /data/3307/mysql.sock

db01-[wuqun]>set sql_log_bin=0;
db01-[wuqun]>source /backup/full_2018-10-17.sql
db01-[wuqun]>source /backup/bin.sql

4.将故障表导出并恢复到生产

mysqldump   -S /data/3307/mysql.sock backup t1 >/backup/t1.sql

mysql -uroot -p123 
set sql_log_bin=0
use backup 
source /backup/t1.sql;

4.备份时优化参数

max_allowed_packet最大的数据包大小
mysqldump -uroot -p123 -A  -R  --triggers --set-gtid-purged=OFF --master-data=2 max_allowed_packet=128M  --single-transaction|gzip > /backup/full_$(date +%F).sql.gz
增加key_buffer_size(临时表有关)
分库分表并发备份
架构分离,分别备份(架构拆分,分布式备份)

数据库