day08-02-备份恢复之逻辑备份

备份恢复

在备份恢复中的职责
1.1 备份策略的设计

备份方式：
全备:全库备份，备份所有数据
增量:备份变化的数据
逻辑备份=mysqldump+mysqlbinlog
物理备份=xtrabackup_full+xtrabackup_incr+binlog或者xtrabackup_full+binlog
备份周期:
根据数据量设计备份周期
比如：周日全备，周1-周6增量

(1) 备份周期:
根据数据量.

60G:

9T：每周做全备

(2)备份工具:
mysqldump (MDP) , XBK (PBK) percona Xtrabackup , MEB(MySQL Enterprise BACKUP MEB) ,mysqlbinlog
(3)备份方式:
逻辑:
全备 mysqldump
增量 binlog (flush logs ,cp)
物理备份:
全备 : XBK
增量 : XBK
1.2 检查备份可用性
crontab -l ---->先看有没有备份策略
备份脚本 ---->
备份路径 ---->
查看备份日志 ---->
检查备份文件(大小，内容) ---->

1.3 定期的恢复演练

1.4 数据恢复
只要备份和日志是完整的,恢复到故障之前的时间点(快速)

1.5 数据迁移 ***
操作系统不同的迁移
mysql -> mysql
其他 -> mysql
mysql -> 其他

备份的介绍

2.1 备份的策略
2.2 备份的工具
2.3 备份类型
热备 : 对于业务影响最小 InnoDB
温备 : 长时间锁表备份 MyISAM
冷备 : 业务关闭情况下备份

mysqldump

优点：
1.不需要下载安装
2.备份出来的是SQL，文本格式，可读性高,便于备份处理
3.压缩比较高，节省备份的磁盘空间

缺点：
4.依赖于数据库引擎，需要从磁盘把数据读出
然后转换成SQL进行转储，比较耗费资源，数据量大的话效率较低
建议：
100G以内的数据量级，可以使用mysqldump
超过TB以上，我们也可能选择的是mysqldump，配合分布式的系统
1EB  =1024 PB =1000000 T

3.1 连接数据库
-u
-p
-S
-h
P

3.2 基础备份参数

-A (all database)

mysqldump -uroot -p123 -A > /data/backup/db_name-full.sql
mysqldump -uroot -p123 -A  >/backup/full.sql

# 把握一个原则，如果对库做备份，就加-B参数。

# 对表做备份，就不要加-B参数，写表名

-B db1 db2 db3

说明：生产中需要备份，生产相关的库和MySQL库
例子2 :
mysqldump -B mysql gtid --set-gtid-purged=OFF >/data/backup/b.sql

库表

mysqldump -uroot -p123 -B simon_test world > /data/backup/db_name-full.sql

例子3 world数据库下的city,country表
mysqldump -uroot -p world city country >/backup/bak1.sql
以上备份恢复时:必须库事先存在,并且ues才能source恢复

第一个是库名，后面只能是表名

mysqldump -uroot -p123 world tb_name1 tb_name2 tb_name3 > /data/backup/db_name-tbname.sql

只写库名，不加-B参数，会把这个库下所有表都逐个备份

mysqldump -uroot -p123 world > /data/backup/db_name-tb_name-all.sql

3.3 特殊备份参数

-R 备份存储过程和函数
-E 备份event事件
--triggers 触发器

-F, --flush-logs 刷新日志

--master-data=2 ******
每天全备 mysqldump 60G
周三，下午2点，数据库损坏
恢复思路？
1.恢复周二的全备。
2.截取全备恢复到损坏之前的binlog

起点=》怎么找？要找开始备份那个点
终点=》故障点之前的position

3.恢复binlog

--master-data=2

两个作用：

1）备份语句添加 --master-data=2 ，开启备份，会立即记录开始备份的起点position。也就是说这个参数是用作截取二进制日志备份起点位置用的。

2）自动锁表

    `--master-data=2`   在不添加    `--single-transaction`  的情况下，就会全局锁表进行温备，可能会阻塞其他正常修改更新业务 。 

	添加  `--sigle-transaction`   ，对于InnoDB表，是不锁表备份（快照备份）。非InnoDB的表，还是会锁表进行温备。不锁表只针对有事务功能的存储引擎。

--single-transaction ******

一句话总结：对于InnoDB的表，通过事务进行一致性快照备份，不锁表。又称为热备。

只适用于存储在支持多版本化的存储引擎中的表(目前只适用于InnoDB)

我们公司备份时会加两个核心参数

--master-data=2 和 --single-transaction

所谓“热备”功能，就是--single-transaction实现的。核心理念就是，备份的时候，创建一个单独的事务，然后保存当前备份时候的一致性快照，实际备份的是快照中的数据，所以不会阻塞其他事务的变更。俗称的“热备”，实际是基于MVCC机制通过快照技术实现的，目前只有Innodb具备这个功能。

(1) 记录备份时刻的binlog信息
(2) 自动锁表
不加--single-transaction ,温备份
加了--single-transaction,对于InnoDB表不锁表备份(快照备份)

--single-transaction *****
对于InnoDB的表,进行一致性快照备份,不锁表.

--set-gtid-purged=auto

auto , on
off 
使用场景:
1. --set-gtid-purged=OFF,可以使用在日常备份参数中.
mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql
2. auto , on:在构建主从复制环境时需要的参数配置
mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=ON >/data/backup/full.sql

--max-allowed-packet=#

mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M >/data/backup/full.sql

 --max-allowed-packet=# 
The maximum packet length to send to or receive from server.

压缩备份并添加时间戳

例子：
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

mysqldump备份的恢复方式（在生产中恢复要谨慎，恢复会删除重复的表）
set sql_log_bin=0;
source /backup/full_2018-06-28.sql

注意：
1、mysqldump在备份和恢复时都需要mysql实例启动为前提。
2、一般数据量级100G以内，大约15-45分钟可以恢复，数据量级很大很大的时候（PB、EB）
3、mysqldump是覆盖形式恢复的方法。

一般我们认为，在同数据量级，物理备份要比逻辑备份速度快.
逻辑备份的优势:
1、可读性强
2、压缩比很高



InnoDB 引擎和 MyisAM引擎 分开备份
1）Myisam引擎：
mysqldump -uroot -p123456 -A -B --master-data=1 -x| gzip > /data/all_$(date +%F).sql.gz

（2）InnoDB引擎：
mysqldump -uroot -p123456 -A -B  --master-data=1 --single-transaction > /data/bak.sql

（3）生产环境DBA给出的命令
a、for MyISAM
mysqldump --user=root --all-databases --flush-privileges --lock-all-tables \
--master-data=1 --flush-logs --triggers --routines --events \
--hex-blob > $BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql

b、for InnoDB
mysqldump --user=root --all-databases --flush-privileges --single-transaction \
--master-data=2 --flush-logs --triggers --routines --events \
--hex-blob > $BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql

恢复案例

4.1 背景环境：
正在运行的网站系统，mysql-5.7.20 数据库，数据量50G，日业务增量1-5M。
4.2 备份策略：
每天23:00点，计划任务调用mysqldump执行全备脚本
4.3 故障时间点：
年底故障演练:模拟周三上午10点误删除数据库.
4.4 思路：
1、停业务，挂维护页,避免数据的二次伤害
2、找一个临时库，恢复周二23：00全备
3、截取周二23：00 --- 周三10点误删除之间的binlog，恢复到临时库
4、测试可用性和完整性
5、
5.1 方法一：直接使用临时库顶替原生产库，前端应用割接到新库
5.2 方法二：将误删除的表导出，导入到原生产库
6、开启业务
处理结果：经过20分钟的处理，最终业务恢复正常

4.5 故障模拟演练
4.5.1 准备数据
create database backup;
use backup
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;
rm -rf /backup/*

4.5.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

4.5.3 模拟周二 23：00到周三 10点之间数据变化
use backup
insert into t1 values(11),(22),(33);
commit;
create table t2 (id int);
insert into t2 values(11),(22),(33);
commit;

4.5.4 模拟故障,删除表(只是模拟，不代表生产操作)
drop database backup;

4.6 恢复过程
4.6.1 准备临时数据库（多实例3307）
systemctl start mysqld3307
4.6.2 准备备份
（1）准备全备：
cd /backup
gunzip full_2018-10-14.sql.gz
（2）截取二进制日志
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=753;
753
1519

mysqlbinlog --skip-gtids   --start-position=753 --stop-position=1519  /data/binlog/mysql-bin.000002 >/backup/bin.sql

4.6.3 恢复备份到临时库

mysql -S /data/3307/mysql.sock
set sql_log_bin=0;
source /backup/full_2019-07-15.sql
source /backup/bin.sql

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

mysqldump   -S /data/3307/mysql.sock -B  backup  >/backup/bak.sql
mysql -uroot -p123 
set sql_log_bin=0
source /backup/bak.sql;

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

需求，跳过第六步恢复表数据

扩展参数 ***
在构建主从时,使用AUTO/ON
--set-gtid-purged=AUTO/ON

仅是做普通的本机备份恢复时,可以添加
--set-gtid-purged=OFF

SET @@GLOBAL.GTID_PURGED='aa648280-a6a6-11e9-949f-000c294a1b3b:1-11';

1-11 表示，该备份集已经包含 1-11事务

--max_allowed_packet=128M 控制的是备份时传输数据包的大小.

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

生产环境下完整备份语句

mysqldump -u user_name -p'xxxxxx' \
-S /data/3306/mysql.sock \
--single-transaction \
--master-data=2 \
--set-gtid-purged=OFF \
--hex-blob \
--opt 
--routines \
--triggers \
--all-databases > /backup_dir/mysqldump-20190326.sql

posted @ 2022-11-24 20:23 oldSimon 阅读(20) 评论(0) 编辑收藏举报

刷新页面返回顶部

Simon

day08-02-备份恢复之逻辑备份

mysqldump

--set-gtid-purged=auto

--max-allowed-packet=#

压缩备份并添加时间戳

生产环境下完整备份语句

公告