第七章 mysql的备份与恢复

mysql的备份与恢复

一  mysql 备份的类型

冷备份：关闭数据、停止业务
温备份：加锁备份
热备份：在先备份，不会影响到也正常运行

 

二 备份方式

 

1. 逻辑备份：基于SQL语句的备份

（1）mysqldump建库、建表、数据插入
（2）基于二进制日志：数据库的所有变化类的操作
（3）基于复制的备份：将二进制日志实时传送到另一台机器并且恢复

2. 物理备份

（1）xtrabackup进行物理备份
（2）拷贝数据文件（冷备）

 

 

三 备份工具

（1）mysqldump
　　　　mysql原生自带很好用的逻辑备份工具
（2）mysqlbinlog
　　　　实现binlog备份的原生态命令
（3）xtrabackup
　　   precona公司开发的性能很高的物理备份工具

 

 

四 mysqldump备份工具使用

　　优点：逻辑备份工具，都是SQL语句，都是文本格式，便于查看和编辑，更便于压缩
　　缺点：备份效率较慢

 

1.mysqldump常用参数

　　-u -p -h  -S  -P 

2. 全库备份

　　-A

mysqldump -uroot -p123456 -A >/backup/full.sql

 

 

3. 单库备份

例子：

mysqldump -uroot -p123456 -B ysl >/backup/ysl.sql
mysqldump -uroot -p123456  ysl >/backup/ysl1.sql

 

-B 增加建库（create）及（use库）的语句   后面恢复时，不需要手动创建库和use

不加 -B 后面恢复时，要先创库  use库

另外 -B 选项可以实现， 同时备份多个库，备份到一个文件中

mysqldump -uroot -p123456 -B ysl test >/backup/ysl_test.sql

如果不加 -B 就是备份 ysl库下 test表

mysqldump 库1 表1 表2 表3 > 库1.sql

4. 生产环境备份额外参数

　　-R　　　　        备份存储过程和函数数据
　　--triggers 　　   备份触发器数据

mysqldump -uroot -p123456  -A  -R --triggers   >/backup/ysl.sql

 

-F, --flush-logs   　　   刷新binlog日志，为了方便将来二进制日志截取时的起点  缺点有多少个库 就会刷新多少次binlog日志

--master-data={1|2}      告诉你备份后时刻的binlog位置 2 注释              1 非注释,要执行(主从复制)

 

 

锁表:适合所有引擎(myisam,innodb)
　　-x, --lock-all-tables
　　-l, --lock-tables

　　--single-transaction 对innodb引擎进行热备
　　通过快照的方式实现热备

 

压缩备份:

#压缩备份:

mysqldump -uroot -p123 -A  -R --triggers --master-data=2 --single-transaction |gzip >/backup/all_$(date +%F).sql.gz

 

解压 ：

　　gunzip   all_2019-09-16.sql

　　

5. mysqldump备份恢复

使用source命令进行恢复:
mysql>set sql_log_bin=0;
mysql> source /backup/xxx.sql;

 

五.  模拟故障并恢复（mysqldump + mysqlbinlog）

背景环境：
    正在运行的网站系统，mysql数据库，数据量25G，日业务增量10-15M。
备份方式：
    每天23:00点，计划任务调用mysqldump执行全备脚本
故障时间点：
    上午10点，误删除了一个表
-------
思路：
    1、断开业务，防止对数据库二次伤害，挂出维护页面
    2、搭建备用库，恢复全备
    3、截取昨天晚上23:00之后到上午10点误删除操作之前的二进制日志
    4、恢复到备用库，验证数据可用性和完整性
    5、两种方案恢复前端应用
        5.1 备用库导出误删除的表，导入到生产库，开启业务
        5.2 直接将应用切割到备用库，替代生产库，开启业务

 

 

1. 原始数据创建

create database ysl   charset utf8mb4;
create table student(id int, name varchar(20)) engine=innodb;    
use ysl;
insert into student values (1,'y1');
insert into student values (2,'y2');
insert into student values (3,'y3');
commit;

 

 

 

2. 晚上23点到了  开始备份数据

mysqldump -uroot -p123456 -A  -R --triggers --master-data=2  --single-transaction | gzip  >/backup/all_$(date +%F).sql.gz

 

 

3.模拟第二天（23:00-10:00）业务对数据的修改

insert into student values (4,'zhang33');
insert into student values (5,'li44');
insert into student values (6,'wang54');
commit;

 

 

4. 模拟故障

drop table student;

 

 

5、恢复

1) 准备全量 并获取到备份文件中的binlog的截取起点

cd /backup/; gunzip all_2019-09-17.sql

 

-- CHANGE MASTER TO MASTER_LOG_FILE='my-bin.000002', MASTER_LOG_POS=852779;
-- 找到全量截取起点

-- 找到   结束点
show  binlog events in 'my-bin.000002';  

 

 

2）截取二进制日志

mysqlbinlog   --start-position=852779  --stop-position=853177 /data/binlog/my-bin.000002 >/backup/binlog.sql

 

3)恢复全备+binlog

set sql_log_bin=0;
source /backup/all_2019-09-17.sql
source /backup/binlog.sql

 

 

---

 

六.  Xtrabackup物理备份工具使用

https://www.cnblogs.com/augustyang/p/11535355.html