MySQL DBA培训: 备份恢复—老男孩培训机构

    /    2019-07-23

1. 运维在数据库备份恢复方面的职责

1.1 设计备份策略

全备  、增量、时间、自动

1.2 日常备份检查

备份存在性
备份空间够用否

1.3 定期恢复演练(测试库)

一季度 或者 半年

1.4 故障恢复

通过现有备份,能够将数据库恢复到故障之前的时间点. 

1.5 迁移

1. 停机时间
2. 回退方案 

2. 备份类型

2.1 热备

在数据库正常业务时,备份数据,并且能够一致性恢复(只能是innodb)
对业务影响非常小

2.2 温备

锁表备份,只能查询不能修改(myisam)
影响到写入操作

2.3 冷备

关闭数据库业务,数据库没有任何变更的情况下,进行备份数据.
业务停止

3. 备份方式及工具介绍

3.1 逻辑备份工具

基于SQL语句进行备份
mysqldump       *****
mysqlbinlog     *****

3.2 物理备份工具

基基于磁盘数据文件备份
xtrabackup(XBK) :percona 第三方   *****
MySQL Enterprise Backup(MEB)

4. 逻辑备份和物理备份的比较

4.1 mysqldump (MDP)

优点:
1.不需要下载安装
2.备份出来的是SQL,文本格式,可读性高,便于备份处理
3.压缩比较高,节省备份的磁盘空间
缺点:
4.依赖于数据库引擎,需要从磁盘把数据读出
然后转换成SQL进行转储,比较耗费资源,数据量大的话效率较低
建议:
100G以内的数据量级,可以使用mysqldump
超过TB以上,我们也可能选择的是mysqldump,配合分布式的系统
1EB  =1024 PB =1000000 TB

4.2 xtrabackup(XBK)

优点:
1.类似于直接cp数据文件,不需要管逻辑结构,相对来说性能较高
缺点:
2.可读性差
3.压缩比低,需要更多磁盘空间
建议:
>100G

5.备份策略

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

6.备份工具使用-mysqldump

6.1 mysqldump (逻辑备份的客户端工具)

6.1.1 客户端通用参数

-u  -p   -S   -h  -P    
本地备份:
mysqldump -uroot -p  -S /tmp/mysql.sock
远程备份:
mysqldump -uroot -p  -h 10.0.0.51 -P3306

6.1.2 备份专用基本参数

-A 全备参数

-例子1:
[root@db01 ~]# mkdir -p /data/backup
mysqldump -uroot -p -A >/data/backup/full.sql
Enter password: 
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
# 补充:
# 1.常规备份是要加 --set-gtid-purged=OFF,解决备份时的警告
# [root@db01 ~]# mysqldump -uroot -p123 -A  --set-gtid-purged=OFF  >/backup/full.sql
# 2.构建主从时,做的备份,不需要加这个参数
# [root@db01 ~]# mysqldump -uroot -p123 -A    --set-gtid-purged=ON >/backup/full.sql

-B db1 db2 db3 备份多个单库

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

备份单个或多个表

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

6.1.3 高级参数应用

特殊参数1使用(必须要加)

-R            备份存储过程及函数
--triggers  备份触发器
-E             备份事件
例子4:
[root@db01 backup]# mysqldump -uroot -p -A -R -E --triggers >/data/backup/full.sql
(5) 特殊参数2使用

-F 在备份开始时,刷新一个新binlog日志

例子5:
mysqldump -uroot -p  -A  -R --triggers -F >/bak/full.sql

--master-data=2

以注释的形式,保存备份开始时间点的binlog的状态信息
mysqldump -uroot -p  -A  -R --triggers --master-data=2   >/back/world.sql
[root@db01 ~]# grep 'CHANGE' /backup/world.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000035', MASTER_LOG_POS=194;
功能:
(1)在备份时,会自动记录,二进制日志文件名和位置号
0 默认值
1  以change master to命令形式,可以用作主从复制
2  以注释的形式记录,备份时刻的文件名+postion号
2) 自动锁表
3)如果配合--single-transaction,只对非InnoDB表进行锁表备份,InnoDB表进行“热“”备,实际上是实现快照备份。

--single-transaction

innodb 存储引擎开启热备(快照备份)功能       
master-data可以自动加锁
1)在不加--single-transaction ,启动所有表的温备份,所有表都锁定
1)加上--single-transaction ,对innodb进行快照备份,对非innodb表可以实现自动锁表功能
例子6: 备份必加参数
mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql

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

6.2 小练习:

6.2.1. 实现所有表的单独备份

提示:
information_schema.tables
mysqldump -uroot -p123 world city >/backup/world_city.sql
select concat("mysqldump -uroot -p123 ",table_schema," ",table_name," --master-data=2 --single-transaction --set-gtid-purged=0  -R -E --triggers>/backup/",table_schema,"_",table_name,".sql"from information_schema.tables where table_schema not in ('sys','information_schema','performance_schema')
;

6.2.2.模拟故障案例并恢复

1)每天全备
2)binlog日志是完整
3)模拟白天的数据变化
4)模拟下午两点误删除数据库
需求: 利用全备+binlog回复数据库误删除之前。
故障模拟及恢复:
1. 模拟周一23:00的全备
mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql
2. 模拟白天的数据变化
Master [(none)]>create database day1 charset utf8;
Master [(none)]>use day1
Master [day1]>create table t1(id int);
Master [day1]>insert into t1 values(1),(2),(3);
Master [day1]>commit;
Master [world]>update city set countrycode='CHN';
Master [world]>commit;
模拟磁盘损坏:
[root@db01 data]# \rm -rf /data/mysql/data/*
3. 恢复故障
[root@db01 data]# pkill mysqld
[root@db01 data]# \rm -rf /data/mysql/data/*
4. 恢复思路
1.检查备份可用性
2.从备份中获取二进制日志位置
3.根据日志位置截取需要的二进制日志
4.初始化数据库,并启动
5.恢复全备

(0)

分享至