Mysql设置定时全量同步与增量同步实操

前言

数据库数据备份是任何业务系统都应考虑到的方面。否则一旦数据库发生误删（或者恶意的删库跑路），那可真是欲哭无泪。我所了解的常见备份方式有两种：全量备份与增量备份。

全量备份

全量备份其实就是将我们的目标数据库整个导出为.sql文件，使用的是Mysql自带的功能mysqldump。

关于mysqldump命令，简单的介绍下:

# 导出整个数据库(包括数据库中的数据）
mysqldump -u username -p dbname > dbname.sql  

# 导出数据库结构（不含数据）
mysqldump -u username -p -d dbname > dbname.sql 

# 导出数据库中的某张数据表（包含数据）
mysqldump -u username -p dbname tablename > tablename.sql 

# 导出数据库中的某张数据表的表结构（不含数据）
 mysqldump -u username -p -d dbname tablename > tablename.sql 

了解完mysqldump 的基本使用，为了便于我们进行数据库的全量备份，可以写一个脚本，并设置自动执行。

全量备份脚本

定义的全量备份脚本如下:

#!/bin/bash
#在使用之前，请提前创建以下各个目录
#获取当前时间
date_now=$(date "+%Y%m%d-%H%M%S")
# 备份文件夹
backUpFolder=/home/app/tss/dev/admin/db/backup/
# 备份数据库名
username="root"
# 备份数据库密码
password="password"
# 备份目标数据库
db_name="backup_test"
#定义备份文件名
fileName="${db_name}_${date_now}.sql"
#定义备份文件目录
backUpFileName="${backUpFolder}/${fileName}"
echo "starting backup mysql ${db_name} at ${date_now}."
# 参数 —flush-logs：使用一个新的日志文件来记录接下来的日志
# 参数 —lock-all-tables：锁定所有数据库
/usr/bin/mysqldump -u${username} -p${password}  --lock-all-tables --flush-logs ${db_name} > ${backUpFileName}
#进入到备份文件目录
cd ${backUpFolder}
#压缩备份文件
tar zcvf ${fileName}.tar.gz ${fileName}

date_end=$(date "+%Y%m%d-%H%M%S")
echo "finish backup mysql database ${db_name} at ${date_end}."

大家只需修改其中的相关变量即可直接运行。

crontab

Crontab 是一个在 Unix 和 Linux 操作系统上用于定时执行任务的工具。它允许用户创建和管理计划任务，以便在特定的时间间隔或时间点自动运行命令或脚本。

首先了解下crontab命令的示例，输入命令：

cat /etc/crontab

输入命令：

crontab -e

打开一个空白文件，在该文件中输入定时任务命令即可。比如：

# 每分钟执行一次备份脚本
* * * * * sh /usr/your/path/mysqlBackup.sh

# 每五分钟执行
*/5 * * * * sh /usr/your/path/mysqlBackup.sh

# 每小时执行
0 * * * * sh /usr/your/path/mysqlBackup.sh

# 每天执行
0 0 * * * sh /usr/your/path/mysqlBackup.sh

# 每周执行
0 0 * * 0 sh /usr/your/path/mysqlBackup.sh

# 每月1号执行
0 0 1 * * sh /usr/your/path/mysqlBackup.sh

# 每年1月1号执行
0 0 1 1 * sh /usr/your/path/mysqlBackup.sh

按照我个人的业务需求，我将全量备份定义为一周执行一次，具体时间设置为每周日的0点，于是命令如下：

0 0 * * 7 sh /usr/your/path/mysqlBackup.sh

如果需要删除或者修改定时任务，再次输入命令crontab -e编辑命令即可。

到这里，我们的定时全量备份就完成了。

增量备份

通常我们的业务数据会越来越多，如果每次都保持比较高频的全量同步，势必会影响到业务的正常运行（持续时间比较久、会锁数据、大量冗余数据等问题）。增量备份可以一定程度上解决上述问题。

增量备份不存在冗余数据，不停的追加数据库的变更内容，同时效率更高。

Mysql的增量备份原理就是使用binlog日志，其保存了所有更新或者可能更新数据库的操作。binlog日志在启动MySQL服务器开启后开始记录，并在文件达到 max_binlog_size 所设置的大小或者接收到 flush logs 命令后重新创建的日志文件。只需要定时执行flush logs 方法重新创建新的日志，生成二进制的文件序列，并及时把这些日志保存起来到安全的地方就完成了一个时间段的增量备份.。

开启binlog

要设置增量备份，首先需要开启Mysql的log_bin配置。进入mysql命令行输入命令（或者使用navicat工具）：

show variables like '%log_bin%';

查看log_bin是否开启：

如果是OFF状态，则需要修改配置并保存。

如果不知道mysql的配置文件在什么位置，按以下步骤查询：

# 查询安装位置
which mysql

# 查询mysql 配置文件加载顺序
/usr/bin/mysql --verbose --help | grep -A 1 'Default options'

现在我们直接编辑第一个配置文件，增加两个配置：

注：如果要设置定时删除binlog，可以通过增加配置行实现：

#设置binlog清理时间
expire_logs_days = 7

然后重启Mysql即可。通过命令show variables like '%log_bin%';可以看到，现在binlog已开启。

增量备份脚本

#!/bin/bash
# 在使用之前，请提前创建以下各个目录
backupDir=/home/app/tss/dev/admin/db/backup/daily
# 增量备份时复制mysql-bin.00000*的目标目录，提前手动创建这个目录
mysqlDir=/var/lib/mysql
# 设置mysql增量备份的日志文件保存路径，没这个文件夹需要主动新建
logFile=/var/lib/mysql/backup/bak.log
#mysql的index文件路径，放在数据目录下的
BinFile=/var/lib/mysql/mysql-bin.index

# 产生新的mysql-bin.00000*文件
mysqladmin -uroot -pPassword flush-logs

# wc -l 统计行数
# awk 简单来说awk就是把文件逐行的读入，以空格为默认分隔符将每行切片，切开的部分再进行各种分析处理。
Counter=`wc -l $BinFile |awk '{print $1}'`
NextNum=0
#这个for循环用于比对$Counter,$NextNum这两个值来确定文件是不是存在或最新的
for file in `cat $BinFile`
do
    base=`basename $file`
    echo $base
    #basename用于截取mysql-bin.00000*文件名，去掉./mysql-bin.000005前面的./
    NextNum=`expr $NextNum + 1`
    if [ $NextNum -eq $Counter ]
    then
        echo $base skip! >> $logFile
    else
        dest=$backupDir/$base
        if(test -e $dest)
        #test -e用于检测目标文件是否存在，存在就写exist!到$logFile去
        then
            echo $base exist! >> $logFile
        else
            cp $mysqlDir/$base $backupDir
            echo $base copying >> $logFile
         fi
     fi
done
echo `date +"%Y年%m月%d日 %H:%M:%S"` $Next Bakup succ! >> $logFile

同样，我们将增量备份也设置相应的定时任务即可:

# 打开crontab并编辑
crontab -e

# 添加配置并保存，每天凌晨2点执行一次
0 2 * * * sh /usr/your/path/mysqlBackup.sh

至此，我们的数据库备份就完成了，欢迎大家留言交流。