第七章备份恢复

本课程，适合具备一定Linux运维或者开发基础的朋友，课程定级中、高级DBA。
只要掌握80%，轻松助力薪资15k-25K。
课程内容均来自与MySQL官网+MySQL源码。
配套精品视频(2021 5月全新录制，版权所有：郭加磊 oldguo。)，获取方法私聊。

1. 备份的作用

处理数据库损坏。
损坏？
物理：磁盘、文件系统、数据文件。处理方案：主从、高可用、备份+日志。
逻辑：drop truncate delete update 。处理方案：备份+日志、延时从。

2. 备份工具

逻辑备份： mysqldump (MDP)、binlog 、主从 .....
物理备份 : Percona Xtrabackup (PXB\XBK\Xbakcup)
扩展: 8.0 Clone plugin

3. 作为DBA在备份恢复工作职责

a. 设计备份策略：备份周期、备份工具、备份方式（全备、增量..）
b. 定期备份检查。
c. 定期恢复演练。
d. 数据恢复。
e. 数据的迁移升级。

4. mysqldump 应用

4.1 介绍

逻辑备份工具。文本形式保存备份，可读性较强。
备份逻辑：将建库、建表、数据插入语句导出，包存至一个sql文件中。
比较适合于：数据量较小的场景，单表数据行千万级别以内。百G以内的小型数据库.跨版本、跨平台迁移。
可以本地、可以远程备份。
注意：一般情况下，恢复需要耗费的时间是备份耗费时间的3-5倍。

4.2 使用

4.2.1 连接参数

mysqldump -u -p -S -h -P

4.2.2 备份基础参数

-A 全备

[root@db01 ~]# mysqldump -uroot -p123 -A >/data/backup/full.sql

-B 单库或多库备份

[root@db01 ~]# mysqldump -uroot -p123 -B test world >/data/backup/db.sql

单表或多表备份

[root@db01 ~]# mysqldump -uroot -p123  world city country >/data/backup/tb.sql

注意：
-A 和 -B 都带有了 create database 和use 语句，直接恢复即可
单表或多表备份方式, 没有 create database 和use 语句，所以要手工进行建库和use，再恢复数据。

4.2.3 高级功能参数

参数一： --master-data=2

a. 自动记录binlog位置点 b. 自动加GRL锁(FTWRL ,flush tables with read lock) c. 配合--single-transaction ,减少锁的时间。

参数二： single-transaction
a. 对于InnoDB表，利用MVCC中一致性快照进行备份。备份数据时,不加锁 b. 备份期间如果出现DDL操作，导致备份数据不一致问题： mysqldump是严格意义上的热备吗？ 8.0 之后 master-data和single-transaction,对于InnoDB数据备份时是快照备份的. 备份表结构等数据时,还是FTWRL过程备份. --single-transaction 只是针对InnoDB表数据进行一致性快照备份。问题： mysqldump备份需要锁表吗？是有的。global read lock

参数三： -R -E --triggers 备份特殊对象
存储过程函数触发器事件

参数四： --max_allowed_packet=64M
最大允许数据包的大小。

4.2.4 标准化备份

[root@db01 backup]# mysqldump -uroot -p -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M >/data/backup/full_`date +%F`.sql
Enter password:

4.2.5 案例：通过mysqldump全备+binlog实现PIT数据恢复

环境背景：小型的业务数据库，50G，每天23:00全备，定期binlog异地备份。
故障场景：周三下午2点，开发Navicat连接数据库实例错误，导致生产数据被误删除（DROP）
恢复思路：
1. 挂维护页。
2. 检查备份、日志可用。
3. 如果只是部分损坏，建议找一个应急库进行恢复
a. 全备恢复
b. 日志截取并恢复
4. 恢复后数据校验（业务测试部门验证）
5. 立即备份（停机冷备）
6. 恢复架构系统
7. 撤维护页，恢复业务

> 模拟环境：
mysql> create database mdb;
Query OK, 1 row affected (0.01 sec)

mysql> use mdb
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.03 sec)

mysql> create table t2 (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)


[root@db01 backup]# mysqldump -uroot -p -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M >/data/backup/full_`date +%F`.sql

mysql> create table t3 (id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t3 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database mdb;

> 恢复过程：  
a. 查看备份，获取二进制日志位置点

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=1274;

思考一个问题： binlog位置点是备份开始时，还是备份结束时的位置点？

b.  恢复全备
mysql> source /data/backup/full_2020-09-18.sql

c.  binlog 截取并恢复 
| binlog.000001 | 1711 | Xid            |         1 |        1742 | COMMIT /* xid=2278 */    

[root@db01 backup]# mysqlbinlog --skip-gtids --start-position=1274 --stop-position=1742 /data/3306/data/binlog.000001 >/tmp/bin.sql


mysql> set sql_log_bin=0;
mysql> source /tmp/bin.sql

4.2.6 扩展 (课后作业)

100G mysqldump全备恢复时间很长，误删除的表10M大小，有什么思路可以快速恢复？

思路：  
a. 从全备中，将单表 建表语句和insert语句提取出来 ，进行恢复

# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `oldguo`/!d;q'  /data/backup/mdp/full.sql>/data/createtable.sql
# grep -i 'INSERT INTO `oldguo`'   /data/backup/mdp/full.sql >/data/data.sql 

b. 从binlog中单独截取单表的所有binlog，进行恢复。 
binlog2sql 截取单表binlog，恢复。

5. 物理备份工具使用-Percona Xtrabackup（PXB）

5.0 介绍

物理备份工具，支持全备和增量备份。
备份逻辑：
a. 数据库运行期间，拷贝数据表空间.
b. 拷贝的同时，会将备份期间的redo进行备份
恢复逻辑：
模拟了InnoDB Crash Recovery 功能，需要要将备份进行处理（前滚和回滚）后才能恢复

5.1 安装

yum install percona-xtrabackup*.rpm

注意：
对于MySQL 8.0.20版本，需要使用PXB 8.0.12+以上版本，MysQL：8.0.11 ~ 8.0.19 使用PXB 8.0正式版本。
MySQL 8.0 之前（5.6，5.7 ）的版本： PXB 2.4

5.2 全量备份

1.全量备份

xtrabackup --defaults-file=/etc/my.cnf --user=root --password=123   --backup --target-dir=/data/backup/full

2.数据恢复：

[root@db01 ~]# pkill mysqld
[root@db01 ~]# rm -rf /data/3306/data/*
[root@db01 ~]# rm -rf /data/3306/logs/*
[root@db01 ~]# rm -rf /data/3306/binlog/*

2.1 准备：(CR)

xtrabackup   --prepare --target-dir=/data/backup/full

说明：模拟CR过程，将redo前滚，undo回滚，让备份数据是一致状态

2.2 拷回数据：

xtrabackup  --copy-back --target-dir=/data/backup/full

2.3 修改权限并启动数据库

[root@db01 data]# chown -R mysql.mysql /data/*
[root@db01 data]# /etc/init.d/mysqld start

5.3 增量备份

5.3.1 介绍

增量备份，是基于上一次备份LSN变化过的数据页进行备份，在备份同时产生的新变更，会将redo备份。
第一次增量是依赖于全备的。将来的恢复也要合并到全备中，再进行统一恢复。

5.3.2 增量备份演练

全量备份的目录为： mkdir -p /data/backup/full
增量备份的目录为： mkdir -p /data/backup/inc

1.备份过程
1. 全量备份：
xtrabackup --defaults-file=/etc/my.cnf  --user=root --password=123  --backup --parallel=4 --target-dir=/data/backup/full

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

1.2.增量备份：
xtrabackup --defaults-file=/etc/my.cnf --user=root --password=123  --backup --parallel=4 --target-dir=/data/backup/inc  --incremental-basedir=/data/backup/full
 
1.3. 模拟损坏  
[root@db01 ~]# pkill mysqld
[root@db01 ~]# rm -rf /data/3306/data/*

2. 恢复操作：
2.1 准备全备份的日志：
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/full


2.2 准备增量备份的日志：
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/full  --incremental-dir=/data/backup/inc


2.3 全备份准备：
# xtrabackup --prepare --target-dir=/data/backup/full


2.4 拷回数据：
xtrabackup    --copy-back --target-dir=/data/backup/full
 
2.5 修改数据目录的权限和属性：
chown -R mysql:mysql /data/*

课后思考: 
500G数据， 每周日全备，其他时间段增量，周三下午误删除了一个表（10M）数据。
如何快速恢复。

2.8 MySQL 8.0（8.0.17+） Clone-plugin

2.8.1 Clone Plugin介绍

本地克隆
启动克隆操作的MySQL服务器实例中的数据，克隆到同服务器或同节点上的一个目录里
远程克隆
默认情况下，远程克隆操作会删除接受者(recipient)数据目录中的数据，并将其替换为捐赠者(donor)的克隆数据。您也可以将数据克隆到接受者的其他目录，以避免删除现有数据。(可选)

2.8.2 原理

PAGE COPY
开启redo archiving功能，从当前点开始存储新增的redo log，这样从当前点开始所有的增量修改都不会丢失。同时上一步在page track的page被发送到目标端。确保当前点之前所做的变更一定发送到目标端。
关于redo archiving，实际上这是官方早就存在的功能，主要用于官方的企业级备份工具，但这里clone利用了该特性来维持增量修改产生的redo。
在开始前会做一次checkpoint，开启一个后台线程log_archiver_thread()来做日志归档。当有新的写入时(notify_about_advanced_write_lsn)也会通知他去archive。当arch_log_sys处于活跃状态时，他会控制日志写入以避免未归档的日志被覆盖(log_writer_wait_on_archiver), 注意如果log_writer等待时间过长的话， archive任务会被中断掉.

Redo Copy
停止Redo Archiving,所有归档的日志被发送到目标端，这些日志包含了从page copy阶段开始到现在的所有日志，另外可能还需要记下当前的复制点，例如最后一个事务提交时的binlog位点或者gtid信息，在系统页中可以找到。

Done
目标端重启实例，通过crash recovery将redo log应用上去。

2.8.3 限制

官方文档列出的一些限制：
The clone plugin is subject to these limitations:
* DDL, is not permitted during a cloning operation. This limitation should be considered when selecting data sources. A workaround is to use dedicated donor instances, which can accommodate DDL operations being blocked while data is cloned. Concurrent DML is permitted.
* An instance cannot be cloned from a different MySQL server version. The donor and recipient must have the same MySQL server version. For example, you cannot clone between MySQL 5.7 and MySQL 8.0\. The clone plugin is only supported in MySQL 8.0.17 and higher.
* Only a single MySQL instance can be cloned at a time. Cloning multiple MySQL instances in a single cloning operation is not supported.
* The X Protocol port specified byis not supported for remote cloning operations
* The clone plugin does not support cloning of MySQL server configurations.
* The clone plugin does not support cloning of binary logs.
* The clone plugin only clones data stored inInnoDB. Other storage engine data is not cloned.
* Connecting to the donor MySQL server instance through MySQL Router is not supported.
* Local cloning operations do not support cloning of general tablespaces that were created with an absolute path. A cloned tablespace file with the same path as the source tablespace file would cause a conflict.

2.8.4. 应用

a. 本地

# 1. 加载插件
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
或
[mysqld]
plugin-load-add=mysql_clone.so
clone=FORCE_PLUS_PERMANENT

SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE 'clone';

# 2 创建克隆专用用户
CREATE USER clone_user@'%' IDENTIFIED by 'password'; 
GRANT BACKUP_ADMIN ON *.* TO 'clone_user'; 

注意：BACKUP_ADMIN是MySQL8.0 才有的备份锁的权限

# 3 本地克隆
[root@db01 3306]# mkdir -p /data/test/
[root@db01 3306]# chown -R mysql.mysql /data/
mysql -uclone_user -ppassword
CLONE LOCAL DATA DIRECTORY = '/data/test/clonedir';

# 4 观测状态
db01 [(none)]> SELECT STAGE, STATE, END_TIME FROM performance_schema.clone_progress;
+-----------+-------------+----------------------------+
| STAGE     | STATE       | END_TIME                   |
+-----------+-------------+----------------------------+
| DROP DATA | Completed   | 2020-04-20 21:13:19.264003 |
| FILE COPY | Completed   | 2020-04-20 21:13:20.025444 |
| PAGE COPY | Completed   | 2020-04-20 21:13:20.028552 |
| REDO COPY | Completed   | 2020-04-20 21:13:20.030042 |
| FILE SYNC | Completed   | 2020-04-20 21:13:20.439444 |
| RESTART   | Not Started | NULL                       |
| RECOVERY  | Not Started | NULL                       |
+-----------+-------------+----------------------------+
7 rows in set (0.00 sec)

# 日志观测： 
set global log_error_verbosity=3;
tail -f db01.err
CLONE LOCAL DATA DIRECTORY = '/data/test/3308';

# 启动新实例
[root@db01 clonedir]# mysqld_safe  --datadir=/data/test/clonedir --port=3333 --socket=/tmp/mysql3333.sock --user=mysql --mysqlx=OFF &

2.8.5 远程clone

# 各个节点加载插件
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
或
[mysqld]
plugin-load-add=mysql_clone.so
clone=FORCE_PLUS_PERMANENT

SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE 'clone';

# 创建远程clone用户
# 捐赠者(source)授权
create user test_s@'%' identified by '123';
grant backup_admin on *.* to test_s@'%';

# 接受者(target)授权
create user test_t@'%' identified by '123';
grant clone_admin on *.* to test_t@'%';

# 远程clone(目标端)
# 开始克隆
SET GLOBAL clone_valid_donor_list='10.0.0.51:3306';
mysql -utest_t -p123 -h10.0.0.52  -P3306
CLONE INSTANCE FROM test_s@'10.0.0.51':3306 IDENTIFIED BY '123';

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 204,530评论 6赞 478
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 86,403评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 151,120评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,770评论 1赞 277
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,758评论 5赞 367
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,649评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,021评论 3赞 398
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,675评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,931评论 1赞 299
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,659评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,751评论 1赞 330
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,410评论 4赞 321
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,004评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,969评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,203评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,042评论 2赞 350
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,493评论 2赞 343

第七章 备份恢复

1. 备份的作用

2. 备份工具

3. 作为DBA在备份恢复工作职责

4. mysqldump 应用

4.1 介绍

4.2 使用

4.2.1 连接参数

4.2.2 备份基础参数

4.2.3 高级功能参数

4.2.4 标准化备份

4.2.5 案例：通过mysqldump全备+binlog实现PIT数据恢复

4.2.6 扩展 (课后作业)

5. 物理备份工具使用-Percona Xtrabackup（PXB）

5.0 介绍

5.1 安装

5.2 全量备份

5.3 增量备份

5.3.1 介绍

5.3.2 增量备份演练

2.8 MySQL 8.0（8.0.17+） Clone-plugin

2.8.1 Clone Plugin介绍

2.8.2 原理

2.8.3 限制

2.8.4. 应用

a. 本地

2.8.5 远程clone

推荐阅读更多精彩内容

第七章备份恢复