第七章节 备份恢复
- 运维在数据库备份恢复方面的职责
1.1 设计备份策略
(1) 备份什么数据?
数据部分
二进制日志
配置文件
(2) 什么时间备份
低估期
(3) 备份周期
每周,每天...
(4) 备份的方式
逻辑备份: mysqldump,binlog
物理备份: xtrabackup
全备
增量
热备
冷备
(5) 备份脚本设计
略
1.2 备份的检查
(1) 日志
(2) 备份集的大小和完整性
1.3 备份的恢复演练
1.5 故障恢复
1.6 迁移
同版本
异构平台
简介
优势
技能
工作经历
项目
个人评价
==========================
备份类型
2.1 热备 : 在线备份. 不停业务进行备份,对业务影响小. Innodb支持
2.2 温备 : 锁表备份. 备份过程只能查询,不能修改. MyISAM
2.3 冷备 : 业务停止备份.备份方式
逻辑备份 : SQL语句的备份.
物理备份 : 基于数据文件的备份.备份工具:
5.1
mysqldump : MDP
逻辑备份工具,备份的就是SQL语句(Create database create table insert)
文本形式,可读性好,便于管理和备份处理,压缩比更高.
适用于数据量小的库备份.
建议:
<100G
TB: 分布式备份
100PB 怎么备份?
xtrabackup : XBK , PBK
物理备份工具.备份快.原生态支持增量备份.
100 <1T
=============
- 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
备份大数据量表时候需要添加.
- Got a packet bigger than 'max_allowed_packet' bytes
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.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);
mysql> commit;
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);
mysql> commit;
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;
- 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
yum -y install percona-xtrabackup-24-2.4.4-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
innobackupex version 2.4.4 Linux (x86_64) (revision id: df58cf2)
[root@db01 ~]#
8.3 xbk备份原理
自动判断引擎类型
InnoDB
Non-InnoDBInnoDB表 实现热备功能备份
开始备份innoDB时,自动进程ckpt,将当前已经提交的事务数据,刷写到磁盘,会生成一个CKPT的LSN号.
cp 数据文件,ibdata ,ibd frm ,ibtmp ,redo(只会拷贝备份期间产生的新的redo),拷贝完成后会产生一个LAST_LSN非InnoDB 表
自动开启 FTWRL (flush tables with read lock) 全局锁
拷贝数据文件.
自动解锁.增量备份
基于上一次备份的last_lSN,检查数据页的变化,然后备份走.
8.5 innobackupex 备份工具应用
vim /etc/my.cnf
[clinet]
socket=/tmp/mysql.sock
(1) 全备
[root@db01 ~]# innobackupex --user=root --password=123 /data/backup
-rw-r----- 1 root root 62 11月 20 12:12 xtrabackup_binlog_info
-rw-r----- 1 root root 117 11月 20 12:12 xtrabackup_checkpoints
-rw-r----- 1 root root 538 11月 20 12:12 xtrabackup_info
-rw-r----- 1 root root 2560 11月 20 12:12 xtrabackup_logfile
[root@db01 /data/backup/2019-11-20_12-12-21]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 206912588 ----ckpt
last_lsn = 206912597 ----last-9
compact = 0
recover_binlog_info = 0
生产备份:
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. 模拟周一数据变化
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. 模拟周一晚上增量
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);
commit;
-- 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);
commit;
-- 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. 恢复
备份准备? prepare
模仿了CSR的过程: redo前滚,undo回滚过程.
--apply-log
--apply-log Prepare a backup in BACKUP-DIR by applying the
transaction log file named "xtrabackup_logfile" located
in the same directory. Also, create new transaction logs.
The InnoDB configuration is read from the file
"backup-my.cnf".
--redo-only This option should be used when preparing the base full
backup and when merging all incrementals except the last
one. This forces xtrabackup to skip the "rollback" phase
and do a "redo" only. This is necessary if the backup
will have incremental changes applied to it later. See
the xtrabackup documentation for details.
(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
mysqlbinlog --start-position=1362 --skip-gtids /data/binlog/mysql-bin.000005 >/data/backup/bin.sql
(7) 恢复备份
[root@db01 /data/backup/full_2019-11-20]# cp -a /data/backup/full_2019-11-20/* /data/mysql/data/
[root@db01 /data/backup/full_2019-11-20]# chown -R mysql.mysql /data/mysql/data/*
[root@db01 ~]# /etc/init.d/mysqld start
mysql> set sql_log_bin=0;
mysql> source /data/backup/bin.sql
mysql> set sql_log_bin=1;
练习:
-- 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);
mysql> commit;
-- 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);
commit;
-- 6. 模拟周二晚上增量
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/data/backup/inc1 /data/backup/inc2
-- 7. 模拟周三数据变化
use chp
create table t3(id int);
insert into t3 values(1),(2),(3);
commit;
-- 8. 搞破坏
drop database chp;
-- 9. 恢复
思路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. 物理损坏
2. 逻辑损坏
drop
delete
truncate
思考作业:
假设生产mysqldump全备200G,误删除了一个100M表.怎么快速恢复?
================================================
- 数据库迁移
停机时间 : 停止业务的时间
回退方案 : 业务快速恢复原状
9.1 同版本,同平台
小 : mysqldump
(1) 备份原库(1:00-2:30)
(2) 备份拷贝到目标库,恢复2:30-3:30
(3) 不断追加binlog到目标库,几乎接近
(5) 停掉原库,4:00,继续追加binlog到目标库,直到追平(5分钟)
(6) 业务的IP地址切换为目标库地址(5分钟),测试应用(业务割接)
大 : XBK
主从复制: 业务停机时间最短
同版本,异构平台
windows -----> linux
mysqldump 逻辑工具迁移
同平台,不同版本
迁移+升级 ,使用mysqldump备份工具进行备份恢复+升级(mysql_upgrade),不要跨太多版本迁移升级
异构数据库
mysql ----CSV---> mongodb
mysql ----json---> mongodb
mysql ---ali cannel--> redis
数据库上云 docker + k8s
体验很重要.
