2019-04-29 MySQL企业级备份应用知识与实践
1. 数据库管理员的两大工作核心
1.1 能够让数据安全得到保护
所谓得数据安全，最容易备份误以为是只有数据丢失，其实还包含数据被脱库、泄密等方面。
1.2 能7 x 24小时提供服务
数据库剧本7 x 24小时提供服务的能力，是数据库管理员得重要职责。
2. 全量备份与增量备份
2.1 全量备份的概念
全量数据就是数据库中所有的数据（或某一个库的全部数据）；全量备份就是把数据库中所有的数据进行备份。
下面以InnoDB引擎数据库为例进行讲解。
备份数据库中所有库的所有数据的命令：
mysqldump -B --master-data=2 --single-transaction -A | gzip > /opt/all.sql.gz
备份oldboy一个库中所有数据的命令：
mysqldump -B --master-data=2 --single-transaction oldboy | gzip > /opt/oldboy.sql.gz
2.2 增量备份的概念
增量数据就是指上一次全量备份数据之后到下一次全量备份之前数据库所更新的数据。在使用mysqldump命令做全备时，增量数据就是MySQL的binlog日志，因此，对binlog日志的备份在此处就可以称为增量备份。
2.3 全量与增量如何结合备份
按天全备与增量备份数据示例
按天全备的特点如下：
优点：恢复数据时需要的数据文件数量少，恢复时间短，维护成本低。
缺点：每天一个全备，占用空间多，占用系统资源多，经常备份会影响用户体验。中小企业用得最多的策略就是按天全备，然后根据空间情况保留全备份数，例如仅保留7天内的备份数据，如果企业数据很重要，则可以使用磁带机等设备留存1年以上的备份数据。
binlog增量得清理可以通过在my.cnf中配置“过期清理天数”的相关参数（expire_logs_days = 7）来实现，例如保留7天内的binlog日志，理论上如果每天进行全备，那么binlog只要保留1天就够了。
按周全备与增量备份数据示例
按周全备的特点如下：
优点：每周仅有一个完整备份，因此占用磁盘总空间小，占用系统资源少，备份次数少，用户体验好一些。
缺点：恢复时数据文件多，导致恢复麻烦，维护成本高，恢复时间长。
大型企业由于数据量特别大，每天全备时间太长，因此有可能会采用周备的策略，这样不仅有利于节省数据存储空间而且不会影响用户访问数据库的体验。
3. MySQL常用的备份方式
MySQL备份的常用方式有逻辑备份和物理备份。
3.1 逻辑备份方式
1. 逻辑备份
MySQL的逻辑备份其实就是使用MySQL自带的mysqldump命令或其他相关工具，把MySQL数据以SQL语句的形式导出或备份成文件。在恢复的时候则通过执行mysql恢复命令（或source等）将存储的SQL语句文件数据还原到MySQL数据库中。
2. 逻辑备份的特点
逻辑备份的优点为操作简单、方便、可靠，并且备份的数据可以跨平台、跨版本、甚至跨软件、跨操作系统，还可以实现分库分表备份；逻辑备份也有一定的缺点，例如，备份速度比物理备份慢、恢复的效率也不是特别高等。
3. 逻辑备份的常用工具
mysqldump是MySQL官方自带得最常用的逻辑备份工具，还能实现分表分库备份，也是本章重点讲解的备份工具。除此之外，还有一个mydumper工具，它是一个在GPL许可下发布的高性能MySQL备份和恢复工具集。
4. 逻辑备份的企业应用场景
适用于数据量不是特别大的场景，例如打包前不大于30GB的数据库数据（30GB为参考值）。30GB的值主要是考虑备份效率的问题，以及管理员使用复杂度的平衡。
不过，在跨版本、跨软件升级或迁移数据的时候，此时物理备份一般就不能使用了。
3.2 物理备份方式
1. 物理备份
（1）冷备方法
MySQL的物理备份方法之一是使用cp、rsync、tar、scp等复制工具把MySQL数据文件复制成多份，由于在备份期间数据仍然有写入操作，所以，直接复制的备份方式会引起数据丢失。
一般在进行大规模数据库迁移时，先停库，然后物理迁移。
（2）除了在Linux命令行通过命令直接复制MySQL数据文件之外，还有一些其他的第三方的开源或商业物理热备份工具，如Xtrabackup。这个工具可以实现物理全备及增量备份。
2. 物理备份的特点
优点：速度快，效率高
缺点：不容易跨平台、跨版本、跨软件、跨操作系统，可以实现分库分表备份，但恢复时会麻烦很多，软件的使用也比较复杂一些
3. 物理备份的常用工具或方法
Linux下冷备份工具为cp、tar，备份时需要锁表或者停库以确保数据的一致性；开源的热备份（基于InnoDB）工具则是Xtrabackup。
4. 物理备份的企业应用场景
- 数据库总数据量超过30GB的，可使用Xtrabackup热备工具进行备份，以提升效率
- 可以选择在数据库的从库上进行备份，备份时停止SQL线程应用数据到数据库，然后通过cp或tar打包备份，这也是一种不错的冷备方案，不会影响数据库的服务
3.3 物理备份与逻辑备份的区别
物理备份与逻辑备份的区别
4 逻辑备份的企业级应用实战
4.1 中小企业的MySQL备份实战
1. 中小企业全备备份策略与实践
中小企业一般会采用逻辑备份，常用的工具就是mysqldump命令，备份的策略一般是每日进行全量备份，备份会选择在数据库业务流量低谷时执行，备份时可以锁表或者采用事务方式备份，以下为简单的实战备份脚本案例：
[root@oldboy scripts]# cat bak.sh
#!/bin/bash
export PATH=/application/mysql/bin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
bak_path=/server/backup
file_name=bak_$(date +%F)
[ ! -d $bak_path ] && mkdir -p $bak_path ---若备份路径不存在则创建
mysqldump -B -A --master-data=2 | gzip > $bak_path/${file_name}.sql.gz
---如果仅为innodb引擎，则可以再加上--single-transaction参数
#rsync all data to backup server ---备份完成后立即推送至备份服务器，需要提前部署rsync服务
rsync -az $bak_path/ rsync_backup@172.16.1.31::mysql/ --password-file=/etc/rsync.password
#del expiries file ---删除本地的7天备份，如果空间紧张那么保留三天也可以
find $bak_path/ -type -f -name "*.sql.gz" -mtime +7 | xargs rm -f
稍微复杂点的备份脚本：
[root@oldboy scripts]# cat bak.sh
#!/bin/bash
export PATH=/application/mysql/bin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
bak_path=/server/backup
[ ! -d $bak_path ] && mkdir -p $bak_path
#if week is 6 then bak other file name.
if [ $(date +%w) -eq 6 ] ---如果时间为周六，则
then
file_name=bak_$(date +%w_%F) ---将备份文件名改为周和日期，目的是在备份服务器上保留每周六的数据
else
file_name=bak_$(date +%F) ---否则，备份文件名为日期
fi
mysqldump -B -A --master-date=2 | gzip > $bak_path/${file_name}.sql.gz
md5sum $bak_path/${file_name}.sql.gz > $bak_path/${file_name}.flag
---做md5指纹的目的是用于未来检查备份及传输结果是否正常
#rsync all data to backup server
rsync -az $bak_path/ rsync_backup@172.16.1.31::mysql/ --password-file=/etc/rsync.password
#del expiries file
find $bak_path/ -type -f -name "*.sql.gz" -mtime +7 | xargs rm -f
#send mail to self or adminstrator ---还可以将备份结果发送邮件给管理员，不过最佳策略是在备份服务器上检查备份结果统一发管理员
上述两个脚本是相对比较简单的企业级实战数据库备份脚本，在实际工作中，可能在判断及逻辑上会更复杂一些。
最后配置定时任务，使其每日0点执行上述脚本：
[root@oldboy scripts]# crontab -e ---在最后添加以下两行
#bak mysql for oldboy at 20190427
00 00 * * * /bin/sh /server/scripts/bak.sh &> /dev/null
最终的数据库本地（备份服务器相同）备份结果示例如下：
[root@oldboy scripts]# ll /server/backup/
total 12
-rw-r--r--. 1 root root 73 Apr 26 00:00 bak_2019-04-26.flag ---普通备份对应的指纹文件
-rw-r--r--. 1 root root 20 Apr 26 00:00 bak_2019-04-26.sql.gz ---普通的备份文件
-rw-r--r--. 1 root root 73 Apr 27 00:00 bak_6_2019-04-27.flag ---周六备份对应的指纹文件
-rw-r--r--. 1 root root 20 Apr 27 00:00 bak_6_2019-04-27.sql.gz ---周六的备份文件
[root@oldboy scripts]# cat /server/backup/bak_6_2019-04-27.flag
024226a8b13b16415b6affc62aa55024 /server/backup/bak_6_2019-04-27.sql.gz
将来在备份服务器上可以检查备份是否成功，以及数据在备份过程中是否被改变等：
[root@oldboy backup]# md5sum -c bak_6_2019-04-27.flag ---检测备份文件是否正常以及在传输过程中是否有损坏或被篡改
/server/backup/bak_6_2019-04-27.sql.gz: OK ---OK表示一切正常
一般会在备份服务器上保留最近7天的所有备份，同时保留每周六的全部备份：
[root@oldboy backup]# find /server/backup/ -type f -name "bak_*" -mtime +7 ! -name "bak_6*"
/server/backup/bak_2019-04-26.flag
/server/backup/bak_2019-04-26.sql.gz
2. 全备的数据什么时候可以派上用场
- 迁移或者升级数据库时
- 增加从库的时候
- 人为执行DDL、DML语句破坏数据库数据时（此时若使用主从库就无法防止数据丢失，因为所有库都会执行破坏的语句）
- 跨机房灾备时，此时需要将全备份复制到异地
若是因为硬件或删除物理文件导致数据库故障，就不需要用备份数据恢复了，可以直接把主库关闭，在从库上配置好VIP等配置后，启动从库提供服务即可。
3. 中小企业增量备份策略与实践
比较好的binlog增量备份或MySQL备份方法就是为MySQL数据库配置异机主从复制功能（实时复制功能），即binlog会被实时地发送到从服务器上，这样效果是最好的。当然，也要相应地在主从复制的从库上实现全备。
4. 备份binlog增量文件何时可以派上用场
当需要完整恢复数据库数据的时候，就会需要binlog增量恢复。
5. 企业里MySQL备份策略选择
大多数中小企业得数据库环境都为一主多从，因此，可采取在一个从库服务器上专门做全量以及增量备份。
4.2 中小企业MySQL增量恢复案例实战
假设当前数据库内的数据如下：
[root@oldboy ~]# mysql -e "select * from oldboy.test;"
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | zuma |
| 5 | kaka |
+----+---------+
模拟0点开始对数据库oldboy数据库进行全备：
[root@oldboy ~]# mkdir -p /data/backup
[root@oldboy ~]# mysqldump -B --master-data=2 --single-transaction oldboy | gzip > /data/backup/oldboy_$(date +%F).sql.gz
[root@oldboy ~]# ll /data/backup/
total 4
-rw-r--r--. 1 root root 870 Apr 27 07:01 oldboy_2019-04-27.sql.gz
模拟0点全备后用户继续写入数据：
[root@oldboy ~]# mysql -e "use oldboy;insert into test values(6,'bingbing');"
[root@oldboy ~]# mysql -e "use oldboy;insert into test values(7,'xiaoting');"
[root@oldboy ~]# mysql -e "select * from oldboy.test;"
+----+----------+
| id | name |
+----+----------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | zuma |
| 5 | kaka |
| 6 | bingbing | ---全备后写入的数据
| 7 | xiaoting |
+----+----------+
模拟上午10点管理人员删除oldboy数据库：
[root@oldboy ~]# date -s "2019-04-27 10:00"
Sat Apr 27 10:00:00 CST 2019
[root@oldboy ~]# mysql -e "drop database oldboy;show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldboy_utf8 |
| performance_schema |
+--------------------+
假设数据库出问题10分钟后，发现故障，联系运维人员DBA处理问题。DBA会查看网站报错（或查看后台日志），可以看到连不上oldboy数据库的提示，登录数据库排查，发现数据库已经不存在了。登录系统分析系统审计日志，以及分析数据库binlog可以发现库丢失的原因。
找到原因后，准备开始恢复。恢复前先要移走所有binlog增量文件，防止被二次破坏，并确认是否有全备：
[root@oldboy ~]# cp -a /application/mysql/data/oldboy-bin.* /data/backup/
[root@oldboy ~]# ll /data/backup/
total 28
-rw-r--r--. 1 root root 870 Apr 27 07:01 oldboy_2019-04-27.sql.gz ---全备
-rw-rw----. 1 mysql mysql 168 Feb 7 21:46 oldboy-bin.000001
-rw-rw----. 1 mysql mysql 168 Feb 7 21:47 oldboy-bin.000002
-rw-rw----. 1 mysql mysql 8899 Apr 27 10:00 oldboy-bin.000003
-rw-rw----. 1 mysql mysql 60 Feb 7 21:47 oldboy-bin.index
数据库恢复：
1)停止数据库对外访问。因为是通过drop命令删除数据库的，后面不会有写入操作，因此，这里可以不用额外停止写入，但如果是因为update导致得数据破坏，最好是停库处理或对外停止写入。这里采用iptables防火墙屏蔽所有应用程序的写入：
[root@oldboy ~]# iptables -I INPUT -p tcp --dport 3306 ! -s 192.168.9.115 -j DROP ---非192.168.9.115禁止访问数据库3306端口
---系统是CentOS7的话默认防火墙是firewalld，需自行改成firewalld的规则
2）解压全备的数据：
[root@oldboy ~]# cd /data/backup/
[root@oldboy backup]# gzip -cd oldboy_2019-04-27.sql.gz > oldboy.sql
[root@oldboy backup]# ls -lrt oldboy.sql
-rw-r--r--. 1 root root 2205 Apr 27 10:14 oldboy.sql
3）解析binlog文件增量数据：
由于在全备时增加了--master-data=2参数，所以可以知道全备后的binlog文件名以及binlog文件对应的恢复位置点。
[root@oldboy backup]# sed -n '22p' oldboy.sql ---这边的案例是在第22行，所以只看第22行
-- CHANGE MASTER TO MASTER_LOG_FILE='oldboy-bin.000003', MASTER_LOG_POS=8343;
从上面的代码可以看到，要从oldboy-bin.000003文件的8343位置点开始恢复增量数据：
[root@oldboy backup]# mysqlbinlog -d oldboy oldboy-bin.000003 --start-position 8343 -r bin.sql
工作中除了oldboy-bin.000003文件之外，还可能生成oldboy-bin.000004、oldboy-bin.000005……多个binlog文件，现在可以继续恢复后面的所有binlog文件：
[root@oldboy backup]# mysqlbinlog -d oldboy oldboy-bin.000004 oldboy-bin.000005 -r bin1.sql
---由于此次模拟环境只有一个binlog文件oldboy-bin.000003，因此此命令就不要执行了
4）需要注意得是，这里需要剔除误删数据库的drop语句，否则，直接恢复就又进入了删库故障的坑了：
[root@oldboy backup]# grep -w drop bin.sql ---把误删语句找出来
drop database oldboy
[root@oldboy backup]# sed -i '/drop database oldboy/d' bin.sql ---删除drop数据库oldboy的语句
现在准备工作全部完成，开始正式恢复。
先恢复出故障以前得全备数据：
[root@oldboy backup]# mysql < /data/backup/oldboy.sql ---先恢复全备，即出故障以前的备份
[root@oldboy backup]# mysql -e "select * from oldboy.test;"
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | zuma |
| 5 | kaka |
+----+---------+
可以看到，出故障以前的数据已恢复，但0点全备到出故障时间的增量还没有。
再恢复增量数据，即从binlog中解析出清理了drop语句的bin.sql文件：
[root@oldboy backup]# mysql oldboy < /data/backup/bin.sql ---恢复增量文件，由于增量备份没有use db，所以需要指定数据库名
[root@oldboy backup]# mysql -e "select * from oldboy.test;"
+----+----------+
| id | name |
+----+----------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | zuma |
| 5 | kaka |
| 6 | bingbing | ---增量的数据回来了
| 7 | xiaoting | ---增量的数据回来了
+----+----------+
5. 分库分表的生产备份策略
5.1 为什么要分库分表备份
还原时，很多时候只需要还原一个库或者多个库的一个表，这个时候，整个备份文件就会很难拆分，给恢复也会带来麻烦。对于这种情况，最好是分库分表备份。
5.2 如何进行分库备份
最佳的方法就是从数据库中取出所有库名，然后对每个数据库执行一次备份。
分库备份的脚本如下：
[root@oldboy scripts]# cat fenku.sh
#!/bin/bash
export PATH=/application/mysql/bin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
bak_path=/server/bakup/$(date +%F)
[ ! -d $bak_path ] && mkdir -p $bak_path
for dbname in `mysql -e "show databases" | sed '1,2d' | grep -v _schema` ---取库名轮询备份
do
mysqldump -B --master-data=2 $dbname | gzip > $bak_path/${dbname}_$(date +%F).sql.gz
done
执行分库脚本并查看备份结果：
[root@oldboy scripts]# sh fenku.sh
[root@oldboy scripts]# ll /server/bakup/2019-04-27/
total 188
-rw-r--r--. 1 root root 181184 Apr 27 11:22 mysql_2019-04-27.sql.gz
-rw-r--r--. 1 root root 890 Apr 27 11:22 oldboy_2019-04-27.sql.gz
-rw-r--r--. 1 root root 604 Apr 27 11:22 oldboy_utf8_2019-04-27.sql.gz
5.3 如何进行分表备份
分表备份比分库更细，实际上就是先取一个库名，然后循环读取该库里的表进行备份，备份完之后，再取下一个库名，继续循环库里的所有表进行备份，直到所有库里的所有表都备份完毕。
实际脚本如下：
[root@oldboy scripts]# cat fenbiao.sh
#!/bin/bash
export PATH=/application/mysql/bin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
bak_path=/server/bakup/$(date +%F)
[ ! -d bak_path ] && mkdir -p $bak_path
for dbname in `mysql -e "show databases" | sed '1,2d' | grep -v _schema`
do
for tablename in `mysql -e "show tables from $dbname;" | sed '1d'`
do
mysqldump -B --master-data=2 $dbname $tablename | gzip > $bak_path/${dbname}_${tablename}_$(date +%F).sql.gz
done
done
执行分表脚本并查看备份结果：
[root@oldboy scripts]# sh fenbiao.sh
[root@oldboy scripts]# ll -tr /server/bakup/2019-04-27/
total 5232
-rw-r--r--. 1 root root 181184 Apr 27 11:22 mysql_2019-04-27.sql.gz
-rw-r--r--. 1 root root 890 Apr 27 11:22 oldboy_2019-04-27.sql.gz
-rw-r--r--. 1 root root 604 Apr 27 11:22 oldboy_utf8_2019-04-27.sql.gz
-rw-r--r--. 1 root root 181020 Apr 27 11:40 mysql_columns_priv_2019-04-27.sql.gz
-rw-r--r--. 1 root root 181020 Apr 27 11:40 mysql_db_2019-04-27.sql.gz
-rw-r--r--. 1 root root 181020 Apr 27 11:40 mysql_event_2019-04-27.sql.gz
-rw-r--r--. 1 root root 181020 Apr 27 11:40 mysql_func_2019-04-27.sql.gz
-rw-r--r--. 1 root root 181020 Apr 27 11:40 mysql_general_log_2019-04-27.sql.gz
-rw-r--r--. 1 root root 181020 Apr 27 11:40 mysql_help_category_2019-04-27.sql.gz
-rw-r--r--. 1 root root 181020 Apr 27 11:40 mysql_help_keyword_2019-04-27.sql.gz
-rw-r--r--. 1 root root 181020 Apr 27 11:40 mysql_help_relation_2019-04-27.sql.gz
-rw-r--r--. 1 root root 181020 Apr 27 11:40 mysql_help_topic_2019-04-27.sql.gz
-rw-r--r--. 1 root root 181020 Apr 27 11:40 mysql_innodb_index_stats_2019-04-27.sql.gz
-rw-r--r--. 1 root root 181020 Apr 27 11:40 mysql_innodb_table_stats_2019-04-27.sql.gz
-rw-r--r--. 1 root root 181020 Apr 27 11:40 mysql_ndb_binlog_index_2019-04-27.sql.gz
-rw-r--r--. 1 root root 181020 Apr 27 11:40 mysql_plugin_2019-04-27.sql.gz
-rw-r--r--. 1 root root 181020 Apr 27 11:40 mysql_proc_2019-04-27.sql.gz
-rw-r--r--. 1 root root 181020 Apr 27 11:40 mysql_procs_priv_2019-04-27.sql.gz
-rw-r--r--. 1 root root 181020 Apr 27 11:40 mysql_proxies_priv_2019-04-27.sql.gz
-rw-r--r--. 1 root root 181020 Apr 27 11:40 mysql_servers_2019-04-27.sql.gz
-rw-r--r--. 1 root root 181020 Apr 27 11:40 mysql_slave_master_info_2019-04-27.sql.gz
-rw-r--r--. 1 root root 181020 Apr 27 11:40 mysql_slave_relay_log_info_2019-04-27.sql.gz
-rw-r--r--. 1 root root 181020 Apr 27 11:40 mysql_slave_worker_info_2019-04-27.sql.gz
-rw-r--r--. 1 root root 181020 Apr 27 11:40 mysql_slow_log_2019-04-27.sql.gz
-rw-r--r--. 1 root root 181020 Apr 27 11:40 mysql_tables_priv_2019-04-27.sql.gz
-rw-r--r--. 1 root root 181020 Apr 27 11:40 mysql_time_zone_2019-04-27.sql.gz
-rw-r--r--. 1 root root 181020 Apr 27 11:40 mysql_time_zone_leap_second_2019-04-27.sql.gz
-rw-r--r--. 1 root root 181020 Apr 27 11:40 mysql_time_zone_name_2019-04-27.sql.gz
-rw-r--r--. 1 root root 181020 Apr 27 11:40 mysql_time_zone_transition_2019-04-27.sql.gz
-rw-r--r--. 1 root root 181020 Apr 27 11:40 mysql_time_zone_transition_type_2019-04-27.sql.gz
-rw-r--r--. 1 root root 181020 Apr 27 11:40 mysql_user_2019-04-27.sql.gz
-rw-r--r--. 1 root root 795 Apr 27 11:40 oldboy_test_2019-04-27.sql.gz
事实上，分库分表的数据尽量不要用于完整恢复，因为binlog有可能会有写入操作，用于单库单表的恢复，如果非要用来做完整恢复，最好通过写脚本来实现。
6. MySQL生产常用备份架构方案
在中小公司一般比较常用的做法是，每日0点执行全备任务，先把数据按照日期备份到数据库本地，然后推送到数据库备份服务器，由于本地空间有限，因此本地仅保留3~7日的全备。
如果有备用的服务器资源可用，那么最好通过主从同步的方式进行备份，这样即使物理机损坏了也可以很快地切换到新服务器（还可以HA自动切换），但是主从复制得缺点是不能解决错误执行SQL语句的问题。
因此，一般会在一台不对外提供业务的从库上使用上述的mysqldump或Xtrabackup来进行定时备份。
