2019-06-26 Day10~MySQL-备份恢复迁移
2019-06-26 本文已影响0人
Ffvc
今日预告：
1、企业的备份恢复案例（mysqldump+binlog）
《年终故障恢复演练》
案例背景：
某种小型互联网公司，MySQL-5.7.26，CentOS-7.6，数据量级80G，每日数据增量5-6M
备份策略：
每天myqldump全备+binlog备份，每天23：00进行
故障描述：
周三下午2点，由于某原因导致数据损坏
处理思路：
1. 挂出维护页
2. 评估数据损坏状态
2.1 全部丢失 ---> 推荐直接生产恢复
2.2 部分丢失 ---> （1）从备份中导出单表数据
（2）测试库进行全备恢复
3. 恢复全备，将数据追溯到周二晚上23：00状态
4. 截取并恢复从备份时起，到下午两点误删除之前的binlog
5. 校验数据一致性
6. 撤回维护页，恢复生产。
处理结果：
1. 经过30-40分钟处理，业务恢复
2. 评估此次故障处理的合理性和实用性
案例模拟：
1. 进行周二全备
[root@db01 ~]# mysqldump -uroot -p123 -A -R --triggers -E --master-data=2 --single-transaction >/data/backup/full.sql
[root@db01 /data/backup]# vim full.sql
SET @@GLOBAL.GTID_PURGED='1357a112-8d94-11e9-8bda-000c29c21dbb:1-12';
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=194;
--
2. 模拟全备之后下午两点前的业务操作
mysql> create database mdp charset utf8mb4;
mysql> use mdp
mysql> create table t1(id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;
mysql> insert into t1 values(11),(12),(13);
mysql> commit;
mysql> update t1 set id=20 where id>10;
mysql> commit;
3. 模拟损坏数据库
rm-rf /data/mysql/data/*
pkill mysqld
rm -rf /data/mysql/data/*
4. 初始化数据
[root@db01 /data/mysql/data]# mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data
[root@db01 /data/mysql/data]# /etc/init.d/mysqld start
5. 进行全备恢复
set sql_log_bin=0; #关闭日志
source /data/backup/full.sql #恢复
flush privileges; #刷新
6. 找日志起点和终点
image.png
#截取日志
mysqlbinlog --skip-gtids --include-gtids='1357a112-8d94-11e9-8bda-000c29c21dbb:13-17' /data/binlog/mysql-bin.000007 >/data/backup/bin.sql
7. 恢复日志
set sql_log_bin=0;
source /data/backup/bin.sql;
show databases;
扩展：从全备中导出单表备份
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
image.png
作业：
模仿以上备份命令，书写备份脚本。
2、XBK的应用
2.1 安装
2.1.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
2.1.2 下载软件并安装
#上传percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
[root@db01 ~]# yum install -y percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
[root@db01 ~]# innobackupex --version
2.2 备份命令介绍
xtrabackup
innobackupex *****
2.3 备份方式——物理备份
（1）对于非Innodb表（比如 myisam）是，锁表cp数据文件，属于一种温备份。
（2）对于Innodb的表（支持事务的），不锁表，拷贝数据页，最终以数据文件的方式保存下来，把一部分redo和undo一并备走，属于热备方式。
面试题： xbk 在innodb表备份恢复的流程
- xbk备份执行的瞬间,立即触发ckpt,已提交的数据脏页,从内存刷写到磁盘,并记录此时的LSN号
- 备份时，拷贝磁盘数据页，并且记录备份过程中产生的redo和undo一起拷贝走,也就是checkpoint LSN之后的日志
- 在恢复之前，模拟Innodb“自动故障恢复”的过程，将redo（前滚）与undo（回滚）进行应用
- 恢复过程是cp 备份到原来数据目录下
精简说明
备份过程：
1. ckpt ，记录ckpt后LSN ,From lsn
2. 拷贝数据页 ，保存为数据文件
3. 自动将备份过程redo，会一并备份走，提取最后的LSN ,to LSN
恢复：
其实就是模拟了CSR过程
对比LAST LSN ,From lsn
使用redo进行前滚，对未提交的事务进行回滚
最后得到一个一致性备份
2.4 innobackupex使用
2.4.1 全备
[root@mysql ~]# mkdir -p /data/backup/bak
方法一：
[root@db01 ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123 /data/bak
方法二：
必须在配置文件加上client
vim /etc/my.cnf
[mysqld]
[client]
socket=/tmp/mysql.sock
[root@db01 backup]# innobackupex --user=root --password=123 /data/bak
模拟删除数据：
[root@db01 /data/bak]# pkill mysqld
[root@db01 /data/bak]# \rm -rf /data/mysql/data/*
恢复前准备：
[root@mysql /data/bak]# innobackupex --apply-log /data/bak/2019-06-26_12-16-35/
恢复：
[root@mysql /data/bak/2019-06-26_12-13-17]# cp -a * /data/mysql/data/
[root@mysql /data/mysql/data]# chown -R mysql. /data/
恢复测试：
[root@mysql /data/mysql/data]# /etc/init.d/mysqld start
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bao |
| bbs |
| binlog |
| gg |
| mdb |
| mysql |
| oldboy |
| oldboy1 |
| oldguo |
| performance_schema |
| school |
| sys |
| wordpress |
| world |
+--------------------+
15 rows in set (0.00 sec)
2.4.2 自主定制备份路径名
[root@db01 backup]#
innobackupex --user=root --password=123 --no-timestamp /data/bak/full_$(date +%F)
备份集中多出来的文件：
-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 就是，备份过程中产生的数据变化。
2.4.3 XBK 增量备份
备份方式：
基于上次的备份的增量
增量备份不能单独恢复，必须合并到全备中，一起恢复。
1. 周日全备
[root@db01 /data/bak]#
innobackupex --user=root --password=123 --no-timestamp /data/bak/full_$(date +%F)
2. 模拟周一数据变化
mysql> create database xbk charset utf8mb4;
mysql> use xbk
mysql> create table t1(id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;
3. 周一晚上增量备份
[root@db01 /data/bak]#
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/data/bak/full_2019-06-26 /data/bak/inc_$(date +%F)
4. 模拟周二白天数据变化
mysql> use xbk;
mysql> create table t2(id int);
mysql> insert into t2 values(1),(2),(3);
mysql> commit;
5. 周二晚上增量备份
[root@db01 /data/bak]#
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/data/bak/inc_2019-06-26 /data/bak/inc2_$(date +%F)
[root@db01 /data/bak]# cat full_2019-06-26/xtrabackup_checkpoints inc_2019-06-26/xtrabackup_checkpoints inc2_2019-06-26/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 104997435
last_lsn = 104997444
compact = 0
recover_binlog_info = 0
============================
backup_type = incremental
from_lsn = 104997435
to_lsn = 105004202
last_lsn = 105004211
compact = 0
recover_binlog_info = 0
============================
backup_type = incremental
from_lsn = 105004202
to_lsn = 105010038
last_lsn = 105010047
compact = 0
recover_binlog_info = 0
2.4.5 XBK增量恢复演示
思路：
合并所有增量到全备
每个XBK备份都需要恢复准备（prepare）
--apply-log --redo-only
1. 整理全备
[root@db01 /data/bak]#
innobackupex --apply-log --redo-only /data/bak/full_2019-06-26/
2. 整理并合并周一增量到全备
[root@db01 /data/bak]#
innobackupex --apply-log --redo-only --incremental-dir=/data/bak/inc_2019-06-26 /data/bak/full_2019-06-26/
[root@db01 /data/bak]# cat full_2019-06-26/xtrabackup_checkpoints inc_2019-06-26/xtrabackup_checkpoints inc2_2019-06-26/xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 105004202
last_lsn = 105004211
compact = 0
recover_binlog_info = 0
============================
backup_type = incremental
from_lsn = 104997435
to_lsn = 105004202
last_lsn = 105004211
compact = 0
recover_binlog_info = 0
============================
backup_type = incremental
from_lsn = 105004202
to_lsn = 105010038
last_lsn = 105010047
compact = 0
recover_binlog_info = 0
3. 整理并合并周二的增量到全备
[root@db01 /data/bak]#
innobackupex --apply-log --incremental-dir=/data/bak/inc2_2019-06-26 /data/bak/full_2019-06-26/
[root@db01 /data/bak]# cat full_2019-06-26/xtrabackup_checkpoints inc_2019-06-26/xtrabackup_checkpoints inc2_2019-06-26/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 105010038
last_lsn = 105010047
compact = 0
recover_binlog_info = 0
backup_type = incremental
from_lsn = 104997435
to_lsn = 105004202
last_lsn = 105004211
compact = 0
recover_binlog_info = 0
backup_type = incremental
from_lsn = 105004202
to_lsn = 105010038
last_lsn = 105010047
compact = 0
recover_binlog_info = 0
4. 再次整理全备
[root@db01 /data/bak]#
innobackupex --apply-log /data/bak/full_2019-06-26/
[root@db01 /data/bak]# cat full_2019-06-26/xtrabackup_checkpoints inc_2019-06-26/xtrabackup_checkpoints inc2_2019-06-26/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 105010038
last_lsn = 105010047
compact = 0
recover_binlog_info = 0
backup_type = incremental
from_lsn = 104997435
to_lsn = 105004202
last_lsn = 105004211
compact = 0
recover_binlog_info = 0
backup_type = incremental
from_lsn = 105004202
to_lsn = 105010038
last_lsn = 105010047
compact = 0
recover_binlog_info = 0
5. 破坏数据库，并恢复数据
[root@db01 /data/bak]# pkill mysqld
[root@db01 /data/bak]# \rm -rf /data/mysql/data/*
恢复数据
[root@db01 /data/bak]# innobackupex --copy-back /data/bak/full_2019-06-26
[root@db01 /data/mysql/data]# chown -R mysql.mysql /data/*
[root@db01 /data/mysql/data]# /etc/init.d/mysqld start
3、企业备份恢复案例（ XBK full + inc + binlog ）
案例背景:
某中型互联网公司。
MySQL 5.7.26 , Centos 7.6 ,数据量级600G,每日数据增量15-50M。
备份策略:
周日XBK全备+周一到周六inc增量+binlog备份,每天23:00进行。
故障描述:
周三下午2点,数据由于某原因数据损坏。
处理思路：
- 挂出维护页
- 评估一下数据损坏状态
2.1 全部丢失->推荐直接生产恢复
2.2 部分丢失- 整理合并所有备份：full + inc1 + inc2
- 截取 周二晚上到周三下午故障点的binlog日志
- 恢复全备，恢复binlog
- 检查数据完整性
- 恢复业务
处理结果：
- 经过70-80分钟处理，业务恢复
- 评估此次故障
案例模拟：
1. 模拟周日全备
[root@db01 ~]#
innobackupex --user=root --password=123 --no-timestamp /data/bak/full
2. 模拟周一的数据变化
mysql> create database hisoss charset utf8mb4;
mysql> use hisoss;
mysql> create table his_order(id int);
mysql> insert into his_order values(1),(2),(3);
mysql> commit;
3. 周一增量备份
[root@db01 /data/bak]#
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/data/bak/full /data/bak/inc1
4. 模拟周二的数据变化
mysql> use hisoss;
mysql> insert into his_order values(11),(22),(33);
mysql> commit;
5. 周二的增量备份
[root@db01 ~]#
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/data/bak/inc1 /data/bak/inc2
6. 模拟周三的数据变化
mysql> use hisoss;
mysql> insert into his_order values(111),(222),(333);
mysql> commit;
7. 有一个傻子，把数据库data目录给rm掉了
pkill mysqld
\rm -rf /data/mysql/data/*
8. 整理、合并备份
8.1 整理全备
[root@db01 /data/mysql/data]#
innobackupex --apply-log --redo-only /data/bak/full
8.2 inc1 合并并整理到full中
[root@db01 ~]#
innobackupex --apply-log --redo-only --incremental-dir=/data/bak/inc1 /data/bak/full
8.3 inc2 合并并整理到full中
[root@db01 ~]#
innobackupex --apply-log --incremental-dir=/data/bak/inc2 /data/bak/full
8.4 整体的整理
innobackupex --apply-log /data/bak/full
9. 恢复数据
[root@db01 /data/mysql/data]#
cp -a /data/bak/full/* /data/mysql/data/
10.
image.png
扩展：
假如，只是少量数据被损坏，以上方法有哪些不妥的地方？
image.png
4. MySQL数据迁移
4.0 迁移前要考虑的问题
技术方面
选择什么工具、方法
非技术
停机时间
回退方案
4.1 换主机
4.1.1 数据量小
思路：
- MDP，XBK备份出来，scp到目标主机
- 追加所有备份后的日志
3.申请停机 - 剩余部分的binlog继续恢复（搭建主从的方式来替代）
- 校验数据
- 进行业务割接
4.1.2 数据量大
XBK备份出来，scp到目标主机
搭建主从的方式
申请停机15分钟
校验数据
进行业务割接
4.2 换版本升级
例如：
5.6 ---> 5.7
（1）方法一：
建议使用mysqldump逻辑备份方式，按业务库进行分别备份，排除掉information_schema,performance_schema,sys
（2）方法二：
进行过滤复制，排除掉
information_schema,performance_schema,
4.3 异构平台——系统不一样
只能用逻辑备份
4.4 异构平台——数据库产品不同
Oracle --- OGG ---> MySQL
MySQL --- CSV ---> MongoDB
MySQL --- JSON ---> MongoDB
