第七章 MySQL 备份恢复

最新推荐文章于 2024-04-29 22:54:07 发布

飞鸡起飞

最新推荐文章于 2024-04-29 22:54:07 发布

阅读量169

点赞数

本文链接：https://blog.csdn.net/weixin_45367149/article/details/103149855

版权

1. 运维在数据库备份恢复方面的职责

1.1 设计备份策略

(1) 备份什么数据
数据部分
二进制日志
配置文件
(2) 什么时间备份
低估期
(3) 备份周期
每周,每天…
(4) 备份的方式
逻辑备份: mysqldump,binlog
物理备份: xtrabackup
全备
增量
热备
冷备
(5) 备份脚本设计
略
1.2 备份的检查
(1) 日志
(2) 备份集的大小和完整性

1.3 备份的恢复演练

一季度或者半年

1.4 故障恢复

通过现有备份,能够将数据库恢复到故障之前的时间点.

1.5 迁移

同版本
1.停机时间
2.回退方案

2. 备份类型

2.1 热备 : 在线备份. 不停业务进行备份,对业务影响小. Innodb支持
2.2 温备 : 锁表备份. 备份过程只能查询,不能修改. MyISAM
2.3 冷备 : 业务停止进行备份.

3. 备份方式

逻辑备份 : SQL语句的备份.
物理备份 : 基于数据文件的备份.

4. 数据恢复(闪回)

DML语句闪回: delete 一个大表只删除几百行数据如何快速恢复

binlog2sql     ---->   row binlog     ----> 闪回数据
mariadb  ---->  mysqlbinlog --flash-back 
RDS   -----> 回收站

binlog2sql地址: https://www.jianshu.com/p/6432188bc3d9
https://www.cnblogs.com/ivictor/p/6418409.html
flashbackup:
https://www.cnblogs.com/waynechou/p/mysql_flashback_intro.html

5. 备份工具:

mysqldump : MDP
优点: 逻辑备份工具,备份的就是SQL语句(Create database create table insert)
文本形式,可读性好,便于管理和备份处理,压缩比更高.
适用于数据量小的库备份.
缺点：依赖于数据库引擎，需要从磁盘把数据读出然后转换成SQL进行转储，比较耗费资源，数据量大的话效率较低
建议：
100G以内的数据量级，可以使用mysqldump
超过TB以上，我们也可能选择的是mysqldump，配合分布式的系统
1EB =1024 PB =1000000 TB
xtrabackup : XBK
优点: 物理备份工具.备份快.原生态支持增量备份.
缺点：可读性差压缩比低，需要更多磁盘空间
建议：
>100G<1TB

6. mysqldump 应用

6.1 连接参数

#### 本地连接
mysqldump -uroot -p123 -S /tmp/mysql.sock
#### 远程连接
mysqldump -uroot -p123 -h10.0.0.51 -P3306

6.2 备份基础参数

####A 全备参数
mkdir -p /data/backup 
[root@db01 ~]# mysqldump -uroot -p123 -S /tmp/mysql.sock  -A >/data/backup/full.sql  2>/dev/null
####B 单库或多库备份
[root@db01 ~]# mysqldump -uroot -p123 -B huoche school world >/data/backup/db.sql
####单表或多表
[root@db01 ~]# mysqldump -uroot -p123  world city country >/data/backup/tab.sql
说明: 单表或多表备份,在恢复时,需要先创建库,use 库中,再source
[root@db01 ~]# mysqldump -uroot -p123  world   >/data/backup/tab1.sql

6.3 备份高级参数

6.3.1 --master-data=2

(1) 以注释方式,在备份文件中,自动记录二进制日志名+位置点
(2) 会自动进行锁表操作.(innodb除外).
mysqldump -uroot -p123 -S /tmp/mysql.sock  -A --master-data=2 >/data/backup/full.sql  2>/dev/null

6.3.2 --single-transaction

开启备份时,对InnoDB引擎的表,生成一致性快照备份.不会阻塞这些表的正常业务操作.
mysqldump -uroot -p123 -S /tmp/mysql.sock  -A --master-data=2  --single-transaction >/data/backup/full.sql  2>/dev/null

6.3.3 -R -E --triggers

-R  备份存储过程,函数  
-E  备份事件
--triggers 备份触发器
mysqldump -uroot -p123 -S /tmp/mysql.sock  -A --master-data=2  --single-transaction -R -E --triggers >/data/backup/full.sql  2>/dev/null

6.3.5 --max_allowed_packet=128M

备份大数据量表时候需要添加.
mysqldump -uroot -p123 -S /tmp/mysql.sock  -A --master-data=2  --single-transaction -R -E --triggers --max_allowed_packet=128M >/data/backup/full.sql  2>/dev/null

6.3.6 终极备份语句

mysqldump -uroot -p123 -S /tmp/mysql.sock  -A --master-data=2  --single-transaction -R -E --triggers --max_allowed_packet=128M | gzip > /backup/full_$(date +%F).sql.gz

7.年终故障演练

7.1 方案

数据量: 200G
备份策略: 每周日23:00 mysqldump全备,周一到周六binlog备份
故障模拟: 模拟周三上午10点,数据损坏(DROP DATABASE)
演练过程:
1,将生产数据提前1周导入测试库.
2,周日全备模拟
3,周一到周二binlog备份模拟
4,周三模拟故障
5,数据恢复
6,数据校验
7,模拟上线
演练结果:
1, 在发生数据故障时,需要大约1:00-1:30小时左右可以恢复所有业务.
2, 此恢复方案,数据恢复较慢,可以考虑更快恢复方案.

7.2 模拟过程:

7.2.1. 模拟原始数据

mysql> create database mdp charset=utf8mb4;
mysql> create table t1 (id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;

7.2.2. 模拟周日全备

mysqldump -uroot -p123 -S /tmp/mysql.sock  -A --master-data=2  --single-transaction -R -E --triggers --max_allowed_packet=128M | gzip > /data/backup/full_$(date +%F).sql.gz
检查备份
[root@db01 /data/backup]# gunzip full_2019-11-20.sql.gz

7.2.3. 模拟周1的数据

mysql> create table t2 (id int);
mysql> insert into t2 values(1),(2),(3);

7.2.5. 周1晚上备份binlog

[root@db01 /data/binlog]# mysqladmin -uroot -p123 flush-logs
[root@db01 /data/binlog]# cp /data/binlog/mysql-bin.000002 /data/backup

7.2.6. 模拟周2的数据

mysql> create table t3 (id int);
mysql> insert into t3 values(1),(2),(3);

7.2.7. 周2晚上备份binlog

[root@db01 /data/binlog]# mysqladmin -uroot -p123 flush-logs
[root@db01 /data/binlog]# cp /data/binlog/mysql-bin.000003 /data/backup

7.2.8. 周三数据损坏模拟
```
mysql> drop database mdp;
```

7.2.9. 恢复数据:

(1) 备份检查  
全备  
binlog
(2) binlog位置点确认 
起点: -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=748;
'79f1839c-0439-11ea-83bb-000c29d354c0:1-3' 
终点:'79f1839c-0439-11ea-83bb-000c29d354c0:8' 
分析结果:
'79f1839c-0439-11ea-83bb-000c29d354c0:4-7' 
mysql-bin.000002 mysql-bin.000003	 mysql-bin.000004  mysql-bin.000005
(3) 截取日志 
[root@db01 /data/binlog]# mysqlbinlog --include-gtids='79f1839c-0439-11ea-83bb-000c29d354c0:4-7' --skip-gtids mysql-bin.000002 mysql-bin.000003 mysql-bin.000004  mysql-bin.000005 >/data/backup/bin.sql
(5) 恢复数据
set sql_log_bin=0;
source /data/backup/full_2019-11-20.sql
source /data/backup/bin.sql
set sql_log_bin=1;

8. XBK应用

8.1 介绍

percona公司研发的MySQL物理备份的工具.使用perl语言开发的.
https://www.percona.com/downloads/Percona-XtraBackup-LATEST/

8.2 安装

#### 安装依赖
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
#### 下载rpm包
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
#### 安装
[root@db01 ~]# yum install -y percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
[root@db01 ~]# innobackupex --version

8.3 xbk备份原理

1.自动判断引擎类型
InnoDB
Non-InnoDB
2.InnoDB表实现热备功能备份
开始备份innoDB时,自动进程ckpt,将当前已经提交的事务数据,刷写到磁盘,会生成一个CKPT的LSN号.
cp 数据文件,ibdata ,ibd frm ,ibtmp ,redo(只会拷贝备份期间产生的新的redo)
3.非InnoDB 表
自动开启 FTWRL (flush tables with read lock) 全局锁
拷贝数据文件.
自动解锁
拷贝完成后会产生一个LAST_LSN.
5.增量备份
基于上一次备份的last_lSN,检查数据页的变化,然后备份走.

8.5 innobackupex 备份工具应用

vim /etc/my.cnf
[clinet]
socket=/tmp/mysql.sock
(1) 全备 
[root@db01 ~]# innobackupex --user=root --password=123 /data/backup
xtrabackup_binlog_info
xtrabackup_checkpoints
xtrabackup_info
xtrabackup_logfile
[root@db01 /data/backup/2019-11-20_12-12-21]# cat 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
说明:开始lsn和结束相差9,备份时刻有可能会有其他的数据写入
####生产备份:
innobackupex --user=root --password=123 --no-timestamp /data/backup/full_`date +%F`	 

(2) 增量备份应用
####1. 模拟周日全备 
\rm -rf /data/backup/*
innobackupex --user=root --password=123 --no-timestamp /data/backup/full_`date +%F`
####2. 模拟周一数据变化
 create database xbk charset=utf8mb4;
use xbk
create table t1(id int);
insert into t1 values(1),(2),(3);
####3. 模拟周一晚上增量
innobackupex --user=root --password=123 --no-timestamp  --incremental  --incremental-basedir=/data/backup/full_2019-11-20 /data/backup/inc1
####5 . 模拟周二数据变化 
use xbk
create table t2(id int);
insert into t2 values(1),(2),(3);
####6. 模拟周二晚上增量
innobackupex --user=root --password=123 --no-timestamp  --incremental  --incremental-basedir=/data/backup/inc1  /data/backup/inc2
####7. 模拟周三数据变化
use xbk
create table t3(id int);
insert into t3 values(1),(2),(3);
####8. 搞破坏
[root@db01 /data/mysql/data]# \rm -rf /data/mysql/data/*
[root@db01 /data/mysql/data]# pkill mysqld
[root@db01 /data/mysql/data]# \rm -rf /data/mysql/data/*
####9. 恢复 
--apply-log : 模仿了CSR的过程: redo前滚,undo回滚过程.
--redo-only : 应用在全备+增量.最后一次不需要
(1) 原始全备的处理
innobackupex --apply-log --redo-only /data/backup/full_2019-11-20
(2)合并inc1增量到full,prepare所有备份.
innobackupex --apply-log --redo-only --incremental-dir=/data/backup/inc1   /data/backup/full_2019-11-20
(3) 合并inc2增量到full,prepare所有备份.
innobackupex --apply-log --incremental-dir=/data/backup/inc2   /data/backup/full_2019-11-20
(5) 最后一次原始全备的处理
innobackupex --apply-log  /data/backup/full_2019-11-20
(6)截取二进制日志
cd   /data/backup/inc2
[root@db01 /data/backup/inc2]# cat xtrabackup_binlog_info 
mysql-bin.000005	1362	79f1839c-0439-11ea-83bb-000c29d354c0:1-13
[root@db01 /data/backup/inc2]#mysqlbinlog --start-position=1362  --skip-gtids  /data/binlog/mysql-bin.000005 >/data/backup/bin.sql 
(7) 恢复备份
cp -a /data/backup/full_2019-11-20/* /data/mysql/data/
chown -R mysql.mysql /data/mysql/data/*
/etc/init.d/mysqld start
set sql_log_bin=0;
source /data/backup/bin.sql
set sql_log_bin=1;

9. 练习:

1. 模拟周日全备 
\rm -rf /data/backup/*
innobackupex --user=root --password=123 --no-timestamp /data/backup/full_`date +%F`
2. 模拟周一数据变化
mysql> create database chp charset=utf8mb4;
mysql> use chp
mysql> create table t1(id int);
mysql> insert into t1 values(1),(2),(3);
3. 模拟周一晚上增量
innobackupex --user=root --password=123 --no-timestamp  --incremental  --incremental-basedir=/data/backup/full_2019-11-20 /data/backup/inc1
5 . 模拟周二数据变化 
use chp
create table t2(id int);
insert into t2 values(1),(2),(3);
4. 模拟周二晚上增量
innobackupex --user=root --password=123 --no-timestamp  --incremental  --incremental-basedir=/data/backup/inc1  /data/backup/inc2
5. 模拟周三数据变化
use chp
create table t3(id int);
insert into t3 values(1),(2),(3);
6. 搞破坏
drop database chp;
7. 恢复 
思路1: 
(1) 表空间迁移 (恢复周二晚上)
create table t1(id int);
alter table t1  discard tablespace;
cp ibd ...
alter table t1 import tablespace;
(2) binlog日志截取及恢复
mysqlbinlog -d xxxxxx  
思路2: 
找测试库全备恢复+binlog , 误删除数据dump出来,再导入生产.
1.合并日志
innobackupex --apply-log --redo-only /data/backup/full_2019-11-20
innobackupex --apply-log --redo-only --incremental-dir=/data/backup/inc1   /data/backup/full_2019-11-20
innobackupex --apply-log --incremental-dir=/data/backup/inc2   /data/backup/full_2019-11-20
innobackupex --apply-log  /data/backup/full_2019-11-20
2. 截binlog日志   ,注意截取全备结束到drop之前的
cd /data/backup/inc2/
mysqlbinlog --include-gtids='9b4895ef-0b72-11ea-95c2-000c29c3fec5:5-7' --skip-gtids /data/binlog/mysql-bin.000003 >/data/backup/bin.sql
3.合并日志恢复
mkdir /data/mysql/data2 -p
chown -R mysql. /data/
cp -a /data/backup/full_2019-11-20/* /data/mysql/data2/
4.配置文件重新指定数据目录
vim /etc/my.cnf
datadir=/data/mysql/data2
5.进入数据库恢复
systemctl restart mysqld.service 
mysql> set sql_log_bin=0
mysql>source /data/backup/bin.sql
mysql>set sql_log_bin=1