第八章节 备份恢复
1. 运维人员在备份恢复工作职责
a. 备份、恢复策略定制
b. 备份巡检
c. 定期恢复演练
d. 出现数据损坏，快速、准确恢复
e. 升级、迁移
2. MySQL 备份工具
2.1 逻辑 ：
mysqldump
mydumper
into outfile/load data infile/mysqlimport
mysqlbinlog
binlog2sql
主从复制
2.2 物理 ：
xtrabackup
3. mysqldump的应用
3.1 介绍
自带的客户端工具。
逻辑备份工具，备份出来的是SQL语句（Create database 、create table 、insert into）,文本文件。
可读性比较强。
可以有较高的压缩比。
支持本地和远程备份。
3.2 mysqldump 备份参数
a. 连接参数
-u
-p
-h
-P
-S
b. 备份参数
# -A 全库 备份 ：
[root@db01 ~]# mkdir -p /data/backup/mdp
[root@db01 ~]# mysqldump -uroot -p123 -A > /data/backup/mdp/full.sql
# -B 单库或多库备份：
[root@db01 mdp]# mysqldump -uroot -p123 -B world test > /data/backup/mdp/db.sql
# 单表或多表备份
[root@db01 mdp]# mysqldump -uroot -p123 world city country >/data/backup/mdp/tab.sql
[root@db01 mdp]# mysqldump -uroot -p123 world city >/data/backup/mdp/tab1.sql
# --master-data=2
a. 备份开始时，自动记录binlog 文件名+位置号。
b. 自动锁表，自动解锁
c. 配合--single-transaction可以减少锁表
--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.
[root@db01 mdp]# mysqldump -uroot -p123 -A --master-data=2 > /data/backup/mdp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=454;
# --single-transaction
备份InnoDB表时，开启一个独立事务，调整隔离级别RR，开启快照备份。
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.
[root@db01 mdp]# mysqldump -uroot -p123 -A --master-data=2 --single-transaction > /data/backup/mdp/full.sql
彩蛋： 备份流程说明
1. 备份元数据（create database ，create table ）---》 show ，information_schema ---> FTWRL ,global read lock
2. 备份MyISAM表数据,锁表备份 ，select --转换-》 full.sql
3. 备份InnoDB表数据,调整隔离级别，解锁表，开启独立事务,生成快照, select 快照----》full.sql
具体备份流程可以参照：
mysql> show variables like '%general%';
+------------------+--------------------------+
| Variable_name | Value |
+------------------+--------------------------+
| general_log | OFF |
| general_log_file | /data/3306/data/db01.log |
+------------------+--------------------------+
mysql> set global general_log=on;
# -R（存储过程和函数） --triggers（触发器） -E（事件） 特殊对象备份
mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R --triggers -E > /data/backup/mdp/full.sql
# max_allowed_packet 数据包大小
mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R --triggers -E --max_allowed_packet=64M > /data/backup/mdp/full.sql
# 压缩备份
mysqldump -uroot -p123 -A -R --triggers -E --master-data=2 --single-transaction --max_allowed_packet=64M |gzip > /backup/full_$(date +%F).sql.gz
3.3 MDP+binlog 故障恢复演练
备份策略：
1. 每天晚上23：00进行MDP的全备+binlog
2. 每天中午12:30，进行binlog备份
模拟故障：
周二上午10点 数据库损坏了，binlog和mdp备份都是好的。
3.3.1 模拟环境
a. 原始数据
mysql> create database mdp charset utf8mb4;
mysql> use mdp;
mysql> create table t1(id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;
b. 模拟周一晚上23:00,mdp全备
[root@db01 mdp]# mysqldump -uroot -p123 -A -R --triggers -E --master-data=2 --single-transaction --max_allowed_packet=64M |gzip > /data/backup/mdp/full_$(date +%F).sql.gz
[root@db01 mdp]# gunzip full_2020-07-10.sql.gz
[root@db01 mdp]# vim full_2020-07-10.sql
c. 模拟周二白天数据变化
mysql> create table t2(id int);
mysql> insert into t2 values(1),(2),(3);
mysql> commit;
d. 模拟周二10点的数据故障
mysql> drop database mdp;
3.3.2 恢复演练
a. 检查备份和日志
vim full_2020-07-10.sql
SET @@GLOBAL.GTID_PURGED='4d98ed45-c0e9-11ea-8dd7-000c295bb94f:1-7';
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1047;
[root@db01 mdp]# cd /data/3306/binlog/
[root@db01 binlog]# ll
total 16
-rw-r----- 1 mysql mysql 357 Jul 10 09:03 mysql-bin.000001
-rw-r----- 1 mysql mysql 541 Jul 10 09:26 mysql-bin.000002
-rw-r----- 1 mysql mysql 1622 Jul 10 11:50 mysql-bin.000003
-rw-r----- 1 mysql mysql 105 Jul 10 09:26 mysql-bin.index
[root@db01 binlog]#
b. 截取binlog
起点： 'mysql-bin.000003', MASTER_LOG_POS=1047
终点： | mysql-bin.000003 | 1533 | Query | 6 | 1622 | drop database mdp
[root@db01 binlog]# mysqlbinlog --skip-gtids --start-position=1047 --stop-position=1533 mysql-bin.000003 >/tmp/bin.sql
或者：
[root@db01 binlog]# mysqlbinlog --skip-gtids --include-gtids='4d98ed45-c0e9-11ea-8dd7-000c295bb94f:8-9' mysql-bin.000003 >/tmp/bin1.sql
c. 恢复数据
[root@db01 binlog]# cd /data/backup/mdp/
mysql> set sql_log_bin=0;
mysql> source /data/backup/mdp/full_2020-07-10.sql
mysql> set sql_log_bin=0;
mysql> source /tmp/bin.sql
mysql> set sql_log_bin=1;
=========================================
彩蛋：扩展案例
备份策略： mdp全备+binlog ，数据量200G
故障场景： 误删除了核心表oldguo表，10M小表。
需求：快速恢复
=========================================
预备环境：
原始环境：
mysql> create database mdb charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
mysql> use mdb;
mysql> create table oldguo (id int);
mysql> insert into oldguo values(1),(2),(3);
全备：
mysqldump -uroot -p123 -A -R --triggers -E --master-data=2 --single-transaction --max_allowed_packet=64M > /data/backup/mdp/full.sql
追加新数据：
mysql> insert into oldguo values(4),(5),(6);
mysql> commit;
mysql> use test;
mysql> delete from t1 where id>10;
Query OK, 4 rows affected (0.00 sec)
mysql> commit;
搞破坏：误删除oldguo表
mysql> drop table mdb.oldguo;
如何快速恢复？
a. 全备截取单表备份：
# 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. binlog2sql 截取binlog单表的日志:
# 功能
1. 友好的展示或管理binlog
2. 快速DML闪回（通过日志翻转方式）。
# 安装配置binlog2sql
git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
yum install python3
pip3 install -r requirements.txt
pip3 show pymysql
pip3 install --upgrade PyMySQL
#解析日志事件SQL
[root@db01 binlog2sql]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123 -d mdb -t oldguo --start-file='mysql-bin.000003'
USE b'test';
create database test;
USE b'test';
create table t1 (id int);
INSERT INTO `test`.`t1`(`id`) VALUES (1); #start 649 end 822 time 2020-06-13 19:58:40
INSERT INTO `test`.`t1`(`id`) VALUES (2); #start 649 end 822 time 2020-06-13 19:58:40
INSERT INTO `test`.`t1`(`id`) VALUES (3); #start 649 end 822 time 2020-06-13 19:58:40
UPDATE `test`.`t1` SET `id`=10 WHERE `id`=1 LIMIT 1; #start 932 end 1110 time 2020-06-13 19:58:56
DELETE FROM `test`.`t1` WHERE `id`=3 LIMIT 1; #start 932 end 1198 time 2020-06-13 19:59:05
[root@db01 binlog2sql]#
# 只解析delete类型操作
[root@db01 binlog2sql]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123 -d test -t t1 --start-file='mysql-bin.000003' --sql-type=delete
#生成指定事件回滚语句
[root@db01 binlog2sql]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123 -d test -t t1 --start-file='mysql-bin.000003' --sql-type=delete --start-position=932 --stop-position=1198 -B
[root@db01 binlog2sql]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123 -d test -t t1 --start-file='mysql-bin.000003' --sql-type=delete --start-position=932 --stop-position=1198 -B>/tmp/flashback.sql
# 遗留问题
