第二十周-day88-数据库day10-备份恢复02
2019-08-16 本文已影响0人
Linux丶晨星
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)
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)-物理备份工具
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
- XBK ：表空间迁移
- 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分钟
