MySQL之备份恢复

1. 数据损坏种类

1.1 物理损坏

• 磁盘损坏：硬件、坏道、dd、格式化

• 文件损坏：数据文件损坏、redo log损坏

1.2 逻辑损坏

• drop
• delete
• truncate
• update

2. 运维人员在备份、恢复的职责

2.1 设计备份、容灾策略

• 备份策略：
  • 备份工具选择
  • 备份周期设计
  • 备份监控方法
• 容灾策略：
  • 备份
  • 架构：高可用、延迟从库、灾备库(两地三中心)

2.2 定期的备份、容灾检查

2.3 定期的故障恢复演练

2.4 数据损坏时的快速准确恢复

2.5 数据迁移工作

3. 常用备份工具

3.1 逻辑备份方式

• mysqldump(MDP)
• replication
• mydumper
• load data in file

3.2 物理备份方式

• MySQL Enterprise Backup
• Percona Xtrabackup(PBK、XBK)

4. mysqldump

4.1 介绍

逻辑备份工具，备份的是SQL语句

对InnoDB表：
可以采用快照备份的方式：开启一个独立的事务，获取当前最新的一致性快照(不需要锁表)
将快照数据放在临时表中，转换成SQL(create database、create table、insert)保存到SQL文件中

对于非InnoDB表(MyISAM)：
需要锁表备份，会触发FTWRL，全局锁表，转换成SQL，保存到SQL文件中

4.2 核心参数

4.2.1 连接参数

和mysql客户端的参数一样
-u		user，用户名
-p		password，密码
-h		host，主机地址
-P		port，端口
-S		socket

4.2.2 备份参数

-A		全备份，在恢复时会覆盖恢复(删除已有的重名表)
[root@db01 ~]# mkdir -p /data/backup
[root@db01 ~]# chown -R mysql.mysql /data/*
[root@db01 ~]# mysqldump -A > /data/backup/full.sql
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. 

-B		备份1个或多个库，备份时会添加create database和use database两条语句
[root@db01 ~]# mysqldump -B world test > /data/backup/db.sql

不加参数可以单独备份表，第一个参数为库名，后面的为该库下面的表
[root@db01 ~]# mysqldump world city country > /data/backup/tab.sql
用该方法备份全库时需要自行创建数据库并use到该库后再恢复        

使用和不使用-B备份全库的区别

4.2.3 高级参数

• --master-data：备份时将binlog的位置和文件名追加到输出中，1直接追加，2为注释追加，0为不追加，配合下一个参数使用可以减小锁表的影响

--master-data[=#]   This causes the binary log position and filename to be
    appended to the output. If equal to 1, will print it as a
    CHANGE MASTER command; if equal to 2, that command will
    be prefixed with a comment symbol. This option will turn
    --lock-all-tables on, unless --single-transaction is
    specified too (in which case a global read lock is only
    taken a short time at the beginning of the dump; don't
    forget to read about --single-transaction below). In all
    cases, any action on logs will happen at the exact moment
    of the dump. Option automatically turns --lock-tables
    off.

• –single-transaction：对于InnoDB引擎表备份时，开启一个独立的事务，获取一致性快照再进行备份(就不用锁表了)

--single-transaction 
    Creates a consistent snapshot by dumping all tables in a
    single transaction. Works ONLY for tables stored in
    storage engines which support multiversioning (currently
    only InnoDB does); the dump is NOT guaranteed to be
    consistent for other storage engines. While a
    --single-transaction dump is in process, to ensure a
    valid dump file (correct table contents and binary log
    position), no other connection should use the following
    statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
    TRUNCATE TABLE, as consistent snapshot is not isolated
    from them. Option automatically turns off --lock-tables.

• -R -E --traiggers：导出存储过程、事件、触发器
• --max_allowed_packet：发送包数据最大值

参数使用场景分析：

场景：每周全备，每天备份binlog，所有备份都是完整的，有一天一个扑街删库跑路了

方案：恢复全备+所需要的binlog恢复
痛点：binlog起点和终点的截取
终点：drop操作前的位置点
起点：
	方案一：备份时使用-F选项，每次开始备份时都会滚动一次日志，但每备份一个库都会滚动一次日志，会生成很多binlog日志文件
	方案二：使用上述高级参数，全备中会记录开启备份时的binlog位置点和文件信息

4.3 mysql+binlog故障恢复案例

案例场景：
	基础环境：CentOS 7.6 + MySQL 5.7.28，LNMT 网站业务，数据量100G，每天增长5-10M数据
	备份策略：mysqldump每天全备，binlog定时备份
	故障说明：周三上午10点数据故障，如：核心业务库被误删

恢复思路：
	1. 挂维护页面(503?)，告诉用户我们是在维护升级，不是数据库坏了🌚
	2. 使用测试库，恢复周二全备
	3. 截取周三全备，截取出上午10点误删前的binlog，并恢复
	4. 测试物业功能是否正常
	5. 恢复业务：
		方案一：将故障库恢复使用
		方案二：直接使用测试库上线，等故障库恢复后再改回去

模拟数据损坏及恢复

-- 1. 模拟原始数据
mysql> create database mdp charset utf8mb4;
mysql> use mdp
mysql> create table t1 (id int);
mysql> begin;
mysql> insert into t1 values(1),(2),(3);
mysql> commit;

-- 2. 模拟周二晚上全备
[root@db01 ~]# mysqldump -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M > /data/backup/full_`date +%F`.sql

-- 3. 模拟周三白天数据变化
mysql> create table t2 (id int);
mysql> begin;
mysql> insert into t2 values(1),(2),(3);
mysql> commit;

-- 4. 搞破坏
mysql> drop database mdp;

-- 5. 检查全备(主要是找到备份开始时的binlog位置)
[root@db01 ~]# vim /data/backup/full_2020-06-25.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=788;

-- 6. 恢复全备
mysql> set sql_log_bin=0;		# 开了GTID后全备时其实会自动关
mysql> source /data/backup/full_2020-06-25.sql

-- 7.截取binlog
起点：
[root@db01 ~]# grep "^-- CHANGE MASTER TO" /data/backup/full_2020-06-25.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=788;
终点：
mysql> show binlog events in 'mysql-bin.000007';
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                               |
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000007 | 1210 | Gtid           |         6 |        1275 | SET @@SESSION.GTID_NEXT= '432e4da1-abba-11ea-99d1-000c29c8dc7e:14' |
| mysql-bin.000007 | 1275 | Query          |         6 |        1364 | drop database mdp                                                  |
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
通过GTID截取
[root@db01 ~]# mysqlbinlog --skip-gtids --include-gtids='432e4da1-abba-11ea-99d1-000c29c8dc7e:12-13' /data/binlog/mysql-bin.000007 > /tmp/binlog1.sql
通过position截取
[root@db01 ~]# mysqlbinlog --skip-gtids --start-position=788 --stop-position=1275 /data/binlog/mysql-bin.000007 > /tmp/binlog2.sql

-- 8. 恢复binlog
mysql> set sql_log_bin=0;
mysql> source /tmp/binlog1.sql;
mysql> set sql_log_bin=1;

4.4 小结

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

缺点：
1.依赖于数据库引擎，需要从磁盘把数据读出，然后转换成SQL进行转储，比较耗费资源，数据量大的话效率较低
2.备份事件相对较长，恢复时间很长

建议：
100G以内的数据量级，可以使用mysqldump
超过TB以上，我们也可能选择的是mysqldump，配合分布式的系统

5. Percona Xtrabackup

5.1 安装

# 安装依赖包
[root@db01 ~]# yum -y install perl perl-devel libaio libaio-devel perL-Time-HiRes perl-DBD-MySQL libev

# 下载安装
[root@db01 ~]# wget https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
[root@db01 ~]# yum -y install percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm 

5.2 介绍

物理备份工具，备份的是数据文件，支持全量备份和增量备份

前提：
(1) 数据库服务处于启动状态
(2) 数据库客户端能连上数据库(XtraBackup只能使用socket文件本地连接，默认读取配置文件[client]块中的socket)
(3) 默认读取配置文件[mysqld]块中的datadir

5.2.1 全量备份

对于InnoDB表：热备份，在数据库正常业务时,备份数据,并且能够一致性恢复，对业务影响小
1. checkpoint，将已提交的数据页刷新到新的磁盘，记录一个LSN
2. 拷贝InnoDB表相关文件(ibdata1、frm、ibd...)
3. 备份期间产生新的数据变化的redo、undo也会备份走

对于MyISAM表：温备份，锁表备份,只能查询不能修改，会影响到写入操作
1. FTWRL，触发全局锁
2. 拷贝MyISAM表的数据
3. 解锁

再次统计LSN，写到专用文件
记录二进制日志位置记录下来
所有备份文件统一存放再一个目录下

全量备份流程总结：

1. 复制已有的redo log，然后监听redo log变化并持续复制
2. 复制事务引擎数据文件
3. 等到数据文件复制完成
4. FTWRL，加全局读锁
5. 备份非事务引擎数据文件及其他文件
6. 获取binlog位置信息等元数据
7. 停止复制redo log
8. 解锁
9. 复制buffer pool dump
10. 备份完成

为什么要先复制redo log，而不是直接开始复制数据文件？

因为XtraBackup是基于InnoDB的crash recovery机制进行工作的，由于是热备操作，在备份过程中可能有持续的数据写入，直接复制出来的数据文件可能有缺失或被修改的页，而redo log记录了InnoDB引擎的所有事务日志，可以在还原时应用redo log来补全数据文件中缺失或修改的页。所以为了确保redo log一定包含备份过程中涉及的数据页，需要首先开始复制redo log。

加全局读锁的作用？

因为要保证”非事务资源 自身的一致性“ 和 ”非事务资源与 事务资源的一致性“。在加锁期间，没有新数据写入，XtraBackup会复制此时的binlog位置信息，frm表结构，MyISAM等非事务表

为什么要先停止复制redo log，再解锁全局读锁？

也是因为要保证“非事务资源与事务资源的一致性”，保证通过redo log回放后的InnoDB数据与非InnoDB数据都是处于读锁期间取得的位点

全备操作

命令：
[root@db01 ~]# innobackupex /data/xbk/
# 使用自定义时间戳
[root@db01 ~]# innobackupex --no-timestamp /data/xbk/full_`date +%F`

备份结果：
xtrabackup_binlog_info：记录备份完成后binlog位置点信息，binlog的截取起点
xtrabackup_checkpoints：记录备份类型、LSN的变化状态、当前备份集的状态(是否prepare过)等
xtrabackup_info：记录备份时的一些参数、脚本版本、数据库版本、备份事件、是否压缩、备份方式等
xtrabackup_logfile：记录从备份开始到备份结束新增的redo日志，恢复时先用用该文件进行前滚

全备还原流程

1. prepare，数据准备，模拟MySQL进行crash recover，将redo log回放到数据文件中
2. 等待recover完成
3. 重建redo log，为数据库启动做准备
4. 将数据库文件复制回数据目录
5. 还原完成

全备恢复示例

# 破坏数据文件
[root@db01 ~]# pkill mysqld
[root@db01 ~]# rm -rf /data/3306/*

# 对备份集先进行处理(redo、undo)
[root@db01 ~]# innobackupex --apply-log /data/xbk/2020-06-25_22-38-04/

# 将备份集拷贝回数据目录，该目录必须是空目录
[root@db01 ~]# cp -a /data/xbk/2020-06-25_22-38-04/* /data/3306/
[root@db01 ~]# chown -R mysql.mysql /data/*

# 启动数据库
[root@db01 ~]# systemctl start mysqld

为什么需要先进行prepare？

XtraBackup基于InnoDB的crash recovery机制，在备份还原时利用redo log得到完整的数据文件，并通过全局读锁，保证InnoDB数据与非InnoDB数据的一致性，最终完成备份还原的功能

5.2.2 增量备份

1. 增量备份的方式，是基于上一次备份进行增量。
2. 增量备份无法单独恢复,必须基于全备进行恢复。
3. 所有增量必须要按顺序合并到全备中

增量备份操作

# 环境准备
mysql> create database xbk charset utf8mb4;
mysql> use xbk
mysql> create table t1(id int);
mysql> begin;
mysql> insert into t1 values(1),(2),(3);
mysql> commit;

# 模拟周日全备
[root@db01 ~]# innobackupex --no-timestamp /data/xbk/full

# 模拟周一增量变化
mysql> create table t2(id int);
mysql> begin;
mysql> insert into t2 values(1),(2),(3);
mysql> commit;

# 模拟周一增量备份
--incremental				# 增量备份开关
--incremental-basedir		# 增量备份基目录
[root@db01 ~]# innobackupex --no-timestamp --incremental --incremental-basedir=/data/xbk/full /data/xbk/inc1

# 模拟周二增量变化
mysql> create table t3(id int);
mysql> begin;
mysql> insert into t3 values(1),(2),(3);
mysql> commit;

# 模拟周二增量备份
[root@db01 ~]# innobackupex --no-timestamp --incremental --incremental-basedir=/data/xbk/inc1 /data/xbk/inc2

# 模拟周三增量变化，该变化不需要增量备份，通过binlog恢复
mysql> create table t4(id int);
mysql> begin;
mysql> insert into t4 values(1),(2),(3);
mysql> commit;

# 查看三个备份集的checkpoints
# 可以看到三个备份集的类型是不一样的，且LSN号码起止是相连的(相差9)
[root@db01 ~]# cat /data/xbk/full/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 245876866
last_lsn = 245876875
compact = 0
recover_binlog_info = 0

[root@db01 ~]# cat /data/xbk/inc1/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 245876866
to_lsn = 245882452
last_lsn = 245882461
compact = 0
recover_binlog_info = 0

[root@db01 ~]# cat /data/xbk/inc2/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 245882452
to_lsn = 245888041
last_lsn = 245888050
compact = 0
recover_binlog_info = 0

增量恢复还原流程

1. 将所有增量备份prepare所有增量备份到全备中，若有多个增量备份则只有最后一个能进行undo，否则各个备份的起止LSN对不上(备份时无数据更改时相差9)
2. 恢复数据，启动数据库
3. 截取binlog日志
4. 恢复日志

增量恢复示例

# 破坏数据文件
[root@db01 ~]# pkill mysqld
[root@db01 ~]# rm -rf /data/3306/*

# 将所有增量备份prepare合并到全备
# 这里inc2是最后一次增量备份，所以前面的都要加--redo-only
[root@db01 ~]# innobackupex --apply-log --redo-only /data/xbk/full
# 增量备份合并
[root@db01 ~]# innobackupex --apply-log --redo-only --incremental-dir=/data/xbk/inc1 /data/xbk/full
[root@db01 ~]# innobackupex --apply-log --incremental-dir=/data/xbk/inc2 /data/xbk/full
# 整体再prepare一下全备
[root@db01 ~]# innobackupex --apply-log /data/xbk/full

# 将备份集拷贝回数据目录
[root@db01 ~]# cp -a /data/xbk/full/* /data/3306/
[root@db01 ~]# chown -R mysql.mysql /data/*

# 重启数据库
[root@db01 ~]# systemctl restart mysqld

# 截取日志
[root@db01 ~]# cat /data/xbk/full/xtrabackup_binlog_info 
mysql-bin.000010	2228	12631d95-b713-11ea-9e29-000c29c8dc7e:1-10,
432e4da1-abba-11ea-99d1-000c29c8dc7e:1-14,
c24af098-b702-11ea-ae34-000c29c8dc7e:1-9
[root@db01 ~]# mysqlbinlog --skip-gtids --start-position=2228 /data/binlog/mysql-bin.000010 > /tmp/bin.sql

# 恢复日志
mysql> set sql_log_bin=0;
mysql> source /tmp/bin.sql
mysql> set sql_log_bin=1;

一些细节：

1. 每次xbk恢复后，可以 reset master 清除无用的gtid信息
2. 每次故障恢复完成后，立即做全备
3. 如果增量合并不了：全备+binlog恢复

5.3 业务快速恢复案例

场景：总数据量3TB，共10个业务，10个库500张表，周三上午10点误删了某张核心业务表

备份策略：每周全备份，每天增量备份，binlog完整

问题：如何快速恢复，还不影响其他业务

思路：利用全备+增量备份+binlog恢复数据文件到测试库中，可用后再将数据迁移回原库
迁移方案：
	1. 用mysqldump将表数据导出再导入，慢，很慢，非常慢
	2. 在原库中创建一张同样的表，用ibd文件迁移表空间

-- 创建表
mysql> create table xx like xx_bak;
-- 删除这张空表的表空间
mysql> alter table xx discard tablespace;
-- 复制ibd文件
[root@db01 ~]# cp -a /backup/xx.ibd /data/xx.ibd
-- 导入表空间文件
mysql> alter table xx import tablespace;