备份和恢复

1. 运维人员在备份恢复方面的职责

1.设计备份策略
    （1）备份内容：数据、日志
    （2）备份容量：大小
    （3）备份周期：
            1.每天全备
            2.周期备份+增量备份
            3.周期全备+日志备份
    （4）备份时间：一般是晚上备份
    （5）备份的目标位置：

2.日常的备份检查
    通过日志检查
    通过备份的内容检查
    通过备份的大小检查

3.定期的恢复演练
    建议每半年做一次

4.故障时的恢复
    快速准确恢复数据

5.平台数据迁移
    同构平台
    异构平台

2.备份工具介绍

1.介绍
mysqldump （MDP）
Percona Xtrabackup (xbk/pbk/pxb)


2. 区别

MDP： 逻辑备份，SQL文件，文本形式，可读性高，便于处理，压缩比高，备份相对较慢比较适合于100G以内的备份
xbk： 物理备份，数据文件，可读性较低，压缩比相对较低，比较适合于100G内的备份

100 PB   ---拆分数据库----   MDP   ------压缩----

3.mysqldump

1.备份逻辑
将磁盘数据，加载到历史表，转换为SQL（建库建表插入语句）

2.核心参数
（1） 连接参数
本地： -u -p -S
远程： -u -p -H -P

(2) 备份参数
基础参数：
--- 1.全备参数
[root@db01 data_3306]# mkdir -p /data/backup
[root@db01 data_3306]# chown -R mysql.mysql /data/backup
[root@db01 data_3306]# mysqldump -uroot -p123 -A >/data/backup/full.sql

--- 2. 单库备份
[root@db01 data_3306]# mysqldump -uroot -p123 -B bgx oldboy test world mysql > /data/backup/db.sql


-- 3. 单表或多表备份
[root@db01 data_3306]# mysqldump -uroot -p123 world city country > /data/backup/tab.sql

-- 4. 只导出建表语句
[root@db01 data_3306]# mysqldump -uroot -p123 world city country --no-data > /data/backup/tab1.sql


高级参数
-- 5. --master-data=2
    (1) 自动记录备份时的binlog filename 及pos
(2) 自动加锁和解锁
(3) 加了--single-transaction , 会有不一样的效果( global read lock is only taken a short time )
 mysqldump -uroot -p123 -A --master-data=2 >/data/backup/full.sql

备份结果：30 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1162;

-- 6. --single-transaction
开启独立事务，备份InnoDB表的一致性快照。实现InnoDB表的热备功能

补充：对于非InnoDB表（开启FTWRL全局读锁 flush tables with read lock），对于MyISAM库下的系统表备份的时候，回家global read lock


-- 7. 特殊的数据库对象
-R            存储过程和函数
--trigers     触发器
-E            时间

-- 8. --max_allowed_packet=128M
使用场景： 在数据库有超大表时，需要加此参数

-- 9. 终极备份语句
mysqldump -uroot -p -A --master-data=2  --single-transaction -R  --triggers -E  --max_allowed_packet=128M  |gzip >/data/backup/full_`date +%F`.sql.gz

3.3 模拟故障和恢复（mysqldump 全备 + binlog）

(1) 模拟原始数据 
mysql> create database mdp charset utf8mb4;
mysql> use mdp
mysql> create table t1 (id int);
mysql> create table t2 (id int);
mysql> create table t3 (id int);
mysql> insert into t1 values(1);
mysql> commit;
mysql> insert into t2 values(1);
mysql> commit;
mysql> insert into t3 values(1);
mysql> commit;
(2) 模拟全备
mysqldump -uroot -p -A --master-data=2  --single-transaction -R  --triggers -E  --max_allowed_packet=128M  |gzip >/data/backup/full_`date +%F`.sql.gz

(3) 模拟新的数据
use mdp
insert into t1 values(2);
commit;
insert into t2 values(2);
commit;
insert into t3 values(2);
commit;

（4） 搞破坏
drop database mdp;

(5) 恢复备份
-- 1. 挂维护页
-- 2. 找测试库
-- 3. 准备备份
    full
    截取 binlog 部分
-- 4. 恢复全备+ binlog 到测试库
-- 5. 导出故障库数据，恢复到生产
-- 6. 撤维护页

恢复过程：
    1.准备备份
vim /data/backup/full_2019-12-26.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=1602;
| mysql-bin.000005 | 2420 | Query          |         6 |        2509 | drop database mdp 
    2. 截取二进制日志
mysqlbinlog --skip-gtids --start-position=1602 --stop-position=2420 /data/mysql/binlog_3306/mysql-bin.000005 >/data/backup/bin.sql

    3. 恢复备份
set sql_log_bin=0;
source /data/backup/full_2019-12-26.sql
source /data/backup/bin.sql
set sql_log_bin=1;

    4.检查数据
show tables;
select * from t1;

扩展： 从全备中提取 单库 单表 的数据进行恢复
1、获得表结构
# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `city`/!d;q'  full.sql>createtable.sql
2、获得INSERT INTO 语句，用于数据的恢复
# grep -i 'INSERT INTO `city`'  full.sqll >data.sql &
3.获取单库的备份
# sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql >world.sql