MySQL---DBA---柒(备份恢复)

一. DBA(运维)在备份恢复需要做哪些工作

1.1 设计备份策略

  1. 备份周期(天,周,月)
  2. 备份方式(全备,增量,差异)
  3. 备份对象(数据,二进制日志)
  4. 备份类型(冷备,温备,热备)
  5. 备份工具(mysqldump(MDP),Xtrabackup(XBK),replication,mysqlbinlog)

1.2 检查备份

  1. 备份内容大小查看(是否符合实际大小)
  2. 备份具体内容查看(是否有错误情况)

1.3 定期恢复演练

  1. 正常恢复到测试库
  2. 模拟故障演练

1.4 快速完整的备份恢复

1.5 升级,迁移的工作

二. 备份类型(笔试)

2.1 冷备

指在关闭数据库业务的情况下,数据库在没有任何变更的情况下,进行数据备份。业务停止进行的备份数据一致性最好,但不适于生成

2.2 温备

指锁表备份,只能进行数据库数据查询,不能进行修改操作(myisam)。影响写入操作,阻塞所有变更操作

2.3 热备

指几乎不锁表备份,在数据库业务正常运行的情况下进行数据备份,并且能够一致性恢复(但只能是事务型引擎:innodb),对业务影响最小

三. 备份工具

3.1 逻辑备份工具(基于SQL语句进行备份)

  • 比如:mysqldump与mysqlbinlog

3.2 物理备份工具(基于磁盘数据文件备份)

  • 比如:Xtrabackup(XBK), percona(第三方工具), MySQL Enterprise Backup(MEB)

3.3 逻辑备份工具mysqldump(MDP)与物理备份工具Xtrabackup(XBK)比较

1. mysqldump(MDP)

1. mysqldump介绍(优势):
---mysqldump简称MDP,属于逻辑备份工具,并且是MySQL数据库自带工具,不需要下载安装,备份出来的
都是SQL语句,文本格式,可读性高,便于备份处理,压缩比较高,节省备份的磁盘空间。

2. 备份逻辑(劣势):
先从ibd磁盘将数据提取出来到内存临时表,然后进行转换,变为SQL语句,最后再存入到某个文件中(恢复
备份是反向转换),过程比较复杂,IO消耗高,相对来说速度慢,并且不支持增量备份。

2. Xtrabackup(XBK)percona公司

1. Xtrabackup介绍(劣势):
Xtrabackup简称XBK或PBK,属于物理备份工具,备份的是数据文件(可以理解为备份的是整个数据库数据
目录下的文件,cp了一份),可读性教差,不便于处理,压缩比较低,需要更多地磁盘空间,并且还需要安
装此备份工具。

2. 备份逻辑(优势):
类似直接cp文件,直接备份,不需要管理逻辑结构,速度快,性能高,支持热备,并且自带增量备份功能。

3. 选择备份工具建议

  1. 数据量小于100G MDP,XBK
  2. 数据量在100G~1T XBK
  3. 数据量超过TB级别 XBK + MDP

四. 备份策略

4.1 备份方式

  1. 全量备份:全库备份,备份数据库所有数据
  2. 增量备份:基于全量备份备份变化的数据
  3. 差异备份:基于前一次的备份进行对变化数据的备份
  4. 逻辑备份:mysqldump+mysqlbinlog
  5. 物理备份:Xtrabackup_full + Xtrabackup_incr + mysqlbinlog

4.2 备份周期

  • 根据数据量进行设计,比如周日全备,周1~周6增量

五. mysqldump逻辑备份工具介绍与应用

5.1 mysqldump介绍

  • mysqldump备份工具属于客户端备份工具,也就意味着他可以进行本地备份或远程备份

5.2 mysqldump客户端通用参数

1. -u:指定数据库用户
2. -p:指定数据库密码
3. -h:指定远程连接地址
4. -P:指定端口
5. -S:指定sock文件
(1)本地备份:
mysqldump -uroot -p123 -S /tmp/mysql.sock-----
(2)远程备份:
mysqldump -uroot -p123 -h 10.0.0.51 -P3306-----

5.3 mysqldump备份专用基本参数

1. -A:全备参数

- mysqldump -uroot -p123  -A >/data/backup/full.sql(模板,不全面,此时会有警告)

2. -B:备份单个库或多库

- mysqldump -B mysql gtid  --set-gtid-purged=OFF >/data/backup/b.sql
- 生产中需要备份与生产相关的gtid库与mysql库(取消备份时的一些警告),并且加-B参数代表是库级别
备份,备份内容中有建库语句,若是不加-B参数直接指定库进行备份则属于表级别备份,备份内容不含建库
语句

3. 备份单个或相同库下的多个表

- mysqldump -uroot -p123 word(库名)  city(表名)  country(表名)>/backup/tab.sql
- 上述备份,事先world库必须存在,并且use到world库,才能source恢复

4. -R , -E , --triggers

1. -R:备份时备份存储过程以及函数
2. -E:备份事件
3. --triggers:备份触发器
---这三个参数是数据库特殊对象备份参数(备份时都加上即可)

5. -F:在备份开始时,刷新一个binlog文件,把备份内容存入新的binlog文件

mysqldump -uroot -p123 -A -R --triggers  -E  -F >/backup/full.sql

6. --master-data=2

1. mysqldump -uroot -p123 -A -R -E --triggers --master-data=2 >/data/a.sql  #进行全备
2. grep 'CHANGE'   /data/a.sql(过滤备份内容)
---显示如下:
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=194;
(即以注释的形式保存备份开始的时间点的binlog文件及position号,是进行binlog截取的开头号)

功能如下:
1. 在备份时,会自动记录二进制文件binlog与位置号position
2. --master-data=0(默认值)
3. 值为1:以change master to命令形式,做主从复制
4. 值为2:以注释形式记录备份时刻的binlog文件及position号
5. 自动锁表功能(自动解锁)
6. 若配合--single-transaction参数,则只对InnoDB引擎表进行不锁表备份(类似热备)
实际上是实现快照备份。

7. --single-transaction

1. 不加此参数,会启动所有表的温备,所有表都锁定,而我们需要‘热备’
2. 加上此参数,只对InnoDB引擎表实现快照备份,对非InnoDB表实现自动锁表

8. --set-gtid-purged=auto/on(off)

1. 功能是为了取消备份后生成的警告信息
2. --set-gtid-purged=off,使用在日常备份的情况下
3. --set-gtid-purged=auto/on,在构建主从复制环境时需要,默认是此,不写也可以

9. --max-allowed-packet=256M

1. 此参数在客户端与服务端都有,是在备份时提取数据时默认最大可处理查询范围,可进行调整,
建议最大不超256M。

10. 由上述得一个全库备份完整命令

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

5.4 实现所有表的单独备份(CONCAT拼接语句)

select CONCAT("mysqldump -uroot -p123  ",table_schema,"  ",table_name,"  --master-data=2   --single-transaction   --set-gtid-purged=2  -R -E --triggers  >/backup/",table_schema,"_",table_name,".sql")  from information_schema.table  where  table_schema not in ('sys','information_schema','performance_schema');

5.5 企业故障案列(mysqldump恢复)

1. 背景环境
---正在运行的网站环境,mysql-5.7.20数据库,数据量50G

2. 备份策略
---每天23点,计划任务调用mysqldump执行全备脚本

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

4. 恢复思路
---停止运行故障业务,挂载维护页,避免数据受到二次伤害
---准备临时库,恢复周二23点全备
---截取全备(周二23点)到故障时刻(周三上午10点)的binlog
---恢复截取日志
---测试可用性和完整性
---直接用临时库代替原生产库,前端应用割接到新库或将误删除表导出,导入原库
---撤维护页,开启业务

5. 注意
---mysqldump备份的恢复会删除重复的表
---mysqldump在备份和恢复时都需要mysql实例能够起到为前提

6. 实际案列演练
(1)准备数据
create database mdp charset  utf8mb4;
use mdp;
create table t1(id int)  engine=innodb  charset=utf8mb4;
insert into t1 values(1),(2),(3);
commit;

(2)模拟周二23点全备
 mysqldump -uroot -p123 -A -E -R --triggers  --master-data=2 --single-transaction  --set-gtid-purged=OFF  --max-allowed-packet=256M|gzip >/backup/full_$(date +%F).sql.gz

(3)模拟周二23点到周三10点之前的数据变化
use mdp;
insert into t1 values(11),(22),(33);
commit;

(4)模拟故障
drop database mdp;

(5)停止故障业务,挂载维护页
(6)准备临时数据库
(7)准备全备内容
gunzip /backup/full_2019-08-16.sql.gz

(8)查找二进制日志开头position号
vim  /backup/full_2019-08-16.sql(地二十二行)

(9)查找二进制日志删除之前的position号
show master status;
show binlog events  in  '上述命令显示正在使用的二进制日志';

(10)截取binlog日志
mysqldump --skip-gtids   --start-position=817   --stop-position=1143   /data/binlog/mysql-bin.000008   >/backup/binlog.sql

(11)恢复备份到数据库
set sql_log_bin=0;
source /backup/full_2019-08-16.sql;
source /backup/binlog.sql;
set sql_log_bin=1;

(12)若为临时库向原生产库导入数据该如何操作
1. mysqldump -S  /data/3307/mysql.sock   mdp  t1  >/backup/t1.sql
(将临时库的故障表导出到某个文件)
2. 再推送到原生产库
3. 在原生产库进行source激活即可

六. Xtrabackup(XBK)物理备份工具应用

6.1 Xtrabackup的安装(percona.com)

1. 从官网进行软件包安装
- https://www.percona.com/downloads/Percona-XtraBackup-LATEST/
(Xtrabackup官网下载界面)
- https://www.percona.com/doc/percona-xtrabackup/8.0/how_xtrabackup_works.html
- https://www.percona.com/doc/percona-xtrabackup/2.4/how_xtrabackup_works.html


2. 安装Xtrabackup依赖
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

3. 上传Xtrabackup软件包到机器进行yum安装
---上传软件包
yum -y install percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
---安装完成即可

6.2 Xtrabackup备份方式(物理备份)

  1. 对于非InnoDB表(比如myisam),它进行的是锁表cp数据文件,属于一种温备方式
  2. 对于InnoDB表(支持事务的引擎),它进行的是不锁表备份,拷贝数据文件,最终以数据文件的方式保存下来,把一部分的redo与undo一并备走,属于热备方式

6.3 面试题:Xtrabackup在InnoDB表备份恢复流程

  1. XBK备份执行的瞬间,立即触发CKPT动作,将已经提交的数据脏页,从内存刷写道磁盘,并记录此时的LSN号
  2. 备份时,拷贝磁盘数据页,并记录备份过程中产生的redo与undo一并备走,也就是checkpoint LSN之后的日志
  3. 在恢复之前,模拟InnoDB“自动故障恢复”过程,将redo前滚,undo回滚进行应用
  4. 恢复过程是cp,直接指定备份到原来的数据目录下

6.4 Xtrabackup物理备份工具的使用(innobackupex)

1. 客户端应用innobackupex需要进行配置(/etc/my.cnf)
[client]
socket=/tmp/mysql.sock
---增加一个client客户端标签并指定socket文件

2. 全备与自主指定备份路径名(--no-timestamp)
全备:
innobackupex --user=root --password=456  /backup/xbk
自主指定路径:
innobackupex --user=root --password=456  --no-timestamp  /backup/xbk/full

3. 备份集多出来的文件(备份集:/backup/xbk/full)
(1)xtrabackup_binlog_info
---记录的是备份时刻binlog的位置,binlog的文件名字和当时结束的position号,可以用来作为截取binlog起点

(2)xtrabackup_checkpoints
backup_type = full-backuped(full-prepared)
---备份类型:全备
from_lsn = 0
---指上次到达的LSN号(对于全备从0开始,增量有其他显示)
to_lsn = 286135788
---备份开始时间点(CKPT点)数据页的LSN
last_lsn = 286135794
---指备份结束后,redo日志最终的LSN(若是与to_lsn号相差9个数,则表示备份期间没有产生任何新数据)
compact = 0
recover_binlog_info = 0

4. 备份过程分析
(1)备份时刻,立即将已经commit过的内存中的数据页刷写道磁盘中(CKPT动作),开始备份数据,数据
文件的LSN会挺溜在to_lsn位置

(2)备份时刻有可能有其他的数据写入,已经备走的数据文件就不会在发生变化了

(3)在备份过程中,备份软件会一直监控着redo与undo,如果一旦有变化则会将日志一并备走,并记录LSN到last_lsn。
从to_lsn到last_lsn就是备份过程中产生的数据变化

5. Xtrabackup全备恢复
(1)准备备份:Prepared
innobackupex --apply-log /data/backup/full
--apply-log:模拟录入InnoDB引擎的ACSR过程,将备份集中的数据和redo日志的LSN追平(LSN号相同
才能启动数据库)将redo进行重做,已经提交的写到数据文件,未提交的使用undo回滚掉。

(2)恢复备份
前提:被恢复的数据目录为空:\rm -rf  /data/3306/data/*
      被恢复的数据库实例关闭:pkill mysqld或systemctl stop mysqld
恢复:
       1. innobackupex  --copy-back  /backup/xbk/full
--copy-back:此参数依赖配置文件(/etc/my.cnf)中的[mysqld]
       2. cp -a /backup/xbk/full/*   /data/3306/data/
强制cp到原来的数据目录下

(3)修改权限并登陆
chown -R mysql.mysql  /data/*
/etc/init.d/mysqld  start
mysql -uroot -p123

6.5 Xtrabackup物理备份工具应用innobackupex进行增量备份(incremental)案列

1. 增量备份逻辑
---增量备份的方式,是基于上一次备份进行增量
---增量别分无法单独恢复,必须基于全备进行恢复
---所有增量必须按顺序合并到全备中

2. 备份方式
---全备+增量备份

3. 故障案列
---背景: 某大型网站,MySQL 5.7.20 ,数据量 800G
---备份策略: Xtrabackup, FULL(周日23:30)+INCN(周一到周六23:30)+binlog(每天中午12:30)
---故障: 周三上午10点,数据库"瘫了"

4. 模拟初始数据
    create database xbk charset utf8mb4;
    use xbk;
    create table t1 (id int)engine=innodb charset=utf8mb4;
    insert into t1 values(1),(2),(3);
    commit;

5. 模拟周日的全备
    innobackupex --user=root --password=456 --no-timestamp /data/backup/full 
6. 模拟周一数据变化
    use xbk;
    insert into t1 values(11),(22),(33);
    commit;
7. 模拟周一晚上增量备份
    innobackupex --user=root --password=456 --no-timestamp --incremental --incremental-basedir=/data/backup/full /data/backup/inc1
8. 模拟周二白天的数据变化
    use xbk;
    insert into t1 values(111),(222),(332);
    commit;
9. 模拟周二晚上增量备份
    innobackupex --user=root --password=456 --no-timestamp --incremental --incremental-basedir=/data/backup/inc1 /data/backup/inc2
10. 模拟周三白天的数据变化
    use xbk;
    insert into t1 values(1111),(2222),(3322);
    commit;
11. 搞破坏
    pkill mysqld 
    rm -rf  /data/mysql/data/*
12. 备份整理,检查备份
    full+inc1+inc2+binlog
13. base_full进行处理备份,整理原始全备
    innobackupex --apply-log --redo-only  /data/backup/full
14. 合并inc1到full中,处理备份 
    innobackupex --apply-log --redo-only --incremental-dir=/data/backup/inc1  /data/backup/full
15. 合并inc2到full中,处理备份 
    innobackupex --apply-log  --incremental-dir=/data/backup/inc2 /data/backup/full
16. 最后一次整理全备 
    innobackupex --apply-log   /data/backup/full
17. 恢复上述整理后的备份
    cp -a /data/backup/full/*   /data/3306/data
    chown -R  mysql.mysql  /data/*
    /etc/init.d/mysqld  start 
18. 截取二进制日志恢复周二增量备份后到删除之前的数据
    cat  /data/backup/inc2/xtrabackup_binlog_info 
显示:mysql-bin.000010  1339(二进制文件与position号)
截取:mysqlbinlog --skip-gtids --start-position=1339 /data/binlog/mysql-bin.000010 >/data/backup/bin.sql
19. 恢复binlog 
    set sql_log_bin=0;
    source /data/backup/bin.sql
    set sql_log_bin=1;

6.6 恢复数据的效率问题

案例:整库的数据量大,但损坏的数据少,500G损坏10G

1. Xtrabackup物理备份工具:表空间迁移
2. mysqldump逻辑备份工具:手工分析(如下)
---从mysqldump全备中获取库和表的备份
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:  `库名`/,/^-- Current Database:  `/p`' all.sql>a.sql

七. 扩展:闪回表数据(binlog闪回)---binlog2sql

八. 扩展:迁移

©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 211,561评论 6 492
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 90,218评论 3 385
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 157,162评论 0 348
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 56,470评论 1 283
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 65,550评论 6 385
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 49,806评论 1 290
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 38,951评论 3 407
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 37,712评论 0 266
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 44,166评论 1 303
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,510评论 2 327
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,643评论 1 340
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 34,306评论 4 330
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 39,930评论 3 313
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,745评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,983评论 1 266
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 46,351评论 2 360
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 43,509评论 2 348

推荐阅读更多精彩内容