MySQL-备份详解,及实例讲解

一. 备份职责

1.设计备份策略

a.时间
b.增量
c.时间
d.自动

2.日常备份检查

a.备份是否存在
b.空间是否够用

3.定期恢复演练

一季度或者半年,防止备份不能用

4.故障备份恢复

通过备份数据恢复到故障之前时间点

5.生产业务迁移

a.缩短停机时间
b.要有回退方案

二.备份类型

1.热备

2.热备

3.冷备

三.备份工具

1.逻辑备份工具

基于SQL语句进行备份
a.mysqldump
b.mysqlbinlog

2.物理备份工具

基于数据文件备份
a.xtrabackup(XBK): peicona *****
b.MySQL Enterprise Backup(MEB)

四.逻辑备份和物理备份比较

mysqldump

优点: 
a.mysql自带
b.备份出来是SQL,文本格式,可读性高,便于备份处理
c.压缩比较高,节省磁盘空间

缺点:
a.比较消耗CPU和内存资源

建议:
100G以内数据量级,建议使用mysqldump

xtrabackup(XBK)

优点:
a.不会转换SQL,相当于直接CP数据文件
b.性能较高

缺点:
a.可读性差
b.压缩比低,需要更多磁盘空间

建议:
>100G<TB

五.备份策略

1.备份方式

a.全备
b.增量(备份变化的数据)
c.逻辑备份
d.物理备份

2.备份周期

根据数据量设计

六.备份工具使用

1.mysqldump参数使用

-u 用户 -p 密码
-h 地址 -s souket
-P 端口 -A 全备

mysqldump -uroot -p123 -S /tmp/mysql.sock -A >/data/backup/full.sql
(全部备份)
mysqldump -uroot -p123 -A --triggers -R -E --master-data=2  >/data/backup/full.sql
(生产环境必加--triggers -R -E --master-data=2)
(--max_allowed-packet=256M 用于大表备份)

--set-gitd-purged=auto(默认)
auto,on
off
使用场景
1.构建主从复制环境时,使用auto,on
2.日常备份参数 off

2.故障模拟

问题:
昨天有全网备份 
mysqldump -uroot -p123 -A --triggers -R -E --master-data=2  >/data/backup/full.sql
今天不小心把数据库删除了.请还原

准备
create database day1 charset utf8;
use day1;
create table t1(id int);
insert  into  ti values(1),(2),(3);
commit;
use world
update city set countrycode='CHN';
commit;
select * from city limit 10;

解题思路
1.初始化数据库
mysqld --initialize-insecure  --user=mysql --basedir=/app/mysql --datadir=/data/mysql
2.查看full.sql做master-data标记,并且截取二进制日志导出
数据库外执行
mysqlbinlog --skip-gtids --start-position=xxx  /data/binlog/mysql-bin.00000x >/tmp/bin.sql
3.进mysql,临时关闭日志写入
set sql_log_bin=0
4.首先恢复到昨天的备份,再恢复昨天到现在二进制日志的变更
source /data/backup/full.sql
source /tmp/bin.sql