1. 运维人员在备份恢复方面的职责
1.设计备份策略
- 备份内容: 数据,二进制日志
- 数据容量: 大小
- 备份周期:
1.每天全备
2.周期全备+增量备份
3.周期全备+日志备份 - 备份时间: 晚上备份
- 备份目标位置
2.日常备份检查
日志 , 备份的内容,备份大小
3.定期恢复演练
我们建议,每半年做一次.
4.故障时的恢复
快速准确恢复数据.
5.平台数据迁移
同构平台
异构平台
2. 备份工具介绍
1.介绍
mysqldump (MDP)
Percona Xtrabackup(xbk,pbk,pxb)
2.区别
MDP : 逻辑备份,SQL文件,文本形式,可读性高,便于处理,压缩比高,备份相对较慢 ,比较适合于100G以内的备份 .
100G 30-40 Min
xbk : 物理备份,数据文件,可读性较低,压缩比低,备份相对较快,比较适合于100G以内的备份 .
100G 10min
600G 50min
100 PB ---拆分数据库---> MDP ---压缩->
3.mysqldump
1.备份逻辑
将磁盘数据,加载到临时表,转换为SQL(建库,建表,插入语句)
2.核心参数
- 连接参数
本地: -u -p -S
远程: -u -p -H -P - 基础备份参数
1. 全备参数
mysqldump -uroot -p123 -A >/data/backup/full.sql
2. 单库或多库备份
mysqldump -uroot -p123 -B bgx oldboy test world mysql > /data/backup/db.sql
3. 单表或多表备份
mysqldump -uroot -p123 world city country >/data/backup/tab.sql
4. 只导出建表语句
mysqldump -uroot -p123 world city country --no-data >/data/backup/tab1.sql
-
高级备份参数
1.master-data=2
介绍
自动记录备份时的binlog filename 及pos
自动加锁和解锁
加了--single-transaction , 会有不一样的效果( global read lock is only taken a short time )
mysqldump -uroot -p123 -A --master-data=2 >/data/backup/full.sql备份的结果:
[root@db01 ~]# cat /data/backup/full.sql
30 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1162;
2.single-transaction
- 介绍
开启独立事务,备份InnoDB表的一致性快照.实现InnoDB表的热备功能.
mysqldump -uroot -p123 -A --master-data=2 --single-transaction >/data/backup/full.sql
对于非InnoDB表(FTWRL,Flush tables with read lock),对于mysql库下的系统表备份的时候,会加global read lock
3.特殊的数据库对象
- 参数
-R 存储过程和函数
--triggers 触发器
-E 事件
mysqldump -uroot -p -A --master-data=2 --single-transaction -R --triggers -E >/data/backup/full.sql
4.max_allowed_packet=128M
- 介绍
1153 - Got a packet bigger than 'max_allowed_packet' bytes
出现场景: 在数据库有超大比表时,需要加此的参数.
5.终极备份语句
mysqldump -uroot -p -A --master-data=2 --single-transaction -R --triggers -E --max_allowed_packet=128M |gzip >/data/backup/full_$(date +%F).sql.gz
3.模拟故障和恢复(mysqldump 全备+ binlog)
- 模拟原始数据
create database mdp charset utf8mb4;
use mdp
create table t1 (id int);
create table t2 (id int);
create table t3 (id int);
insert into t1 values(1);
commit;
insert into t2 values(1);
commit;
insert into t3 values(1);
commit;
- 模拟全备
mysqldump -uroot -p -A --master-data=2 --single-transaction -R --triggers -E --max_allowed_packet=128M |gzip >/data/backup/full_$(date +%F).sql.gz
- 模拟全备
- 模拟新的数据
use mdp
insert into t1 values(2);
commit;
insert into t2 values(2);
commit;
insert into t3 values(2);
commit;
- 搞破坏
drop database mdp;
- 搞破坏
恢复思路
1.挂维护页
2.找测试库
3.准备备份(full 截取binlog部分 )
4.恢复全备+binlog到测试库 , 业务测试
5.导出故障库数据,恢复到生产
6.撤维护页.
- 恢复过程:
- 准备备份
vim /data/backup/full_2019-12-26.sql
第30行:
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=2570;
- 截取二进制日志
起点 : 2570
mysql> show binlog events in 'mysql-bin.000003';
| mysql-bin.000003 | 3388 | Query | 6 | 3477 | drop database mdp
终点 : 3388
mysqlbinlog --skip-gtids --start-position=2570 --stop-position=3388 /data/mysql/binlog_3306/mysql-bin.000003 >/data/backup/bin.sql
- 恢复备份
set sql_log_bin=0;
source /data/backup/full_2019-12-26.sql
source /data/backup/bin.sql
set sql_log_bin=1;
- 检查数据
mysql> show tables;
- 扩展: 从全备中提取 单库 单表的数据进行恢复
1, 获得表结构
sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLEcity/!d;q' full.sql>createtable.sql
2, 获得INSERT INTO 语句，用于数据的恢复
grep -i 'INSERT INTOcity' full.sqll >data.sql &
3, 获取单库的备份
sed -n '/^-- Current Database:world/,/^-- Current Database:/p' all.sql >world.sql`
Percona Xtrabackup (物理)
MySQL物理备份工具-xtrabackup(XBK、Xbackup)
1. 安装
1.安装依赖包
配置阿里epel源
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下载软件
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安装
yum -y install percona-xtrabackup-*.rpm
2.备份命令介绍:
xtrabackup
innobackupex ******
3.备份恢复方式——物理备份
1.INNODB表
- 进行 checkpoint,将内存已经提交事务的脏页,刷新到磁盘ibd文件,并且 记录LSN号码
- 拷贝InnoDB数据(ibd,frm,ibdata1)文件到预定的备份路径
2.非INNODB表
- FLUSH TABLE WITH READ LOCK(FTWRL) Global READ LOCK（全局锁）
- 拷贝非InnoDB表各种数据
- 解锁
- REDO
自动将备份期间的redo变化保存下来.并记录全新的LSN号码
3.XBK恢复流程:
- prepare 准备恢复过程
(1) redo将变化前滚
(2) Undo将未提交的事务进行回滚
- prepare 准备恢复过程
- copy-back 恢复过程
cp
--copy-back
授权
- copy-back 恢复过程
4. 备份策略介绍
full + inc + binlog -------> 全备+增量+binlog
full + binlog ------> 全备+日志
5. xbk备份工具使用
[root@db01 ~]# vim /etc/my.cnf
[client]
socket=/tmp/mysql.sock
1.全备
innobackupex --user=root --password=123 /data/backup
2.全备自定制目录名
innobackupex --user=root --password=123 --no-timestamp /data/backup/full_$(date +%F)
3.备份信息介绍
- xtrabackup_binlog_info
[root@db01 full_2019-12-27]# cat xtrabackup_binlog_info
mysql-bin.000006 2355 cc5e7931-1fe5-11ea-9f38-000c29a10c63:1-25
作用: 二进制日志截取的起点位置
- xtrabackup_checkpoints
[root@db01 full_2019-12-27]# cat xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 165458551
last_lsn = 165458560
compact = 0
recover_binlog_info = 0
- xtrabackup_info
- xtrabackup_logfile
4.增量备份
- 模拟周一白天数据变化:
create database xbk charset utf8mb4;
use xbk;
create table t1 (id int);
create table t2 (id int);
create table t3 (id int);
insert into t1 values(1);
commit;
insert into t2 values(1);
commit;
insert into t3 values(1);
commit;
- 模拟周一晚上的增量inc1
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/data/backup/full_2019-12-27 /data/backup/inc1
- 模拟周二白天数据变化
use xbk;
insert into t1 values(2);
commit;
insert into t2 values(2);
commit;
insert into t3 values(2);
commit;
- 模拟周二晚上的增量inc2
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/data/backup/inc1 /data/backup/inc2
- 模拟周三白天的数据变化
use xbk;
insert into t1 values(3);
commit;
insert into t2 values(3);
commit;
insert into t3 values(3);
commit;
5.故障恢复
\rm -rf /data/mysql/data_3306/*
恢复思路:
1. 找最近的全备
2. 所有增量(inc1,inc2)合并全备(/data/backup/full_2019-12-27 )中
3. 备份的prepare(应用redo和undo)
4. 恢复备份cp ,改权限
5. 启动数据库
6. 截取周二备份到周三故障点的binlog,进行恢复.
- 增量合并并准备
This option should be used when preparing the base full
backup and when merging all incrementals except the last one.
各个备份的,lsn能够连续.
(1) base_full 的 prepared --apply-log
[root@db01 ~]# innobackupex --apply-log --redo-only /data/backup/full_2019-12-27
(2) 将inc1 合并到full中
innobackupex --apply-log --redo-only --incremental-dir=/data/backup/inc1 /data/backup/full_2019-12-27
(3) 将inc2 合并到 full中
innobackupex --apply-log --incremental-dir=/data/backup/inc2 /data/backup/full_2019-12-27
(4) 整体备份的prepare.
innobackupex --apply-log /data/backup/full_2019-12-27
- 恢复备份
[root@db01 full_2019-12-27]# cp -a * /data/mysql/data_3306/
[root@db01 full_2019-12-27]# chown -R mysql.mysql /data
启动数据库
截取二进制日志
[root@db01 inc2]# cat /data/backup/inc2/xtrabackup_binlog_info
mysql-bin.000003 6593 411148c1-26bf-11ea-a420-000c298780da:1-42
mysqlbinlog --skip-gtids --include-gtids='411148c1-26bf-11ea-a420-000c298780da:43-45' /data/mysql/binlog_3306/mysql-bin.000003 >/data/backup/bin.sql
- 恢复binlog
mysql> set sql_log_bin=0;
mysql> source /data/backup/bin.sql
mysql> set sql_log_bin=1;
- 总备份2T,误删除1G一张核心表.
思路: 表空间迁移.
