备份恢复

备份恢复

1. 介绍

最后一根救命稻草。备份主要时用来恢复数据。

2. DBA在备份恢复工作职责

2.1 设计备份策略和恢复策略

备份策略：
1. 备份的时间
凌晨.根据公司的实际业务情况，一般选择业务低谷。
2. 备份的周期
每天
每周
3. 备份的方式
全备、增量
逻辑、物理 (备份sql语句或者磁盘)

恢复策略
1. 方案。
2. 准确性。
3. 需要多长时间。

2.2 检查备份

1. 存在性。
2. 大小.
3. 日志

2.3 定期恢复演练

半年 , 每个季度做一下备份恢复演练 , 故障恢复演练(很有必要)

2.4 故障恢复。

提前写好方案

2.5 迁移升级

备份恢复 , 主从方式进行迁移
平滑迁移 无缝迁移

3. 备份工具介绍

3.1 逻辑备份工具

3.1.1 主流： mysqldump （MDP）

基于SQL（create database ,create table , insert into）语句的备份。
使用场景： 100G以内，比较常用的就是逻辑备份。
优点： 自带工具，不需要单独安装；文本形式存储，便于查看处理；压缩比较高，节省空间。
缺点： 备份时间较长。恢复时间更长（4-6倍）。

3.1.2 其他的： 自行扩展

mydumper 
mysqlpump 
into outfile/load data infile 

3.2 物理备份工具

Percona - Xtrabackup(xbk,pbk,pxb)
备份数据文件。
优点：备份、恢复速度更快。
缺点：二进制方式，可读性差；压缩比较低，浪费空间。
使用场景： 100G-TB级别数据量，一般采用xbk。

4. mysqldump 应用。

4.1 备份方式：

基于SQL（create database ,create table , insert into）语句的备份。
针对InnoDB表可以实现非锁定备份。原理上是通过MVCC中的快照技术进行备份。
针对非InnoDB表，是启用了锁表备份，FTWRL（global read lock）。

4.2 参数应用：

4.2.1 连接参数

-u 
-p 
-S 
-h 
-P 

4.2.2 备份参数

-A   全备参数 ：
[root@db01 ~]# mysqldump -uroot -p123 -A >/opt/full.sql

-B   单库或多库备份：
[root@db01 ~]# mysqldump -uroot -p123 -B world test >/opt/db.sql

备份库下的单表或多表： 
[root@db01 opt]# mysqldump -uroot -p123 world city country >/opt/tab.sql
注意： 单表或多表备份，在恢复时需要提前创建库，use到库中再恢复。

--master-data=2
功能： 
1. 备份时自动生成，当前的binlog位置信息
2. 自动开启锁表备份功能，如果开了--single-transaction，可以减少global read lock.

--single-transaction
功能：InnoDB “热备”。 对于InnoDB表不锁表，开启一执行快照备份。


-R -E --triggers 特殊对象备份
mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers >/opt/full.sql

--max-allowed-packet=128M
1153 - Got a packet bigger than 'max_allowed_packet' bytes 
mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers --max-allowed-packet=128M >/opt/full.sql

mysqldump -uroot -p123 -A  -R  --triggers -E  --master-data=2  --single-transaction --max-allowed-packet=128M |gzip > /opt/full_$(date +%F).sql.gz

[root@db01 opt]# mysqldump -uroot -p123 -A  -R  --triggers -E  --master-data=2  --single-transaction --max-allowed-packet=128M |bzip2 -9   > /opt/full_$(date +%F).sql.bz2

4.3 MDP+binlog生产故障恢复模拟

4.3.0 模式环境

mysql> create database mdp charset utf8mb4;
mysql> create table t1(id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;

4.3.1 模拟全备

[root@db01 opt]# mysqldump -uroot -p123 -A  -R  --triggers -E  --master-data=2  --single-transaction --max-allowed-packet=128M |bzip2 -9   > /opt/full_$(date +%F).sql.bz2

4.3.2 模拟新的数据变化

mysql> create table t2 (id int);
mysql> insert into t2 values(1),(2),(3);
mysql> commit;

4.3.3 搞破坏

mysql> drop database mdp;

恢复：

1. 检查备份。

SET @@GLOBAL.GTID_PURGED='09a8c1af-6e45-11ea-a381-000c29248f69:1-10';
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=1387;

2. 截取日志

起点： 
position ---》mysql-bin.000009', MASTER_LOG_POS=1387;
GTID     ---》09a8c1af-6e45-11ea-a381-000c29248f69:11
终点： 
GTID：    09a8c1af-6e45-11ea-a381-000c29248f69:12
position  mysql-bin.000009 | 1874

[root@db01 opt]# mysqlbinlog  --skip-gtids --include-gtids='09a8c1af-6e45-11ea-a381-000c29248f69:11-12' /data/3306/binlog/mysql-bin.000009 >/opt/bin.sql

3. 恢复数据

mysql> set sql_log_bin=0;
mysql> source /opt/full_2020-03-27.sql 
mysql> source /opt/bin.sql
mysql> set sql_log_bin=1;

练习： mysqldump备份恢复
5. 必做题： 模拟故障恢复 rm -rf /data/3306/data/*
6. 扩展题： 模拟故障恢复 rm -rf /data/3306/*

5. percona-xtrabackup 应用

5.1 介绍

物理备份工具。备份数据文件。

5.2 下载安装

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
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-*

5.3 配置

[client]
socket=/tmp/mysql.sock

5.4 xbk备份恢复原理

备份：
InnoDB表：
1、xbk备份执行的瞬间,立即触发ckpt,已提交的数据脏页,从内存刷写到磁盘,并记录此时的LSN号
2、备份InnoDB表时，拷贝 ibd 、ibdata、undo、ibtmp1，将备份期间产生的redo截取拷贝，记录LSN

非InnoDB表：

1. 触发 FTWRL 全局锁，关闭binlog记录
2. 拷贝非InnoDB表数据。

数据完成后
记录binlog位置，停止redo拷贝，记录last lsn，记录所有备份日志到指定日志文件中，解锁

恢复：
1. prepare 备份：利用了InnoDB CSR的功能，使用redo进行前滚，undo进行回滚
2. 恢复备份： cp 文件至源路径。

5.5 应用

5.5.1 全备

innobackupex --user=root --password=123 --no-timestamp  /opt/full/xbkfull_`date +%F`

5.5.2 全备恢复演示

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

 prepare 全备 
[root@db01 ~]# innobackupex  --apply-log  /opt/full/xbkfull_2020-03-27/

#恢复全备 
[root@db01 ~]# innobackupex --copy-back /opt/full/xbkfull_2020-03-27/
或者： 
[root@db01 data]# cp -r /opt/full/xbkfull_2020-03-27/* /data/3306/data/
[root@db01 ~]# chown -R mysql.mysql /data/
[root@db01 data]# /etc/init.d/mysqld start

6. Xtrabackup 全备+增量应用

6.1 增量备份原理

xrabackup自动检查LSN号码的变化，将每天LSN发生变化的数据页备份走。
增量备份，必须要依赖全备。恢复时，必须合并到全备。

6.2 查看备份集

xtrabackup_binlog_info ：binlog位置点
xtrabackup_checkpoints ：LSN号码记录
xtrabackup_info ：备份总览
xtrabackup_logfile ：部分redo

6.3 增量备份应用

6.3.1 模拟数据

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

6.3.2 模拟周日全备

innobackupex --user=root --password=123 --no-timestamp /opt/full

6.3.3 模拟周一数据

mysql> use incremental 
mysql> create table t2 (id int);
mysql> insert into t2 values(1),(2),(3);
mysql> commit;

6.3.4 模拟周一增量

innobackupex --user=root --password=123 --no-timestamp  --incremental-basedir=/opt/full  --incremental  /opt/inc1

6.3.3 模拟周二数据

 use incremental 
 create table t3 (id int);
 insert into t3 values(1),(2),(3);
 commit;

6.3.5 模拟周二增量

innobackupex --user=root --password=123 --no-timestamp  --incremental-basedir=/opt/inc1  --incremental  /opt/inc2

6.3.6 模拟周三数据

 use incremental 
 create table t4 (id int);
 insert into t4 values(1),(2),(3);
 commit;

6.3.7 搞破坏

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

6.4 故障恢复 full+inc1+inc2+binlog

6.4.1 Prepare Base Full 处理基础全备

[root@db01 ~]# innobackupex --apply-log --redo-only /opt/full/

6.4.2 merging inc1 to BASE FULL ,合并处理inc1增量到全备

innobackupex --apply-log --redo-only  --incremental-dir=/opt/inc1 /opt/full 

6.4.3 merging inc2 to BASE FULL ,合并处理inc2增量到全备

innobackupex --apply-log  --incremental-dir=/opt/inc2  /opt/full 

6.4.4 最后一次全备整理

[root@db01 opt]# innobackupex --apply-log  /opt/full/

6.4.5 恢复数据

[root@db01 full]# chown -R mysql.mysql /opt/*
vim /etc/my.cnf
#datadir=/data/3306/data
datadir=/opt/full

[root@db01 full]# /etc/init.d/mysqld start
Starting MySQL... SUCCESS!

6.4.6 截取恢复binlog

[root@db01 inc2]# cat /opt/inc2/xtrabackup_binlog_info 
mysql-bin.000009	1712	81c442f5-7011-11ea-b4da-000c29248f69:1-7

[root@db01 inc2]# mysqlbinlog --skip-gtids --include-gtids='81c442f5-7011-11ea-b4da-000c29248f69:8-9' /data/3306/binlog/mysql-bin.000009 >/opt/bin.sql

6.4.7 清空binlog日志，再发起全备。

mysql> reset master;
Query OK, 0 rows affected (0.03 sec)

彩蛋：

7. 从mysqldump 全备中获取 库和表的备份

7.1 获得表结构

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

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

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

7.3 获取单库的备份

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

8. 从 xtrabackup 单独恢复表思路？

8.1. 单独起实例，备份数据拉起来

8.2. 截取日志恢复此实例

8.3. 故障表，表空间迁移恢复到生产