美文网首页
2019-06-26 Day10~MySQL-备份恢复迁移

2019-06-26 Day10~MySQL-备份恢复迁移

作者: Ffvc | 来源:发表于2019-06-26 18:55 被阅读0次

    今日预告：

    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表备份恢复的流程

    1. xbk备份执行的瞬间,立即触发ckpt,已提交的数据脏页,从内存刷写到磁盘,并记录此时的LSN号
    2. 备份时，拷贝磁盘数据页，并且记录备份过程中产生的redo和undo一起拷贝走,也就是checkpoint LSN之后的日志
    3. 在恢复之前，模拟Innodb“自动故障恢复”的过程，将redo（前滚）与undo（回滚）进行应用
    4. 恢复过程是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点,数据由于某原因数据损坏。

    处理思路：

    1. 挂出维护页
    2. 评估一下数据损坏状态
      2.1 全部丢失->推荐直接生产恢复
      2.2 部分丢失
    3. 整理合并所有备份：full + inc1 + inc2
    4. 截取 周二晚上到周三下午故障点的binlog日志
    5. 恢复全备，恢复binlog
    6. 检查数据完整性
    7. 恢复业务

    处理结果：

    1. 经过70-80分钟处理，业务恢复
    2. 评估此次故障

    案例模拟：

    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 数据量小

    思路：

    1. MDP，XBK备份出来，scp到目标主机
    2. 追加所有备份后的日志
      3.申请停机
    3. 剩余部分的binlog继续恢复（搭建主从的方式来替代）
    4. 校验数据
    5. 进行业务割接
    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

    相关文章

      网友评论

          本文标题：2019-06-26 Day10~MySQL-备份恢复迁移

          本文链接：https://www.haomeiwen.com/subject/ofkqcctx.html

          延伸阅读

          深度阅读

          • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|2019-06-26 Day10~MySQL-备份恢复迁移|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！