第七章备份恢复及迁移

运维的备份恢复相关职责

设计备份策略
- 备份周期
- 备份方式
- 备份内容
检查备份
- 备份文件大小
- 备份内容
定期恢复演练
故障恢复
升级、迁移

备份类型

热备 (hot backup)

不锁表备份，只能针对事务性引擎的表(例如：InnoDB).
对业务影响最小

冷备 (cold backup)

业务停止或数据库关闭，进行备份
对业务影响最大

温备 (warm backup)

锁表备份，只读备份。阻塞所有的变更操作
影响到写入操作(只能查询)

备份策略

全备

全库备份。

增量

备份变化的数据

逻辑备份

把数据从库中提出来保存为文本文件

mysqldump + binlog

物理备份

直接复制（归档）数据文件的备份方式

XKB_full + xbk_inc + binlog
或者
xtrabackup_full + binlog

备份周期

根据数据量设计备份周期
例如：周日全备，周1 - 周6 增量备份

备份工具

mysqldump

逻辑备份
优势：

文本格式(压缩比高)
备份出来都是SQL语句
可读性较强,便于二次处理。

劣势：

备份和恢复的过程比较慢
恢复时,如果目标库不存在，需要事先手工创建

Xtrabackup(percona)

物理备份
优势：

备份速度快
支持热备
自带增量备份功能

劣势：

备份的是数据文件
可读性较差，不便于二次处理

选择建议

数据小于100G 			 MDP、XBK
数据在100G - 1T  		XBK
数据超过TB级别   		XBK、MDP

mysqldum 逻辑备份工具使用

客户端通用的参数

-p	# 密码
-S	# sock文件
-h	# 连接地址
-P	#端口
-u	#用户名

备份专用基本参数

全库备份-A

mysqldump -uroot -p -A >/backup/full.sql

单库或多库备份-B

##  只备份world和test两个库
mysqldump -uroot -p123 -B world test >/backup/db.sql

单表或多表备份

## 备份单张表
mysqldump -uroot -p123 world city >/backup/city.sql

## 备份多张表  wolrd库下:city表 和 country表
mysqldump -uroot -p123 world city country >/backup/tab.sql

特殊功能参数

1）特殊参数

-R 			 ## 存储过程及函数
--triggers　  ## 触发器
-E　			## 事件

例:
mysqldump -uroot -p -A -R -triggers -E >/backup/full.sql

2）--master-data=2

说明：以注释形势保存备份开始时间点的binlog 状态信息

功能：
	1. 在备份是，自动记录 二进制日志文件名和位置号
	2. 自动锁表
	3. 配合--single-transaction，只对非InnoDB表进行锁表备份，InnoDB表进行热备，实际上是实现快照备份。

--master-data=[0|1|2]
	0 默认值
	1 以change master to命令形式，可以用作主从复制
	2 以注释的形式记录，备份时刻的文件名+postion号

3） --single-transaction

功能：能够对InnoDB存储引擎实现热备。
master-data
1）不加 --single-transaction 启动所有表的温备份，所有表锁定
2）加上 --single-transaction 对innodb进行快照备份,对非innodb表可以实现自动锁表功能

例:
mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql

4）--set-gtid-purged=OFF(GTID模式独有的参数)

作用,去除gtid所有信息,在日常备份恢复时可加.
做主从复制应用的时候,不能加此参数.

5）--max-allowed-packet=512M

mysql服务器端和客户端在一次传送数据包的过程中最大允许的数据包大小

一条完整的备份语句

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

企业故障恢复案例 (MDP)

背景环境：

正在运行的小型网站系统，mysql-5.7.20 数据库，数据量50G.
每天23:00点，计划任务调用mysqldump执行全备脚本

mysqldump -uroot -p123456 -A -E -R --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M |gzip > /backup/full_$(date +%F).sql.gz

故障时间点

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

恢复思路

1）停止故障业务，挂维护页
2）准备测试库，进行全库恢复
3）截取数据从全备开始一直到故障时刻的binlog
4）测试数据可用性和完整性
5）将故障数据导出，导入到生产
6）撤维护页，开启业务

故障演练

模拟故障现场

（1）模拟数据

mysql[(none)]>create database mdp charset utf8mb4;
mysql[(none)]>use mdp;
mysql[mdp]>create table t1(id int) engine=innodb charset=utf8mb4;
mysql[mdp]>insert into t1 values(11),(22),(33);
mysql[mdp]>commit;
mysql[mdp]>select * from t1;;
+------+
| id   |
+------+
|   11 |
|   22 |
|   33 |
+------+
3 rows in set (0.00 sec)

（2）模拟晚上23:00 全备

mysqldump -uroot -p123456 -A -E -R --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M |gzip >/backup/full_$(date +%F).sql.gz 

ls -l /backup/

-rw-r--r-- 1 root root 794255 Aug 17 21:51 full_2019-08-17.sql.gz

（3）模拟备份后数据变化

mysql[(none)]>use mdp;
mysql[mdp]>insert into t1 values(111),(222),(333);
mysql[mdp]>commit;
mysql[mdp]>select * from t1;
+------+
| id   |
+------+
|   11 |
|   22 |
|   33 |
|  111 |
|  222 |
|  333 |
+------+
6 rows in set (0.00 sec)

（4）模拟数据损坏

整个库被误删

mysql[mdp]>drop database mdp;

恢复故障准备

（1）停止故障业务，挂维护页
（2）准备备份

全库准备：

全备数据是压缩备份的，这里先将备份数据解压

cd /backup
gunzip full_2019-08-17.sql.gz

binlog准备：

获取到binlog的 pos 起始位置和 pos 结束位置，将数据导出。

# 获取使用binlog日志和pos起始号 数据在22行
sed  -n 22p /backup/full_2019-08-17.sql 

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1096;

# 获取到pos 结束号（找到drop数据命令那行的pos列）
mysql -uroot -p123456 -e 'show binlog events in "mysql-bin.000001" limit 7,10';

# 此时已经获取到了 使用的binlog文件和 pos的起始号和结束号
# 截取binlog记录的数据导出
mysqlbinlog  --skip-gtids  --start-position=1096 --stop-position=1357 /data/binlog/mysql-bin.000001 >/backup/binlog.sql

进行数据恢复

# 临时关闭binlog日志
mysql[(none)]>set sql_log_bin=0;
# 全备数据恢复
mysql[(none)]>source /backup/full_2019-08-17.sql
mysql[mdp]>select * from mdp.t1;
+------+
| id   |
+------+
|   11 |
|   22 |
|   33 |
+------+
# binlog 数据恢复
mysql[mdp]>source /backup/binlog.sql
mysql[mdp]>select * from mdp.t1;
+------+
| id   |
+------+
|   11 |
|   22 |
|   33 |
|  111 |
|  222 |
|  333 |
+------+

XBK(percona-Xtrabackup) - 物理备份工具

XBK 安装

安装依赖包

wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev

下载软件并安装

https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
yum -y install percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

介绍

物理备份工具，类似于CP数据

备份细节

非InnoDB：
	自动锁表备份，会有短暂的全局锁（例如MyISAM）
InnoDB：
	 立即进行CKPT，将当前所有已提交事务的脏页，立即刷写到磁盘
	 拷贝所有InnoDB的数据文件，系统数据文件也进行拷贝
	 将备份过程中产生的reodo截取并备份走

innobackupex 备份应用

配置文件设置

[client]
socket=/tmp/mysql.sock

执行全备命令

 innobackupex --user=root --password=123  --no-timestamp  /backup/xbk/full

模拟损坏

 pkill mysqld
 
m -rf  /data/3306/data/*

数据恢复准备(备份处理)

innobackupex --apply-log /backup/xbk/full/

恢复数据

--copy-back (依赖于my.cnf [mysqld]的配置)

innobackupex --copy-back   /backup/xbk/full/ 
chown -R mysql.mysql /data/*

--apply-log 参数说明(面试重点)

模仿了InnoDB 引擎的ACSR的过程
将备份集中的数据和日志的LSN追平
利用redo进行前滚
利用undo 进行回滚

备份目录生成的文件信息说明

xtrabackup_binlog_info		    -- 存储的是binlog截取的起始点信息(position,gtid)
xtrabackup_checkpoints		   -- 备份的乐行和lsn相关信息
xtrabackup_info				  -- 详细备份信息

xtrabackup_checkpoints 文件内容说明

# 备份类型
backup_type = full-prepared

# 整个备份包含的最起始的LSN
from_lsn = 0

# CKPT后数据页面(ibd)的LSN
to_lsn = 244445125

# 备份结束时，redo的LSN,（5.7版本默认产生9个LNS）
last_lsn = 244445134
compact = 0
recover_binlog_info = 0

XBK 增量备份(increamental)

增量备介绍

特点：基于全备和上次备份的增量备份
优点：节约磁盘空间和时间
局限性：依赖于全备和上次备份,XKB工具需要将增量合并到全备中才能恢复。

XBK增量(incremental)备份模拟

情况说明：

Xtrabackup企业级增量恢复实战
背景：
某大型网站，mysql数据库，数据量500G，每日更新量20M-30M
备份策略：
xtrabackup，每周日23:00进行全备，周一到周六23:00进行增量备份。
故障场景：
周三下午2点出现数据库意外删除表操作。
如何恢复？

模拟数据

mysql[(none)]>create database test charset utf8mb4;
mysql[(none)]>use test;
mysql[test]>create table t1(id int) engine=innodb charset=utf8mb4;
mysql[test]>insert into t1 values(1),(2),(3);
mysql[test]>commit;

模拟周日全备

innobackupex --user=root --password=123456 --no-timestamp /backup/xbk/full

模拟周一的数据变化

mysql[(none)]>use test;
mysql[test]>insert into t1 values(11),(22),(33);
mysql[test]>commit;

模拟周一晚上增量备份

innobackupex --user=root --password=123456 --no-timestamp --incremental --incremental-basedir=/backup/xbk/full /backup/xbk/inc1

--incremental                          打开增量备份开关
--incremental-basedir=/backup/xbk/full 设定增量备份的基备份(一般是上一天)

模拟周二白天的数据变化

mysql[(none)]>use test;
mysql[test]>insert into t1 values(111),(222),(333);
mysql[test]>commit;

模拟周二晚上增量备份

innobackupex --user=root --password=123456 --no-timestamp --incremental --incremental-basedir=/backup/xbk/inc1 /backup/xbk/inc2

模拟周三白天数据变化

mysql[(none)]>use test;
mysql[test]>insert into t1 values(1111),(2222),(3333);
mysql[test]>commit;

模拟数据损坏

pkill mysqld 
rm -rf /data/3306/data/*

恢复思路

1）测试库，维护页
2）处理备份：合并、准不
3）截取二进制日志
4）数据恢复

开始恢复演练 - 处理备份

# 处理原始全备
innobackupex --apply-log --redo-only /backup/xbk/full/

# 合并周一数据并处理
innobackupex --apply-log  --incremental-dir=/backup/xbk/inc1 /backup/xbk/full

# 合并周二数据并处理
innobackupex --apply-log  --incremental-dir=/backup/xbk/inc2 /backup/xbk/full

# 处理合并后全备数据
innobackupex --apply-log /backup/xbk/full/

恢复备份启动数据库

# 将备份数据拷贝到数据目录
cp -a /backup/xbk/full/* /data/3306/data/ 

# 修改权限
chown -R mysql.mysql  /data/3306/data/

# 启动数据库
/etc/init.d/mysqld start

截取binlog

# 查看使用binlog日志和 pos起始位置
cat /backup/xbk/inc2/xtrabackup_binlog_info 
	mysql-bin.000004	1339

# 获取pos结束位置(drop)
mysql[test]>show binlog events in 'mysql-bin.000004';

# 导出binlog数据
mysqlbinlog --skip-gtids --start-position=1339 --stop-position=1600 /data/binlog/mysql-bin.000004 >/backup/bin.sql

binlog数据恢复

mysql[test]>set sql_log_bin=0;
mysql[test]>source /backup/bin.sql
mysql[test]>select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   11 |
|   22 |
|   33 |
|  111 |
|  222 |
|  333 |
| 1111 |
| 2222 |
| 3333 |

恢复数据的效率(小扩展)

整库数据量较大 ,但是损坏的数据很少**
例如 : 500G数据总量, 损坏的数据是10M

XBK: 表空间迁移

MDP:
手工分析
1、获得表结构

sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE city/!d;q'  /tmp/full.sql>createtable.sql

2、获得INSERT INTO 语句，用于数据的恢复

# grep -i 'INSERT INTO `city`'  /tmp/full.sqll >data.sql

3.获取单库的备份

 sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql >world.sql

小扩展: 闪回表数据(binlog闪回)

数据损坏:
物理: 磁盘,raid ,FS ,ibd
逻辑: drop, alter, delete ,update

说明: 根据 binlog row的记录,自动转化日志为逆操作,实现闪回的功能.

mariadb 默认支持
binlog2sql

小扩展:迁移

Oracle ,SQL Server -----> MySQL
Oracle ----OGG---> MySQL
MySQL 低版本 --XBK,MDP--> MySQL 高版本
上云迁移,DTS