MySQL的数据备份与恢复

一、数据备份介绍

1.1 为何要备份

在生产环境中我们数据库可能会遭遇各种各样的不测从而导致数据丢失, 大概分为以下几种.
硬件故障
软件故障
自然灾害
黑客攻击
误操作 (占比最大)

须知在生产环境中，服务器的硬件坏了可以维修或者换新，软件崩溃可以修复或重新安装, 但是如果数据没了那可就毁了，生产环境中最重要的应该就是数据了。所以, 为了在数据丢失之后能够恢复数据, 我们就需要定期的备份数据。

ps：
在之前的章节里我们介绍过innodb存储引擎有自动的数据恢复功能，在执行一条写操作并且commit成功时innodb存储引擎会将新数据其写入redo log，此时如果数据库挂掉，重启后仍然可以依据redo log来恢复尚未执行完毕的数据，这跟本节讲的数据备份与恢复是两回事，试问，如果你一张表的数据不小心删掉了，redo log能帮你恢复吗，no，
**它只能帮你完成你尚未完成的操作，而对于你已经完成的操作，它帮不了你。**所以不要混淆，为了防止数据意外丢失，我们还是应该做好定期的数据备份工作。

1.2 备份什么

我们要备份什么?

一般情况下, 我们需要备份的数据分为以下几种
数据
二进制日志, InnoDB事务日志
代码(存储过程、存储函数、触发器、事件调度器)
服务器配置文件

1.3 备份的类型

1）冷备、温备、热备

按照备份时数据库的运行状态，可以分为三种

1）冷备：停库、停服务来备份 即当数据库进行备份时, 数据库不能进行读写操作, 即数据库 要下线 
2）温备：不停库、不停服务来备份，会(锁表)阻止用户的写入 即当数据库进行备份时, 数据库的读操作可以执行, 但是不能 执行写操作 
3）热备（建议）：不停库、不停服务来备份，也不会(锁表)阻 止用户的写入 即当数据库进行备份时, 数据库的读写操作均不是受影响

MySQL中进行不同类型的备份还要考虑存储引擎是否支持
MyISAM
温备
冷备

InnoDB
热备
温备
冷备

2）物理与逻辑

按照备份的内容分，可以分为两种
1、物理备份：直接将底层物理文件备份
2、逻辑备份：通过特定的工具从数据库中导出sql语句
或者数据，可能会丢失数据精度

3）全量、差异、增量

按照每次备份的数据量，可以分为
全量备份/完全备份（Full Backup）：备份整个数据集(
即整个数据库 )
部分备份：备份部分数据集(例如: 只备份一个表的变化)

而部分备份又分为：差异备份和增量备份两种

# 1、差异备份（Differential Backup） 
每次备份时，都是基于第一次完全备份的内容，只备份有差异 的数据(新增的、修改的、删除的)，例如 
第一次备份：完全备份
第二次备份：以当前时间节点的数据为基础，备份与第一次备 份内容的差异 第三次备份：以当前时间节点的数据为基础，备份与第一次备 份内容的差异 第四次备份：以当前时间节点的数据为基础，备份与第一次备 份内容的差异 第五次备份：以当前时间节点的数据为基础，备份与第一次备 份内容的差异

# 2、增量备份（Incremental Backup ） 
每次备份时，都是基于上一次备份的内容（注意是上一次，而不是第一次），只备份有差异的数据(新增的、修改的、删除 的)，所以增量备份的结果是一条链，例如 
第一次备份：完全备份 
第二次备份：以当前时间节点的数据为基础，备份与第一次备 份内容的差异 第三次备份：以当前时间节点的数据为基础，备份与第二次备 份内容的差异 第四次备份：以当前时间节点的数据为基础，备份与第三次备 份内容的差异 第五次备份：以当前时间节点的数据为基础，备份与第四次备 份内容的差异 

针对上述三种备份方案，如何恢复数据呢

# 1、全量备份的数据恢复 
只需找出指定时间点的那一个备份文件即可，即只需要找到一个文件即可

# 2、差异备份的数据恢复 
需要先恢复第一次备份的结果，然后再恢复最近一次差异备份 的结果，即需要找到两个文件

# 3、增量备份的数据恢复 
需要先恢复第一次备份的结果，然后再依次恢复每次增量备 份，直到恢复到当前位置，即需要找到一条备份链

综上，对比三种备份方案 
1、占用空间：全量 > 差异 > 增量 
2、恢复数据过程的复杂程度：增量 > 差异 > 全量

1.4 备份的工具

此外，如果考虑到增量备份，还需要结合binlog日志（binlog只属于增量恢复），需要用到工具mysqlbinlog，相当于逻辑备份的一种

二、设计备份策略

2.1 备份策略设计的参考值

备份数据的策略要根据不同的应用场景进行定制, 大致有几个参考数值, 我们可以根据这些数值从而定制符合特定环境中的数据备份策略
能够容忍丢失多少数据
恢复数据需要多长时间
需要恢复哪一些数据

2.2 三种备份策略及应用场景

针对不同的场景下, 我们应该制定不同的备份策略对数据库
进行备份, 一般情况下, 备份策略一般为以下三种
直接cp,tar复制数据库文件
mysqldump+复制BIN LOGS
lvm2快照+复制BIN LOGS
xtrabackup

以上的几种解决方案分别针对于不同的场景

1. 如果数据量较小, 可以使用第一种方式, 直接复制数据库文件
2. 如果数据量还行, 可以使用第二种方式, 先使用mysqldump对数据库进行完全备份, 然后定期备份BINARY LOG达到增量备份的效果
3. 如果数据量一般, 而又不过分影响业务运行, 可以使用第三种方式, 使用lvm2的快照对数据文件进行备份, 而后定期备份BINARY LOG达到增量备份的效果
4. 如果数据量很大, 而又不过分影响业务运行, 可以使用第四种方式, 使用xtrabackup进行完全备份后, 定期使用xtrabackup进行增量备份或差异备份

三、备份实战

3.1 使用cp进行备份

备份步骤

#1、向所有表施加读锁 
FLUSH TABLES WITH READ LOCK; 

#2、备份数据文件 
mkdir /egon_bak 
cp -a /var/lib/mysql/* /egon_bak 

模拟数据丢失并恢复

# 数据丢失 
rm -rf /var/lib/mysql/* 

# 恢复数据 
cp -a /egon_bak/* /var/lib/mysql

# 重启服务 
systemctl restart mysql

3.2 使用mysqldump+复制BINARY LOGS备份

mysqldump命令

#==========语法 
mysqldump -h 服务器 -u用户名 -p密码 选项与参数 > 备份文件.sql 

===选项与参数 
-A/--all-databases           所有库 
-B/--databases bbs db1 db2   多个数据库 
db1                          数据库名
db1 t1 t2                    db1数据库的表t1、t2 
-F                           备份的同时刷新 

binlog 
-R                备份存储过程和函数数据（如果开发写了函数和存储过程， 就备，没写就不备） 
--triggers        备份触发器数据（现在都是开发写触发器） 
-E/--events       备份事件调度器 
-d                仅表结构 
-t                仅数据 

--master-data=1   备份文件中 change master语句是 没有注释的，默认为1，用于已经制作好了主从，现在想扩展一个从库的时候使用，如此备份，扩展添加从库时导入备份文件后 便不需要再加mater_pos了 
change matser to 
master_host='10.0.0.111' 
master_user='rep' 
master_password=123 
master_log_pos=120 
master_log_file='master- bin.000001' 

--master-data=2 备份文件中 change master语句是 被注释的 

--lock-all-tables, 提交请求锁定所有数据库中的所有 表，以保证数据的一致性。这是一个全局读锁，并且自动关闭- -single-transaction 和--lock-tables 选项，他们是互斥的。对于支持事务的表例如InnoDB和BDB，推荐使用--single- transaction选项，因为它根本不需要锁定表 

--single-transaction： 快照备份 （搭配--master- data可以做到热备） 
该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不 会阻塞任何应用程序且能保证导出时数据库的一致性状态。它 只适用于多版本存储引擎，仅InnoDB。 
本选项和--lock-tables 选项是互斥的，不能同时存在，因 为LOCK TABLES 会使任何挂起的事务隐含提交。 

#==========完整语句 
mysqldump -uroot -pEgon@123 -A -R --triggers - -master-data=2 –single-transaction > /backup/full.sql 

#====文件太大时可以压缩 gzip ，但是gzip不属于mysql 独有的命令，可以利用管道 
mysqldump -uroot -pEgon@123 -A -R --triggers - -master-data=2 --single-transaction | gzip > /tmp/full$(date +%F).sql.gz 

#====导出时压缩了，导入时需要解压，可以使用zcat命令， 很方便 
zcat /backup/full$(date +%F).sql.gz | mysql - uroot -p123

储备知识：binlog内容很多，如何定位到某个固定的点

===> 1、grep过滤
===> 2、检查事件：依据End_log_pos的提示，来确定某一 个事件的起始位置与结束位置 
mysql> show binlog events in 'mybinlog.000001'; 

如果事件很多，可以分段查看 
mysql> show binlog events in 'mybinlog.000001' limit 0,30; mysql> show binlog events in 'mybinlog.000001' limit 30,30; mysql> show binlog events in 'mybinlog.000001' limit 60,30;

===> 3、利用mysqlbinlog命令 
生产中很多库，只有一个库的表被删除，我不可能把所有的库 都导出来筛选，因为那样子binlog内容很多，辨别复杂度高， 我们可以利用

[root@egon mysql]# mysqlbinlog -d db1 --start- position=123 --stop-position=154 mybinlog.000001 --base64-output=decode-rows - vvv | grep -v 'SET' 

参数解释： 
1）-d 参数接库名 
mysqlbinlog -d database --base64- output=decode-rows -vvv mysql-bin.000002 
2）--base64-output 显示模式 
3）-vvv 显示详细信息

备份：

# 1、先打开binlog日志 
vim /etc/my.cnf 
[mysqld] 
server_id=1 
log-bin=/var/lib/mysql/mybinlog 
binlog_format='row' #(row,statement,mixed) binlog_rows_query_log_events=on 
max_binlog_size=100M 

# 2、登录数据库，插入测试数据 
mysql> create database db3; 
mysql> use db3; mysql> create table t1(id int); 
mysql> insert t1 values(1),(2),(3