7、备份恢复
1 备份类型
1.1 热备
在数据库正常运行业务时，备份数据，并且能够一致性恢复（只有Innodb支持），对业务影响非常小
1.2 温备
锁表备份，只能查询不能修改（myisam支持），影响到写入操作
1.3 冷备
关闭数据库业务，在数据库没有任何变更的情况下，进行备份数据，业务停止
2 备份方式及工具介绍
2.1 逻辑备份工具
基于SQL语句进行备份
mysqldump
mysqlbinlog
2.2 物理备份工具
基于磁盘数据文件备份
xtrabackup(XBK)：percona 第三方
MySQL Enterprise Backup (MEB)
3 逻辑备份和物理备份的比较
3.1 mysqldump
优点：
1.不需要下载安装
2.备份出来的是SQL，文本格式，可读性高，便于备份处理
3.压缩比高，节省备份的磁盘空间
缺点：
1.依赖于数据库引擎，需要从磁盘把数据读出来，然后转换成SQL进行转储，比较耗费资源，数据量大的话效率较低
建议：
100G以内数据量级，可以使用mysqldump，超过TB以上，也可能选择mysqldump，需要配合分布式系统使用
3.2 xtrabackup(XBK)
优点：
1.类似于直接cp数据文件，不需要逻辑结构，相对来说性能较高
缺点：
1.可读性差
2.压缩比低，需要更多的磁盘空间
建议：
数据量大于100G小于1TB时使用
4 备份策略
方式：
- 全备：全库备份，备份所有数据
- 增量：备份变化的数据
逻辑备份：mysqldump（全备）+mysqlbinlog（增量）
物理备份：xtrabackup_full（全备）+xtrabackup_incr（增量）+binlog（增量）或者xtrabackup_full（全备）+binlog（增量）
备份周期：
根据数据量设计备份周期，比如：周日全备，周1~6增量
5 备份工具的使用
5.1 mysqldump（逻辑备份的客户端工具）
使用备份恢复数据库时，会先进行现有表的删除，再按照备份内容进行库、表、数据的新建。所以生产中一般不在生产库中直接恢复，而是在测试库中恢复，再将表导入到生产库中
5.1.1 客户端通用参数
-u:user
-p:password
-S:socket
-h:host(ip addr)
-P:port
本地备份
mysqldump -uroot -p -S /tmp/mysql.sock
远程备份
mysqldump -uroot -p -h 10.0.0.1 -P3306
5.1.2 备份专用基本参数
-A：全备参数
mysqldump -uroot -p -A >/data/backup/full.sql
一个正确的备份文件头部有备份的系统信息，如下图所示
-B db1 db2 db3：备份多个单库
mysqldump -uroot -p -B oldboy world >/data/backup/full.sql
单库的单表或多表备份
mysqldump -u -p 库 [表1 表2]（不写表名时表示对库下所有表备份）
注意：这种备份文件不包括建库语句，恢复时必须库事先存在,并且use到库，才能source恢复
#备份world数据库下的city,country表
mysqldump -uroot -p world city country >/backup/bak1.sql
5.1.3 高级参数应用
必要的特殊参数
--triggers 备份触发器
-R 或 --routines 备份存储过程及函数
-E或--events 备份事件(类似于计划任务)
-F 在备份开始时,刷新binlog日志
根据除information_schema、performance_schema、sys外的数据库个数，在备份时刷新N个binlog，方便binlog准确记录备份后的数据库操作
mysqldump -uroot -p -A -R --triggers -F >/bak/full.sql
--master-data=2 保存备份开始时间点的binlog状态信息
功能：
在备份时，自动记录二进制binlog文件名和position号
取值：
- 0：默认值，不记录
- 1：以CHANGE MASTER的命令形式记录，用于主从复制场景
- 2：有3个功能
(1)以注释的形式，记录备份时二进制binlog文件名和position号
(2)自动锁表
(3)如果配合--single-transaction，只对非InnoDB表进行锁表备份，InnoDB表进行"热"备，实际上是实现快照备份。
--single-transaction
配合--master-data=2使用，加上--single-transaction不会对Innodb锁表，实现Innodb热备份。否则--master-data=2将对所有表进行锁表备份
--set-gtid-purged=
取值：
- auto , on：备份文件中出现
SET @@GLOBAL.GTID_PURGED=字段，记录备份时GTID信息，使用备份恢复后，新事务将延续GTID编号。在构建主从复制环境时需要配置 - off ：不记录已使用的GTID，使用备份恢复后，新事务从1开始GTID编号。日常使用可以这么设置
--max-allowed-packet=
最大允许传送的数据包大小，在mysqld端和mysqldump端都可以设置，默认是4MB。可以设置的大一些如128M，防止备份大表时出错。
5.2 故障恢复模拟练习
5.2.1 模拟全库删除故障并恢复
环境：
（1）每天全备
（2）binlog日志是完整
（3）模拟白天的数据变化
（4）模拟下午两点误删除全部数据库
需求： 利用全备+binlog回复数据库误删除之前。
5.2.1.1 恢复思路
1.检查备份可用性
2.从备份中获取二进制日志位置
3.根据日志位置截取需要的二进制日志
4.初始化数据库,并启动
5.恢复全备
6.恢复二进制日志
5.2.1.2 模拟周一23:00的全备
mysqldump -uroot -p123 -A -R -E --triggers --master-data=2 --single-transaction > /data/backup/full.sql
5.2.1.3 模拟白天的数据变化
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 [day1]>use world;
Master [world]>update city set countrycode='CHN';
Master [world]>commit;
5.2.1.4 模拟磁盘损坏
pkill mysqld
rm -rf /data/mysql/*
5.2.1.5 故障恢复-查看全备时binlog文件以及position号
[root@node1 ~]$ egrep "^-- CHANGE.*mysql-bin" /data/backup/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=465;
5.2.1.6 故障恢复-截取二进制日志
[root@node1 ~]$ mysqlbinlog --start-position=465 --skip-gtids /data/binlog/mysql-bin.000015 > /data/backup/bin.sql
5.2.1.7 故障恢复-重新初始化数据库
[root@node1 ~]$ mysqld --initialize-insecure --user=mysql --basedir=/app/mysql --datadir=/data/mysql
[root@node1 ~]$ systemctl start mysqld
[root@node1 ~]$ mysqladmin -uroot -p password
Enter password:
New password:
Confirm new password:
5.2.1.8 故障恢复-恢复全备
[none]> source /data/backup/full.sql
5.2.1.9 故障恢复-恢复二进制
[(none)]>set sql_log_bin=0;
[(none)]>source /data/backup/bin.sql;
5.2.2 模拟企业单库故障案例
环境：
正在运行的网站系统，mysql-5.7.20 数据库，数据量50G，日业务增量1-5M。
备份策略：
每天23:00点，计划任务调用mysqldump执行全备脚本(备份并压缩)
故障时间点：
年底故障演练:模拟周三上午10点误删除数据库，并进行恢复.
5.2.2.1 恢复思路
1.停业务，挂维护页，避免数据的二次伤害
2.找一个临时库，恢复周二23：00全备
3.截取周二23：00 --- 周三10点误删除之间的binlog，恢复到临时库
4.测试可用性和完整性
5.方法一：直接使用临时库顶替原生产库，前端应用割接到新库
方法二：将误删除的表导出，导入到原生产库
6.开启业务
处理结果：经过20分钟的处理，最终业务恢复正常
5.2.2.2 准备数据
create database day1;
use day1;
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;
5.2.2.3 模拟周二23:00的全备
mysqldump -uroot -p123 -A -R -E --triggers --master-data=2 --single-transaction | gzip> /data/backup/full_`date +%F`.sql.gz
5.2.2.4 模拟周二 23：00到周三 10点之间数据变化
use day1;
insert into t1 values(11),(22),(33);
commit;
create table t2 (id int);
insert into t2 values(11),(22),(33);
commit;
5.2.2.5 模拟故障,删除库
drop database day1;
5.2.3 单库故障恢复过程
5.2.3.1 准备临时数据库（多实例3307）
[root@node1]$ systemctl start mysqld3307
[root@node1]$ mysql -uroot -p -S /data/3307/mysql.sock
5.2.3.2 解压全备文件，找出二进制备份
#解压全备文件
[root@node1 ~]$ gzip -d /data/backup/full_2020-12-01.sql.gz
#找出二进制备份
[root@node1 ~]$ egrep "^-- CHANGE.*mysql-bin" /data/backup/full_2020-12-01.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000018', MASTER_LOG_POS=234;
#找出删库前的GTID
[root@node1 ~]$ mysql -uroot -p123 -e "show binlog events in 'mysql-bin.000018'" | tail -n 100 | egrep -i "\<drop\>"
mysql-bin.000018 988 Query 6 1080 drop database day1
#只提取day1库的二进制备份
[root@node1 ~]$ mysqlbinlog --skip-gtids -d day1 --start-position=234 --stop-position=988 /data/binlog/mysql-bin.000018 > /data/backup/bin.sql
5.2.3.3 临时库恢复数据
[(none)]>set sql_log_bin=0;
[(none)]>source /data/backup/full_2020-12-01.sql;
[(none)]>source /data/backup/bin.sql;
5.2.3.4 导出故障库并恢复生产
[root@node1 ~]$ mysqldump -uroot -p123 -S /data/3307/mysql.sock -B day1 --triggers -E -R > /data/backup/day1.sql
[(none)]>set sql_log_bin=0;
[(none)]>source /data/backup/day1.sql;
6 MySQL物理备份工具-xtrabackup(XBK)
6.1 安装
安装依赖库
yum -y install libev
yum -y install perl-DBD-MySQL
yum -y install perl-Digest-MD5
下载rpm包，并安装
wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.21/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.21-1.el7.x86_64.rpm
rpm -ivh percona-xtrabackup-24-2.4.21-1.el7.x86_64.rpm
6.2 备份方式-物理备份
1.对于非Innodb表（比如 myisam），锁表cp数据文件，属于一种温备份。
2.对于Innodb的表（支持事务的），不锁表，拷贝数据页，最终以数据文件的方式保存下来，把一部分redo和undo一并备走，属于热备方式。
6.3 Innodb表的备份恢复流程
1.xbx备份执行的瞬间，立即触发ckpt，已提交的数据脏页，从内存刷写到磁盘，并记录此时的LSN号
2.备份时，拷贝磁盘数据页，并将备份过程中产生的redo和undo一起拷贝走，也就是checkpoint LSN之后的日志
3.在恢复之前，模拟Innodb“自动故障恢复”的过程，将redo（前滚）与undo（回滚）进行应用
4.恢复过程是cp备份到原来数据目录下
6.4 innobackupex使用
6.4.1 全备
配置my.cnf，设置客户端读取的socket
vim my.cnf
...
[client]
socket=/tmp/mysql.sock
全备命令，默认会在指定目录生成"date +%F_%H-%M-%S"样式的文件夹
innobackupex --user= --password= 指定目录
[root@node1 ~]$ innobackupex --user=root --password=123 /data/backup/ &> /tmp/xbk.log
全备命令，自定义文件夹名字
innobackupex --user= --password= --no-timestamp 指定目录/自定的文件夹名
[root@node1 ~]$ innobackupex --user=root --password=123 --no-timestamp /data/backup/full_`date +%F` &> /tmp/xbk.log
6.4.2 备份目录下生成的额外文件
xtrabackup_binlog_info
备份时刻的binlog信息，记录了备份时binlog文件名和当时的结束的position，可以用来作为截取binlog时的起点。
[root@node1 ~]$ cat /data/backup/2020-12-02_16-12-21/xtrabackup_binlog_info
mysql-bin.000019 234 023d38e9-330d-11eb-8c53-000c294f5f22:1-5,
9b83cd43-33ca-11eb-be45-000c294f5f22:1-7
xtrabackup_checkpoints
备份时刻的LSN信息和备份结束后redo的LSN信息
[root@node1 ~]$ cat /data/backup/2020-12-02_16-12-21/xtrabackup_checkpoints
backup_type = full-backuped #备份类型是全备
from_lsn = 0 #上次所到达的LSN号(对于全备就是从0开始,对于增量有别的显示方法)
to_lsn = 85055709 #备份开始时间(ckpt)点数据页的LSN
last_lsn = 85055718 #备份结束后，redo日志最终的LSN，如果备份期间没有任何数据变化，last_lsn-9=to_lsn
compact = 0
recover_binlog_info = 0
flushed_lsn = 85055718
xtrabackup_info
汇总的备份信息
[root@node1 ~]$ cat /data/backup/2020-12-02_16-12-21/xtrabackup_info
uuid = 1b601048-3476-11eb-8073-000c294f5f22
name =
tool_name = innobackupex
tool_command = --user=root --password=... /data/backup/
tool_version = 2.4.21
ibbackup_version = 2.4.21
server_version = 5.7.20-log
start_time = 2020-12-02 16:12:21
end_time = 2020-12-02 16:12:23
lock_time = 0
binlog_pos = filename 'mysql-bin.000019', position '234', GTID of the last change '023d38e9-330d-11eb-8c53-000c294f5f22:1-5,
9b83cd43-33ca-11eb-be45-000c294f5f22:1-7'
innodb_from_lsn = 0
innodb_to_lsn = 85055709
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N
xtrabackup_logfile
备份的redo file
6.4.3 全备的恢复
全备恢复，必须要原数据目录下没有文件，只适合数据库全崩的情况下
6.4.3.1 整理备份命令（--apply-log）
将redo进行重做，已提交的写到数据文件，未提交的使用undo回滚掉。模拟了CSR的过程
innobackupex --apply-log 备份文件夹
innobackupex --apply-log /data/backup/2020-12-02_16-12-21/
6.4.3.2 恢复备份命令（--copy-back）
前提：
1.被恢复的数据目录是空的
2.被恢复的数据库的实例是关闭的
恢复备份
innobackupex --copy-back 备份文件夹
innobackupex --copy-back /data/backup/2020-12-02_16-12-21/
更改文件所有者，因为是以root身份进行的文件恢复
chown -R mysql:mysql /data/
启动数据库
systemctl start mysqld
6.4.4 innobackupex 增量备份(incremental)
（1）增量备份的方式，是基于上一次备份进行增量
（2）增量备份无法单独恢复，必须基于全备进行恢复
（3）所有增量必须要按顺序合并到全备中（LSN号联系）
（4）所有备份都必须要--apply-log进行整理整理
（5）除最后一个增量备份外，前序所有备份需要只redo不undo(--redo only)
6.4.4.1 innobackupex 增量备份命令
增量备份命令，自定义文件夹名字
innobackupex --user= --password= --no-timestamp --incremental --incremental-basedir=上一次备份的文件夹
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/inc1 &>/tmp/inc1.log
6.4.4.2 innobackupex 增量备份合并
因为undo会让last_lsn变化，而不是checkpoints文件中记录的值，所以进行增量合并时，除最后一个增量备份外，其余备份（包括第一次全备）只合并redo
innobackupex --apply-log [--redo-only，除最后一个备份，前序备份需要使用此参数] --incremental-dir=增量备份文件夹 全备文件夹
innobackupex --apply-log --redo-only --incremental-dir=/data/backup/inc1 /data/backup/full
6.4.5 增量备份模拟
6.4.5.1 模拟周日全备
innobackupex --user=root --no-timestamp --password=123 /data/backup/full_`date +%F`
6.4.5.2 模拟周一数据变化
db01 [(none)]>create database test charset utf8;
db01 [(none)]>use test;
db01 [test]>create table t1 (id int);
db01 [test]>insert into t1 values(1),(2),(3);
db01 [test]>commit;
6.4.5.3 模拟第一次增量备份（周一）
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/data/backup/full_2020-12-02 /data/backup/inc1&> /tmp/inc1.log
6.4.5.4 模拟周二数据
db01 [test]>create table t2 (id int);
db01 [test]>insert into t2 values(1),(2),(3);
db01 [test]>commit;
6.4.5.5 模拟第二次增量备份（周二）
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/data/backup/inc1 /data/backup/inc2&> /tmp/inc2.log
6.4.5.6 模拟周三数据变化
db01 [test]>create table t3 (id int);
db01 [test]>insert into t3 values(1),(2),(3);
db01 [test]>commit;
db01 [test]>drop database cs;
6.4.6 增量备份恢复
6.4.6.1 检查备份
- 一次备份的
last_lsn-to_lsn=9，证明备份期间没有数据变化 - 相邻的两次备份，
上一次备份的last_lsn-本次备份的from_lsn=9，证明备份是连续的，否则备份不连续中间丢失了数据
6.4.6.2 备份整理（apply-log）+合并备份（full+inc1+inc2）
1.整理全备
innobackupex --redo-only --apply-log /data/backup/full_2020-12-02/
2.合并inc1到full中
innobackupex --redo-only --apply-log --incremental-dir=/data/backup/inc1 /data/backup/full_2020-12-02/
3.合并inc2到full中
innobackupex --apply-log --incremental-dir=/data/backup/inc2 /data/backup/full_2020-12-02/
4.对比full和inc2中的xtrabackup_checkpoints文件
5.最终整理全备
innobackupex --apply-log /data/backup/full_2020-12-02/
6.二进制日志截取
找到起点binlog文件和position号(GTID)，通过最后的增量备份看出已经使用的GTID是1-7
[root@node1 ~]$ cat /data/backup/inc2/xtrabackup_binlog_info
mysql-bin.000001 1601 e1d340aa-3498-11eb-b135-000c294f5f22:1-7
找到终点，drop操作的GTID是10，所以恢复时截取到9
[root@node1 ~]$ mysql -uroot -p123 -e "show binlog events in 'mysql-bin.000001'" | grep -C 5 "drop"
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql-bin.000001 1828 Query 6 1900 BEGIN
mysql-bin.000001 1900 Table_map 6 1945 table_id: 589 (test.t4)
mysql-bin.000001 1945 Write_rows 6 1995 table_id: 589 flags: STMT_END_F
mysql-bin.000001 1995 Xid 6 2026 COMMIT /* xid=131 */
mysql-bin.000001 2026 Gtid 6 2091 SET @@SESSION.GTID_NEXT= 'e1d340aa-3498-11eb-b135-000c294f5f22:10'
mysql-bin.000001 2091 Query 6 2183 drop database test
截取二进制日志
mysqlbinlog --skip-gtids --include-gtids='e1d340aa-3498-11eb-b135-000c294f5f22:8-9' /data/binlog/mysql-bin.000001 > /data/backup/inc2_bin.sql
6.4.6.3 恢复数据
将全备拷贝到测试库
cp -a /data/backup/full_2020-12-02/* /data/3307/data/
更改权限
chown -R mysql:mysql /data/3307/
恢复二进制备份
[(none)]>set sql_log_bin=0;
[(none)]>source /data/backup/inc2_bin.sql
