第二十周-day88-数据库day10-备份恢复02

1. 上节回顾:

1.  binlog 
show master status;
oldguo[(none)]>show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000013 |      194 |              |                  | 545fd699-be48-11e9-8f0a-000c2980e248:1 |
+------------------+----------+--------------+------------------+----------------------------------------+

oldguo[(none)]>show binlog events in 'mysql-bin.000012';
mysqlbinlog  --start-position=   --stop-position   /data/binlog/mysql-bin.000004 >/backup/binlog.sql

set sql_log_bin=0;
source /backup/binlog.sql;
set sql_log_bin=1;
exit

mysqlbinlog  --skip-gtids  --include-gtids='545fd699-be48-11e9-8f0a-000c2980e248:1-10' --exclude-gtids=''

2.  slowlog

3.  mysqldump
-u  -p -h -P -S 
-A  -B  
-R -E --triggers 
--master-data=2
--single-transaction 
--set-gtid-purged=OFF
--max-allowed-packet=256M

 -------- __@      __@       __@       __@      __~@      __~@
 ----- _`\<,_    _`\<,_    _`\<,_     _`\<,_    _`\<,_    _`\<,_
 ---- (*)/ (*)  (*)/ (*)  (*)/ (*)  (*)/ (*)  (*)/ (*)  (*)/ (*) 下一知识点

2. 企业故障恢复案例(MDP)

源文资料 [oldguo] 链接, ctrl+F搜索“企业故障恢复案例”

2.1 背景环境：

正在运行的小型网站系统，mysql-5.7.20 数据库，数据量 50G.
每天23:00点，计划任务调用mysqldump执行全备脚本

2.2 故障时间点：

o(*￣︶￣*)o 面试保持专业性，此案例面试可以说为 故障演练

年底故障演练:模拟周三上午10点误删除数据库，并进行恢复.

2.3 恢复思路

1. 停止故障业务，挂维护页
2. 准备测试库，进行全库恢复
3. 截取从昨晚23:00的全备开始一直到故障时刻的 binlog日志
4. 测试数据的可用性和完整性
5. 将故障数据导出，导入到生产环境中
6. 撤掉维护页，开启业务

2.4 演练过程

2.4.1 模拟数据

mysql [none]>create database mdp charset utf8mb4;
mysql [none]>use mdp;
mysql [mdp]>create table t1(id int) engine=innodb charset=utf8mb4;
mysql [mdp]>insert into t1 values(1),(2),(3);

2.4.2 模拟昨天晚上23:00的全备(命令行执行)

mysqldump -uroot -p123456 -A -E -R --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M |gzip > /backup/full_$(date +%F).sql.gz

2.4.3 模拟备份后数据变化

mysql [none]>use mdp;
mysql [mdp]>insert into t1 values(11),(12),(13);
mysql [mdp]>commit;
mysql [mdp]>select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   11 |
|   12 |
|   13 |
+------+

2.4.5 模拟数据损坏(可以直接删库)

mysql [mdp]>drop database mdp;

2.4.6 恢复故障

(1) 停止故障业务，挂维护页
(此步骤略)

(2) 准备备份
全备准备：
[root@db01 ~]# cd /backup/
[root@db01 /backup]# gunzip full_2019-08-16.sql.gz 

binlog准备：
vim full_2019-08-16.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=1425;


(3) 确认日志的终点
mysql [mdp]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000009 |     1840 |              |                  | ae5de4c2-bf1e-11e9-8109-000c29c6fa61:1-21 |
+------------------+----------+--------------+------------------+-------------------------------------------+
mysql [mdp]>show binlog events in 'mysql-bin.000009';
...
| mysql-bin.000009 | 1686 | Gtid           |         6 |        1751 | SET @@SESSION.GTID_NEXT= 'ae5de4c2-bf1e-11e9-8109-000c29c6fa61:21' |
| mysql-bin.000009 | 1751 | Query          |         6 |        1840 | drop database mdp                                                  |

mysqlbinlog  --skip-gtids  --start-position=1425 --stop-position=1751 /data/binlog/mysql-bin.000009 >/backup/binlog.sql

2.4.7 进行恢复

set sql_log_bin=0;
source /backup/full_2019-08-16.sql
source /backup/binlog.sql
set sql_log_bin=1;

mysql [mdp]>select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   11 |
|   12 |
|   13 |
+------+

3. XBK(Percona-Xtrabackup)-物理备份工具

软件下载链接: 提取码: ahqk

3.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

下载软件并安装
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

将下载好的 Percona-Xtrabackup 安装:
yum -y install percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

[root@db01 ~]# rpm -qa |grep percona
percona-xtrabackup-24-2.4.12-1.el7.x86_64

3.2 介绍

物理备份工具, 类似于cp数据.

3.3 备份细节

非InnoDB：例如 MyISAM，自动锁表备份，会有短暂的全局锁.

InnoDB：
  1. 立即进行CKPT,将当前所有的已提交事务的脏页,立即刷写到磁盘上(to_lsn = 425544571)
  2. 拷贝所有InnoDB的数据文件,系统数据文件(ibdata1)也进行拷贝.
  3. 将备份过程中产生的redo截取并备份走(last_lsn = 425544580).

3.4 innobackupex

3.4.1 配置操作

[root@db01 ~]# cd /backup/
[root@db01 /backup]# mkdir xbk

配置文件中添加client客户端:
[root@db01 /backup]# vim /etc/my.cnf 
...
[client]
socket=/tmp/mysql.sock

3.4.2 全备

innobackupex --user=root --password=123456  /backup/xbk
innobackupex --user=root --password=123456  --no-timestamp  /backup/xbk/full

3.4.5 搞破坏

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

3.4.5 数据恢复准备(备份处理)

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

3.4.6 恢复数据

--copy-back
[root@db01 ~]# innobackupex --copy-back   /backup/xbk/full/ 

[root@db01 /data/3306/data]# chown -R mysql.mysql /data/*

3.4.7 数据恢复成功

[root@db01 /data/3306/data]# ls
auto.cnf       gtid            ibtmp1     performance_schema  xtrabackup_binlog_pos_innodb
binlog         ib_buffer_pool  mdp        school              xtrabackup_info
db01.pid       ibdata1         mysql      sys                 xtrabackup_master_key_id
db01-slow.log  ib_logfile0     mysql.log  test
db1            ib_logfile1     oldgirl    world

3.4.8 --apply-log 参数说明 (面试重点) ☆☆☆☆☆

  • 模仿了InnoDB引擎的ACSR的过程
  • 将备份集中地数据和日志的LSN追平
  • 利用redo进行前滚
  • 利用undo进行回滚

3.5 备份集的认识

3.5.1 备份集文件

[root@db01 /backup/xbk/full]# ll xtrabackup_*
-rw-r----- 1 root root      64 Aug 16 10:43 xtrabackup_binlog_info
-rw-r--r-- 1 root root      22 Aug 16 10:50 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root     117 Aug 16 10:50 xtrabackup_checkpoints
-rw-r----- 1 root root     561 Aug 16 10:43 xtrabackup_info
-rw-r----- 1 root root 8388608 Aug 16 10:45 xtrabackup_logfile
-rw-r--r-- 1 root root       1 Aug 16 10:50 xtrabackup_master_key_id

3.5.2 备份集文件认识

[root@db01 /backup/xbk/full]# cat xtrabackup_binlog_info   #存储的是binlog截取的起始点信息(position,gtid)
mysql-bin.000009    1840    ae5de4c2-bf1e-11e9-8109-000c29c6fa61:1-21

[root@db01 /backup/xbk/full]# cat xtrabackup_binlog_pos_innodb 
mysql-bin.000009    1686

[root@db01 /backup/xbk/full]# cat xtrabackup_checkpoints 
backup_type = full-prepared  #备份类型，默认全备
from_lsn = 0            #全备中是0，增量备份时是最起始的LSN号码
to_lsn = 425544571      #ckpt后数据页(ibd)的LSN号码
last_lsn = 425544580    #备份结束时,redo的LSN,(在当前5.7版本,会有9个LSN可以忽略) 
                        # to_lsn和last_lsn 差9位数字

[root@db01 /backup/xbk/full]# cat xtrabackup_info   3备份的具体参数 时间 （不叙述）

[root@db01 /backup/xbk/full]# file xtrabackup_logfile   #备份过程中的信息，不能打开
xtrabackup_logfile: data

4.XBK增量（incremental）备份

Xtrabackup企业级增量恢复实战
背景：
某大型网站，mysql数据库，数据量500G，每日更新量20M-30M
备份策略：
xtrabackup，每周日23:00进行全备，周一到周六23:00进行增量备份。
故障场景：
周三下午2点出现数据库意外删除表操作。
如何恢复？

4.1 清除以往备份

[root@db01 /backup/xbk]# \rm -rf /backup/xbk/*

4.2 建库建表

mysql [(none)]>create database xbk charset utf8mb4;
mysql [(none)]>use xbk
mysql [xbk]>create table t1(id int) engine=innodb charset=utf8mb4;

4.3 插入数据

mysql [xbk]>insert into t1 values(1),(2),(3);
mysql [xbk]>commit;

4.4 模拟周日全备

[root@db01 /backup/xbk]# innobackupex --user=root --password=123456 --no-timestamp /backup/xbk/full
[root@db01 /backup/xbk]# ll
total 0
drwxr-x--- 14 root root 336 Aug 16 13:35 full

4.5 模拟周一晚上数据变化

insert into t1 values(11),(22),(33);

4.6 模拟周一晚上增量备份


[root@db01 ~]# innobackupex --user=root --password=123456 --no-timestamp --incremental --incremental-basedir=/backup/xbk/full /backup/xbk/inc1 

--incremental        # 打开增量备份开关，(每一次增量都是基于上一次备份)
--incremental-basedir=/backup/xbk/full   #设定增量备份的基备份(一般是上一天)

查看备份集的关系
工作中，每天检查备份，LSN号是一个标志点


4.7 模拟周二白天的数据变化

mysql [xbk]>use xbk;
mysql [xbk]>insert into t1 values(111),(222),(333);
mysql [xbk]>commit;

4.8 模拟周二晚上增量

[root@db01 ~]# innobackupex --user=root --password=123456 --no-timestamp --incremental --incremental-basedir=/backup/xbk/inc1 /backup/xbk/inc2

4.9 模拟周三白天数据库变化

mysql [xbk]>use xbk;
mysql [xbk]>insert into t1 values(1111),(2222),(3333);
mysql [xbk]>commit;

4.10 搞破坏

[root@db01 /backup/xbk]# pkill mysqld 
[root@db01 /backup/xbk]# \rm -rf /data/3306/data/*

4.11 恢复思路

(1) 测试库,维护页
(2) 处理备份
    合并
    准备 
(3) 截取二进制日志
(4) 数据恢复

4.12 开始恢复演练

1. 处理备份: 
(1) 处理原始全备
[root@db01 ~]# innobackupex --apply-log --redo-only /backup/xbk/full/

(2) 合并周1并处理
[root@db01 ~]# innobackupex --apply-log --redo-only --incremental-dir=/backup/xbk/inc1 /backup/xbk/full

(3) 合并周2并处理
[root@db01 ~]# innobackupex --apply-log  --incremental-dir=/backup/xbk/inc2 /backup/xbk/full

(4)处理合并后的全备数据
[root@db01 ~]# innobackupex --apply-log  /backup/xbk/full/


2. 恢复备份启动数据库
[root@db01 /backup/xbk/full]# cp -a /backup/xbk/full/* /data/3306/data/ 
[root@db01 /backup/xbk/full]# chown -R mysql.  /data/3306/data/ 
[root@db01 /backup/xbk/full]# /etc/init.d/mysqld start


3 截取binlog并恢复
[root@db01 /backup/xbk/inc2]# cat /backup/xbk/inc2/xtrabackup_binlog_info 
mysql-bin.000010    2490    ae5de4c2-bf1e-11e9-8109-000c29c6fa61:1-21,
e21f04e4-bfd0-11e9-b8ae-000c29c6fa61:1-10

# 进入数据库查看
oldguo[(none)]>show binlog events in 'mysql-bin.000010';
...
| mysql-bin.000010 | 2720 | Xid            |         6 |        2751 | COMMIT /* xid=188 */ 

#截取binlog并恢复
[root@db01 ~]# mysqlbinlog --skip-gtids --start-position=2490 --stop-position=2720 /data/binlog/mysql-bin.000010 >/backup/bin.sql

set sql_log_bin=0;
source /backup/bin.sql
set sql_log_bin=1;

oldguo[xbk]>select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   11 |
|   22 |
|   33 |
|  111 |
|  222 |
|  333 |
| 1111 |
| 2222 |
| 3333 |
+------+

5. 恢复数据的效率（小扩展）

整库数据量较大 ,但是损坏的数据很少
例如 500G数据总量, 损坏的数据是10M

  1. XBK ：表空间迁移
  1. MDP ：手工分析
1、获得表结构
# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `city`/!d;q'  /tmp/full.sql>createtable.sql
2、获得INSERT INTO 语句，用于数据的恢复
# grep -i 'INSERT INTO `city`'  /tmp/full.sqll >data.sql

3.获取单库的备份
# sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql >world.sql

6. 小扩展: 闪回表数据(binlog闪回)

数据损坏:

  • 物理：磁盘，raid，FS，ibd
  • 逻辑：drop alter delete updatae
    说明: 根据 binlog row的记录,自动转化日志为逆操作,实现闪回的功能.
    mariadb 默认支持
    binlogsql
    Github上的 binlogsql 开源工具

7. 小扩展：迁移

Oracle ,SQL Server --->  MySQL
Oracle ----OGG ---> MySQL
上云迁移,DTS

停机时间:
15分钟

基于Oracle GoldenGate （OGG）进行MySQL->MySQL数据库同步配置

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