mysql如何实现备份策略

这篇文章将为大家详细讲解有关mysql如何实现备份策略，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

设计场景

1）增量备份在周一到周六凌晨3点，复制mysql-bin.00000*到指定目录；
2）全量备份则使用mysqldump将整个数据库导出，每周日凌晨3点执行，并会删除上周留下的mysq-bin.00000*，然后对mysql的备份操作会保留在bak.log文件中。

技术点

Mysqldump、mysqlbinlog、crontab

服务器信息

主机：centos7；数据库：mysql5.7

准备工作

开启binlog日志功能
（1）新建目录，执行：

#mkdir /home/mysql
#cd /home/mysql
#mkdir mysql-bin.      #增量日志文件目录

（2）修改所属的用户/组：（不修改，mysql无法重启）

#chown -R mysql.mysql mysql-bin

（3）修改mysql配置文件，执行：

#vim /etc/my.cnf

其中，server-id表示单个结点的id，这里由于只有一个结点，所以可以把id随机指定为一个数，这里将id设置成1。若集群中有多个结点，则id不能相同(对于5.7以下版本不需要指定server-id)；
log_bin指定binlog日志文件的存储路径，日志文件以mysql-bin开头。

（4）重启mysql，执行：

#systemctl restart mysqld.service

（5）查看日志文件：

#cd /home/mysql/mysql-bin

（6）进入数据库，查看启动效果:

#show variables like '%log_bin%';

编写全量备份脚本(Mysql-FullyBak.sh)

进入/home/mysql目录
新建目录：mkdir backup
进入backup目录，新建daily目录：mkdir backup
切换到/home/mysql目录，执行：

#vim Mysql-FullyBak.sh

参数说明：
–lock-tables
锁定当前导出的数据表，而不是一下子锁定全部库下的表。本选项只适用于MySQL数据库引擎为MyISAM 表，如果是 Innodb 表可以用 –single-transaction 选项。
–flush-logs
结束当前日志，生成新日志文件。
–delete-master-logs
清除以前的日志，以释放空间。但是如果服务器配置为镜像的复制主服务器，用–delete-master-logs删掉MySQL二进制日志很危险，因为从服务器可能还没有完全处理该二进制日志的内容。在这种情况下，使用 PURGE MASTER LOGS更为安全。
–quick
该选项在导出大表时很有用，它强制 MySQLdump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中。
–single-transaction
该选项在导出数据之前提交一个 BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于事务表，例如 InnoDB 和 BDB。本选项和 –lock-tables 选项是互斥的，因为lock-tables会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用 –quick 选项。
–events
导出事件
–master-data=2
其中参数–master-data=[0|1|2]
0: 不记录
1：记录为CHANGE MASTER语句
2：记录为注释的CHANGE MASTER语句
–master-data=2 选项将会在输出SQL中记录下完全备份后新日志文件的名称，
用于日后恢复时参考，例如输出的备份SQL文件中含有：
CHANGE MASTER TO MASTER_LOG_FILE='MySQL-bin.000002′, MASTER_LOG_POS=106;

编写增量备份脚本

切换到/home/mysql目录，执行：

#vim Mysql-DailyBak.sh

设置定时任务crontab

（1）安装crontab（centos7默认已经安装）：

#yum install crontabs

服务操作说明：

#/bin/systemctl start crond.service //启动服务
#/bin/systemctl stop crond.service //关闭服务
#/bin/systemctl restart crond.service //重启服务
#/bin/systemctl reload crond.service //重新载入

配置:

#/bin/systemctl status crond.service //服务状态

加入开机自动启动：

#chkconfig –level 35 crond on

（2）在命令行输入:

#crontab -e

添加相应的任务，wq存盘退出

#每个星期日凌晨3:00执行完全备份脚本
0 3 * * 0 /bin/bash -x /home/mysql/Mysql-FullyBak.sh >/dev/null 2>&1
#周一到周六凌晨3:00做增量备份
0 3 * * 1-6 /bin/bash -x /home/mysql/Mysql-DailyBak.sh >/dev/null 2>&1

说明：默认情况下，crontab执行一次任务后，会通过email通知用户，为避免每次发信息，加入/dev/null 2>&1

（3）查看定时任务：#crontab -l

参数与说明:
crontab -u //设定某个用户的cron服务，一般root用户在执行这个命令的时候需要此参数 ;
　　crontab -l //列出某个用户cron服务的详细内容;
　　crontab -r //删除所有用户的cron服务;
　　crontab -e //编辑某个用户的cron服务;
　　例如：root查看自己的cron设置:crontab -u root -l
　　例如：root删除用户fred的cron设置:crontab -u fred -r
补充：
（1）可直接编辑/etc/crontab 文件，即vi /etc/crontab，添加相应的任务（针对整个系统的crontab文件）;
（2）crontab执行定时任务的记录会写入到/var/log/cron这个文件中，该记录以帐号为区分。

恢复操作

恢复过程亦会写入日志文件，如果数据量很大，建议先关闭binlog日志功能
1、场景：假设早上9点的时候，数据库被攻击，drop了整个数据库！
2、恢复思路：
利用全备的sql文件中记录的CHANGE MASTER语句，binlog文件及其位置点信息，找出binlog文件中增量的那部分。
用mysqlbinlog命令将上述的binlog文件导出为sql文件，并剔除其中的drop语句。
通过全备文件和增量binlog文件导出的sql文件，就可以恢复到完整的数据。
3、恢复步骤：

（1）首先，解压最新的全量备份文件，进入备份文件目录，执行：

#tar -zxvf XXX.sql.tgz

(2)查看全备之后新增的binlog文件，执行：

#grep CHANGE XXX.sql

由图可知，这是全备时刻的binlog文件位置，即mysql-bin.000003的154行，因此在该文件之前的binlog文件中的数据都已经包含在这个全备的sql文件中。

(3)恢复mysql-bin.000003文件的154行之后的信息

进入到mysql-bin.000003目录，执行(sysecokit为数据库名);

#mysqlbinlog --start-position=154 --database=sysecokit mysql-bin.000003 | mysql -uroot -p -v sysecokit

（4）将其他binlog文件（除去mysql-bin.000003）导出sql文件，执行(-d指定数据库)：

#mysqlbinlog -d sysecokit mysql-bin.00000X >00Xbin.sql

(5) vim编辑最新的00Xbin.sql删除其中的drop语句
(6)恢复全备数据，执行：

#mysql -uroot -p < XXX.sql

如：#mysql -uroot -p < 20180716.sql
(7)恢复增量数据，执行（syseco为数据库名称）：

#mysql -uroot -p syseco<00Xbin.sql

如：#mysql -uroot -p syseco<004bin.sql
自此，已经完成所有工作，让我们查看一下运行一周后产生的文件：

关于“mysql如何实现备份策略”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。