MySQL系列（七）：MySQL备份与恢复实战

全量备份与增量备份

全量备份

全量数据就是数据库中所有的数据，全量备份就是把数据库中所有的数据进行备份。

例：

备份所有数据：

mysqldump -uroot -pyjscloud -F -B -A|gzip >/server/backup/mysqlbak_$(date +%F).sql.gz

备份一个库：

mysqldump -uroot -pyjscloud -F -B yjs_stu|gzip >/server/backup/mysqlbak_$(date +%F).sql.gz

增量备份

增量数据是从上次全量备份之后，更新的新数据。对于MySQL来说，binlog日志就是MySQL的增量数据

企业场景全量和增量的频率

1）中小公司，全量一般是每天一次，业务流量低谷执行全备，备份时会锁表

2）单台数据库，如何增量。用rsync（配合定时任务频率大点或者inotify，主从复制把所有binlog备份到远程服务器，尽量做主从复制）

增量备份的例子：

rsync -avz /application/mysql/data/mysql-bin.*  rsync_backup@192.168.0.30::backup --password-file=/etc/rsync.password

3）大公司周备，每周六00点一次全量，下周日-下周六00点前都是增量

优点：节约备份时间，减小备份压力。缺点：增量的binlog文件副本太多，还原会很多麻烦

4）一主五从，会有一个从库做备份，延迟同步。

mysql的mysqldump备份什么时候派上用场？

• 迁移或者升级数据库时。

• 增加从库时候

• 因为硬件或特殊异常情况，主库或从库宕机，从库可以互相切换，无需备份。

• 人为的DDL，DML语句，主从库没有办法了，所有库都会执行。此时需要备份。

• 跨机房灾备，需要备份到异地。

几个问题

1）什么情况下需要增量恢复？

我们再在生产工作中一般常用一主多从的数据库架构，常见的备份方案是在某一个不对外服务的从库上开启binlog，然后实施定时全备和实时增量备份。

什么是增量恢复？

利用二进制日志和全备进行的恢复过程，被称为增量恢复。那么到底什么情况下才需要数据库增量恢复呢？

1、主或者从库宕机（硬件损坏）是否需要增量恢复？

答：不需要增量恢复，主库宕机，只需要把其中一个同步最快的从库切换为主库即可。从库宕机，直接不用就好了（一般都会配LVS负载均衡）。或者正常修复。

2、人为操作数据库SQL语句破坏主库是否需要增量恢复？

在数据库主库内部命令误操作，会导致所有的数据库（包括主从库）数据丢失，例如：在主库执行了drop database test; 这样的删除语句，这时所有的从库也会执行这个drop database test;语句，从而导致所有的数据库上的test库数据丢失，这样的场景是需要增量恢复的。

3）只有一个主库是否需要增量恢复？

如果公司只有一个主库的情况，首先应该做定时全量备份（每天一次）及增量备份（每隔1-10分钟对binlog日志做切割然后备份到其他的服务器上，或者本地其他的硬盘里）或者写到网络文件系统（备份服务器）里。如果不允许数据丢失，最好的办法就是做从库，通过drdb（基于磁盘块的）同步。

正常情况下：

• 主库同步：除了分担读写分离压力外，还可以防止物理设备损坏数据丢失的恢复。

• 从库备份：在从库进行全量和增量方式的备份，可以防止人为对主库的误操作导致数据丢失。确保备份的从库实时和主库是同步状态。

小结：一般由人为（或程序）逻辑的方式在数据库执行的SQL语句误操作，需要增量恢复，因为此时所有的从库也执行了误操作的语句。

MySQL增量恢复必备条件

开启MySQL log-bin日志功能

mysql数据库开启log-bin参数记录binlog日志功能如下：

[root@master ~]# grep log-bin /etc/my.cnf
log-bin=mysql-bin

提示：主库和备份的从库都需要开启binlog记录功能。

小结：增量恢复的条件：存在一份全备加上全备之后的时刻到处问题时刻的所有增量binlog文件备份

存在mysql数据库备份

1）生产环境mysqldump备份命令

备份命令如下：

mysqldump -uroot -pyjscloud --single-transaction -F -B yjs_stu |gzip > /server/backup/mysql_$(date +%F).sql.gz

innodb引擎备份：

mysqldump -u$MYUSER -p$MYPASS -F --single-transaction -A -B | gzip > $DATA_FILE
mysqldump -u$MYUSER -p$MYPASS -F -A -B --lock-all-tables | gzip > $DATA_FILE

MySQL增量恢复案例图解

场景模拟

建库建表

1）创建学生表

create database yjs_stu;
use yjs_stu;
create table student(
Sno int(10) NOT NULL COMMENT '学号',
Sname varchar(16) NOT NULL COMMENT '姓名',
Ssex char(2) NOT NULL COMMENT '性别',
Sage tinyint(2)  NOT NULL default '0' COMMENT '学生年龄',
Sdept varchar(16)  default NULL  COMMENT '学生所在系别',
PRIMARY KEY  (Sno) ,
key index_Sname (Sname)
);

2）学生表插入数据：

INSERT INTO student values(0001,'路飞','男',17,'计算机网络');
INSERT INTO student values(0002,'索隆','男',19,'网络安全');
INSERT INTO student values(0003,'乌索普','男',18,'网络媒体');
INSERT INTO student values(0004,'娜美','女',17,'旅游管理');
INSERT INTO student values(0005,'香吉士','男',22,'新东方厨校');
INSERT INTO student values(0006,'妮可罗宾','女',28,'古代历史');

3）检查数据库及数据

4）定时任务备份数据

这里我们使用脚本备份数据

#!/bin/sh

#parameter defined
MYUSER=root
MYPASS="yjscloud"
#MYSOCK=/application/mysql/tmp/mysql.sock
DBNAME="yjs_stu"
MAIN_PATH=/server/backup
DATA_PATH=/server/backup
LOG_FILE=${DATA_PATH}/mysql_logs_$(date +%F).log
DATA_FILE=${DATA_PATH}/mysql_backup_$(date +%F).sql.gz

#command defined
MYSQL_PATH=/application/mysql/bin
MYSQL_DUMP="$MYSQL_PATH/mysqldump -u$MYUSER -p$MYPASS -F -B --master-data=2 --single-transaction -e  $DBNAME"

#backup command
${MYSQL_DUMP} | gzip > $DATA_FILE

#check backup result
du -sh $DATA_FILE>$LOG_FILE

5）备份完成后插入新数据

INSERT INTO student values(0007,'弗兰奇','男',38,'机械工程');
INSERT INTO student values(0008,'布鲁克','男',58,'音乐学院');

6）模拟用户误删数据

mysql> drop database yjs_stu;

7）检查破坏结果

增量恢复全过程

允许锁库的的情景

通过防火墙禁止web等应用向主库写数据或或者锁表。让主库暂时停止更新。然后再进行恢复。

1）检查全备

2）检查全备后的所有binlog

3）立即刷新并备份出binlog

mysqladmin -uroot -pyjscloud flush-logs

备份刷新出来的binlog

cp mysql-bin.000009 /server/backup/

将新的mysql-bin文件转换为sql文件

mysqlbinlog -d yjs_stu mysql-bin.000009 > bin.sql

4）编辑bin.sql

vim bin.sql 

5）往数据导入数据

mysql -uroot -pyjscloud < mysql_backup_2018-05-20.sql
mysql -uroot -pyjscloud < bin.sql 

6）查看数据库

数据恢复完成

不锁库情景下恢复思想

1）停止一个从库，然后在主库刷新binlog，把mysql-bin.000009恢复成bin.sql（去掉drop语句）

2）把全备mysql_backup_2018-05-20.sql及新插入数据前的增量bin.sql恢复到从库

3）切换到从库提供服务，会有数据丢失插入数据后刷新binlog以后的数据mysql-bin.000010

4）把mysql-bin.000010解析为sql，恢复到从库

以上解决办法会有主键冲突问题