数据库学习day07：备份恢复

1. 运维在数据库备份恢复方面的职责

1.1设计备份策略

(1) 备份什么数据?
    数据部分
    二进制日志
    配置文件
(2) 什么时间备份
    低估期
(3) 备份周期 
    每周,每天...
(4) 备份的方式
    逻辑备份: mysqldump,binlog
    物理备份: xtrabackup
    全备
    增量
    热备
    冷备
(5) 备份脚本设计
    略

1.2 日常备份的检查

(1) 日志 
(2) 备份集的大小和完整性

1.3 定期进行备份恢复演练(测试库)

一季度 或者 半年

1.4 故障恢复

通过现有备份,能够将数据库恢复到故障之前的时间点.

1.5 迁移

同版本 
异构平台

1. 停机时间
2. 回退方案

2. 备份类型

2.1 热备

在数据库正常业务时,备份数据,并且能够一致性恢复  Innodb支持
对业务影响非常小

2.2 温备

锁表备份,只能查询不能修改（MyISAM）
影响到写入操作

2.3 冷备

关闭数据库业务,数据库没有任何变更的情况下,进行备份数据.
业务停止

3. 备份方式及备份工具

3.1 逻辑备份工具

基于SQL语句进行备份
mysqldump       *****
mysqlbinlog     *****

3.2 物理备份工具

基于磁盘数据文件备份
xtrabackup(XBK) ：percona 第三方   *****
MySQL Enterprise Backup（MEB）

4. 逻辑备份和物理备份的比较

4.1 mysqldump (MDP)

优点：
1.不需要下载安装
2.备份出来的是SQL，文本格式，可读性高,便于备份处理
3.压缩比较高，节省备份的磁盘空间

缺点：
4.依赖于数据库引擎，需要从磁盘把数据读出
然后转换成SQL进行转储，比较耗费资源，数据量大的话效率较低
建议：
100G以内的数据量级，可以使用mysqldump
超过TB以上，我们也可能选择的是mysqldump，配合分布式的系统
1EB  =1024 PB =1000000 TB

4.2 xtrabackup(XBK,PBK)

优点：
1.类似于直接cp数据文件，不需要管逻辑结构，相对来说性能较高
缺点：
2.可读性差
3.压缩比低，需要更多磁盘空间
建议：
>100G<TB

5. 备份策略

备份方式：
全备:全库备份，备份所有数据
增量:备份变化的数据
逻辑备份=mysqldump+mysqlbinlog
物理备份=xtrabackup_full+xtrabackup_incr+binlog或者xtrabackup_full+binlog
备份周期:
根据数据量设计备份周期
比如：周日全备，周1-周6增量

6. 备份工具使用-mysqldump

6.1 mysqldump (逻辑备份的客户端工具)

6.1.1 客户端通用参数

-u  -p   -S   -h  -P    
本地备份:
mysqldump -uroot -p  -S /tmp/mysql.sock
远程备份:
mysqldump -uroot -p  -h 10.0.0.51 -P3306

6.1.2 备份专用基本参数

-A

例子1:
[root@db01 ~]# mkdir -p /data/backup
mysqldump -uroot -p -A >/data/backup/full.sql
Enter password: 

mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 

# 补充:
# 1.常规备份是要加 --set-gtid-purged=OFF,解决备份时的警告
# [root@db01 ~]# mysqldump -uroot -p123 -A  --set-gtid-purged=OFF  >/backup/full.sql
# 2.构建主从时,做的备份,不需要加这个参数
# [root@db01 ~]# mysqldump -uroot -p123 -A    --set-gtid-purged=ON >/backup/full.sql

-B 单库或多库备份

说明：生产中需要备份，生产相关的库和MySQL库
例子2 :
mysqldump -B mysql school --set-gtid-purged=OFF >/data/backup/b.sql

备份单个或多个表

[root@db01 ~]# mysqldump -uroot -p123  world city country >/data/backup/tab.sql
备份world库下的city表和country表

[root@db01 ~]# mysqldump -uroot -p123  world   >/data/backup/tab1.sql
备份world库下的所有的表

说明: 单表或多表备份,在恢复时,需要先创建库,use 库中,再source

6.1.3 高级参数应用

特殊参数使用（必须要加）

-R            备份存储过程及函数
--triggers  备份触发器
-E             备份事件

例子4:
[root@db01 backup]# mysqldump -uroot -p -A -R -E --triggers >/data/backup/full.sql

-F 在备份开始时,刷新一个新binlog日志

mysqldump -uroot -p  -A  -R --triggers -F >/bak/full.sql

--master-data=2

(1) 以注释方式,在备份文件中,自动记录二进制日志名+位置点
(2) 会自动进行锁表操作.(innodb除外).

mysqldump -uroot -p  -A  -R --triggers --master-data=2   >/back/world.sql
[root@db01 ~]# grep 'CHANGE' /backup/world.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000035', MASTER_LOG_POS=194;

功能：
（1）在备份时，会自动记录，二进制日志文件名和位置号
        0 默认值
        1  以change master to命令形式，可以用作主从复制
         2  以注释的形式记录，备份时刻的文件名+postion号
（2） 自动锁表
（3）如果配合--single-transaction，只对非InnoDB表进行锁表备份，InnoDB表进行“热“”备，实际上是实现快照备份。
**--single-transaction  一致性快照备份（InnoDB）**

innodb 存储引擎开启热备(快照备份)功能       
master-data可以自动加锁
（1）在不加--single-transaction ，启动所有表的温备份，所有表都锁定
（1）加上--single-transaction ,对innodb进行快照备份,对非innodb表可以实现自动锁表功能
例子6: 备份必加参数
mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql

--set-gtid-purged=auto

auto , on   默认为on
off 
使用场景:
1. --set-gtid-purged=OFF,可以使用在日常备份参数中.
mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql
2. auto , on:在构建主从复制环境时需要的参数配置
mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=ON >/data/backup/full.sql

--max_allowed_packet=128M

备份大数据量表时候需要添加.
- Got a packet bigger than 'max_allowed_packet' bytes 

The maximum packet length to send to or receive from server.

终极备份语句

mysqldump -uroot -p123 -S /tmp/mysql.sock  -A --master-data=2  --single-transaction -R -E --triggers --max_allowed_packet=128M >/data/backup/full.sql  2>/dev/null

6.2 压缩备份并添加时间戳

例子：
mysqldump -uroot -p123 -A  -R  --triggers --master-data=2  --single-transaction|gzip > /backup/full_$(date +%F).sql.gz
mysqldump -uroot -p123 -A  -R  --triggers --master-data=2  --single-transaction|gzip > /backup/full_$(date +%F-%T).sql.gz

mysqldump备份的恢复方式（在生产中恢复要谨慎，恢复会删除重复的表）
set sql_log_bin=0;
source /backup/full_2018-06-28.sql

注意：
1、mysqldump在备份和恢复时都需要mysql实例启动为前提。
2、一般数据量级100G以内，大约15-45分钟可以恢复，数据量级很大很大的时候（PB、EB）
3、mysqldump是覆盖形式恢复的方法。

一般我们认为，在同数据量级，物理备份要比逻辑备份速度快.
逻辑备份的优势:
1、可读性强
2、压缩比很高

7. 企业故障恢复案例

7.1方案

数据量: 200G   
备份策略: 每周日23:00 mysqldump全备,周一到周六binlog备份
故障模拟: 模拟周三上午10点,数据损坏(DROP DATABASE)
演练过程: 
    1,将生产数据提前1周导入测试库.
    2,周日全备模拟 
    3,周一到周二binlog备份模拟
    4,周三模拟故障
    5,数据恢复
    6,数据校验
    7,模拟上线
演练结果:
    1, 在发生数据故障时,需要大约1:00-1:30小时左右可以恢复所有业务.
    2, 此恢复方案,数据恢复较慢,可以考虑更快恢复方案.

7.2 模拟过程:

7.2.1 模拟原始数据

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

7.2.2. 模拟周日全备

mysqldump -uroot -p123 -S /tmp/mysql.sock  -A --master-data=2  --single-transaction -R -E --triggers --max_allowed_packet=128M | gzip > /data/backup/full_$(date +%F).sql.gz

检查备份
[root@db01 /data/backup]# gunzip full_2019-11-20.sql.gz

7.2.3. 模拟周1的数据

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

7.2.4 周1晚上备份binlog

[root@db01 /data/binlog]# mysqladmin -uroot -p123 flush-logs
[root@db01 /data/binlog]# cp /data/binlog/mysql-bin.000002 /data/backup

7.2.5. 模拟周2的数据

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

7.2.6 周2晚上备份binlog

[root@db01 /data/binlog]# mysqladmin -uroot -p123 flush-logs
[root@db01 /data/binlog]# cp /data/binlog/mysql-bin.000003 /data/backup

7.2.7 周三数据损坏模拟

mysql> drop database mdp;

7.2.8 恢复数据:

(1) 备份检查  
    全备  
    binlog
(2) binlog位置点确认   grep "CHANGE" full.sql
    起点: -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=748;
        '79f1839c-0439-11ea-83bb-000c29d354c0:1-3' 
        
    终点:'79f1839c-0439-11ea-83bb-000c29d354c0:8' 
    
    '79f1839c-0439-11ea-83bb-000c29d354c0:4-7' 
    mysql-bin.000002 mysql-bin.000003    mysql-bin.000004  mysql-bin.000005

(3) 截取日志 

[root@db01 /data/binlog]# mysqlbinlog --include-gtids='79f1839c-0439-11ea-83bb-000c29d354c0:4-7' --skip-gtids mysql-bin.000002 mysql-bin.000003 mysql-bin.000004  mysql-bin.000005 >/data/backup/bin.sql

(5) 恢复数据
set sql_log_bin=0;
source /data/backup/full_2019-11-20.sql
source /data/backup/bin.sql
set sql_log_bin=1;

8.MySQL物理备份工具-xtrabackup(XBK、Xbackup)

8.1 介绍

percona公司研发的MySQL物理备份的工具.使用perl语言开发的.
https://www.percona.com/downloads/Percona-XtraBackup-LATEST/

8.2 安装

8.2.1 安装依赖包

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

8.2.2下载软件并安装

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

https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

yum -y install percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

[root@db01 ~]# innobackupex --version
innobackupex version 2.4.4 Linux (x86_64) (revision id: df58cf2)
[root@db01 ~]#

8.3 XBK备份原理

1. 自动判断引擎类型
InnoDB 
Non-InnoDB
2. InnoDB表 实现热备功能备份 
开始备份innoDB时,自动进程ckpt,将当前已经提交的事务数据,刷写到磁盘,会生成一个CKPT的LSN号(数据页的LSN).
cp 数据文件,ibdata ,ibd frm ,ibtmp ,redo,(只会拷贝备份期间产生的新的redo),拷贝完成后会产生一个LAST_LSN（redo日志的LSN）
说明：在5.7中last_lsn-ckptLSN=9,说明在备份过程中没有数据变化。
3. 非InnoDB 表 
自动开启  FTWRL (flush tables with read lock)  全局锁 
拷贝数据文件.
自动解锁.

4. 增量备份 
    基于上一次备份的last_lSN,检查数据页的变化,然后备份走.

面试题： xbk 在innodb表备份恢复的流程

  0、xbk备份执行的瞬间,立即触发ckpt,已提交的数据脏页,从内存刷写到磁盘,并记录此时的LSN号
  1、备份时，拷贝磁盘数据页，并且记录备份过程中产生的redo和undo一起拷贝走,也就是checkpoint LSN之后的日志
  2、在恢复之前，模拟Innodb“自动故障恢复”的过程，将redo（前滚）与undo（回滚）进行应用
  3、恢复过程是cp 备份到原来数据目录下

8.4 innobackupex 备份工具应用

vim /etc/my.cnf
[client]
socket=/tmp/mysql.sock

8.4.1 全备

（1）全备

[root@db01 ~]# innobackupex --user=root --password=123 /data/backup
以时间命名备份目录

备份集中多出来的文件：
-rw-r----- 1 root root       24 Jun 29 09:59 xtrabackup_binlog_info
-rw-r----- 1 root root      119 Jun 29 09:59 xtrabackup_checkpoints
-rw-r----- 1 root root      489 Jun 29 09:59 xtrabackup_info
-rw-r----- 1 root root     2560 Jun 29 09:59 xtrabackup_logfile

xtrabackup_binlog_info ：（备份时刻的binlog位置）
[root@db01 full]# cat xtrabackup_binlog_info 
mysql-bin.000003    536749
79de40d3-5ff3-11e9-804a-000c2928f5dd:1-7
记录的是备份时刻，binlog的文件名字和当时的结束的position，可以用来作为截取binlog时的起点。

xtrabackup_checkpoints ：
backup_type = full-backuped
from_lsn = 0            上次所到达的LSN号(对于全备就是从0开始,对于增量有别的显示方法)
to_lsn = 160683027      备份开始时间(ckpt)点数据页的LSN    
last_lsn = 160683036    备份结束后，redo日志最终的LSN
compact = 0
recover_binlog_info = 0
（1）备份时刻，立即将已经commit过的，内存中的数据页刷新到磁盘(CKPT).开始备份数据，数据文件的LSN会停留在to_lsn位置。
（2）备份时刻有可能会有其他的数据写入，已备走的数据文件就不会再发生变化了。
（3）在备份过程中，备份软件会一直监控着redo的undo，如果一旦有变化会将日志也一并备走，并记录LSN到last_lsn。
从to_lsn  ----》last_lsn 就是，备份过程中产生的数据变化.
生产备份:
innobackupex --user=root --password=123 --no-timestamp /data/backup/full_`date +%F`

--no-timestamp:自己定制目录名

（2）全备的恢复
准备备份（Prepared）

将redo进行重做，已提交的写到数据文件，未提交的使用undo回滚掉。模拟了CSR的过程
[root@db01 ~]# innobackupex --apply-log  /backup/full

--apply-log     Prepare a backup in BACKUP-DIR by applying the
                      transaction log file named "xtrabackup_logfile" located
                      in the same directory. Also, create new transaction logs.
                      The InnoDB configuration is read from the file
                      "backup-my.cnf"

恢复备份

前提：
1、被恢复的目录是空
2、被恢复的数据库的实例是关闭
systemctl stop mysqld

创建新目录

[root@db01 backup]# mkdir /data/mysql1

数据授权

chown -R mysql.mysql /data/mysql1

恢复备份

[root@db01 full]# cp -a /backup/full/* /data/mysql1/

启动数据库

vim /etc/my.cnf
datadir=/data/mysql1
[root@db01 mysql1]# chown -R mysql.mysql /data/mysql1
systemctl start mysqld

8.4.2 innobackupex 增量备份(incremental)

（1）增量备份的方式，是基于上一次备份进行增量。
（2）增量备份无法单独恢复。必须基于全备进行恢复。
（3）所有增量必须要按顺序合并到全备中。

增量备份命令

（1）删掉原来备份
略.
（2）全备（周日）
[root@db01 backup]# innobackupex --user=root --password --no-timestamp /backup/full >&/tmp/xbk_full.log
（3）模拟周一数据变化
db01 [(none)]>create database cs charset utf8;
db01 [(none)]>use cs
db01 [cs]>create table t1 (id int);
db01 [cs]>insert into t1 values(1),(2),(3);
db01 [cs]>commit;

（4）第一次增量备份（周一）
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full  /backup/inc1 &>/tmp/inc1.log
（5）模拟周二数据
db01 [cs]>create table t2 (id int);
db01 [cs]>insert into t2 values(1),(2),(3);
db01 [cs]>commit;
（6）周二增量
 innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc1  /backup/inc2  &>/tmp/inc2.log
（7）模拟周三数据变化
db01 [cs]>create table t3 (id int);
db01 [cs]>insert into t3 values(1),(2),(3);
db01 [cs]>commit;
db01 [cs]>drop database cs;

恢复到周三误drop之前的数据状态

恢复思路：
1.  挂出维护页，停止当天的自动备份脚本
2.  检查备份：周日full+周一inc1+周二inc2，周三的完整二进制日志
3. 进行备份整理（细节），截取关键的二进制日志（从备份——误删除之前）
4. 测试库进行备份恢复及日志恢复
5. 应用进行测试无误，开启业务
6. 此次工作的总结

恢复过程

1. 检查备份
1afe8136-601d-11e9-9022-000c2928f5dd:7-9
2. 备份整理（apply-log）+合并备份（full+inc1+inc2）
(1) 全备的整理
[root@db01 one]# innobackupex --apply-log --redo-only /data/backup/full
(2) 合并inc1到full中
[root@db01 one]# innobackupex --apply-log --redo-only --incremental-dir=/data/backup/inc1 /data/backup/full
(3) 合并inc2到full中
[root@db01 one]# innobackupex --apply-log  --incremental-dir=/data/backup/inc2 /data/backup/full
(4) 最后一次整理全备
[root@db01 backup]#  innobackupex --apply-log  /data/backup/full
3. 截取周二 23:00 到drop 之前的 binlog 
[root@db01 inc2]# mysqlbinlog --skip-gtids --include-gtids='1afe8136-601d-11e9-9022-000c2928f5dd:7-9' /data/binlog/mysql-bin.000009 >/data/backup/binlog.sql
4. 进行恢复
[root@db01 backup]# mkdir /data/mysql/data2 -p
[root@db01 full]# cp -a * /data/mysql/data2
[root@db01 backup]# chown -R mysql.  /data/*
[root@db01 backup]# systemctl stop mysqld
vim /etc/my.cnf
datadir=/data/mysql/data2
systemctl start mysqld
Master [(none)]>set sql_log_bin=0;
Master [(none)]>source /data/backup/binlog.sql

--redo-only

--redo-only      This option should be used when preparing the base full
                      backup and when merging all incrementals except the last
                      one. This forces xtrabackup to skip the "rollback" phase
                      and do a "redo" only. This is necessary if the backup
                      will have incremental changes applied to it later. See
                      the xtrabackup documentation for details.

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