一个自动备份MySQL的脚本

后台开发以及运维的同学，对于MySQL备份肯定不会陌生了，对于线上的数据库定时备份是必须要有的，而且这个过程一般都是自动的，本文将介绍一个shell脚本，它的功能是自动备份MySQL以及自动清除过期备份数据

备份脚本

下面就是自动备份 MySQL 的脚本的内容

#db用户名
dbuser=root
#db密码
dbpasswd="123456"
#ip地址
dbip=127.0.0.1
#备份的数据库名字前缀
pre_name="test"


#备份操作的日志文件
bakfile=/data/sqlbak/log.txt
#备份数据的目录
bakdatadir=/data/sqlbak/bakdata
#备份数据的目录-持久化备份
persist_path=/data/sqlbak/persistdata

#备份保存时间,单位: 天
dateoutday=15


#备份单个数据库
bak_single()
{
        #db name
        sdb=$1

        #日期字符串
        datestr=$2

        echo "bak_single ${sdb}_${datestr}.sql.gz BEGIN..." >> ${bakfile}
        #备份
        mysqldump -u${dbuser} -h ${dbip} -p${dbpasswd} --single-transaction  --no-create-db -R -C -B ${sdb} > ${sdb}_${datestr}.sql
        #注释掉sql脚本中 USE DATABASE 语句
        sed -i "s/USE/-- USE/" ${sdb}_${datestr}.sql
        #压缩
        gzip ${sdb}_${datestr}.sql
        #记录日志
        echo "bak_single ${sdb}_${datestr}.sql.gz  COMPLETE..." >> ${bakfile}
}

#备份所有数据库
bak_all()
{
        #日期字符串
        all_datestr=$(date "+%Y_%m_%d_%H_%M_%S")
        #
        date_dir=$(date "+%Y%m%d")
        #
        echo "bak_all begin ${all_datestr} ====================" >> ${bakfile}
        #所有的数据库
        alldb=`mysql -u${dbuser} -h ${dbip} -p${dbpasswd} -e "show databases"`
        for dbname in ${alldb}; do
               {
                #只备份指定前缀的数据库
                if [[ ${dbname} =~ ${pre_name} ]]; then
                       bak_single ${dbname} ${all_datestr} 
                fi
              }&
        done
           
        #等待所有数据库备份完成 
        wait

        #压缩
        tar czvf dbbak_${all_datestr}.tar *_${all_datestr}.sql.gz
        #删除
        rm *_${all_datestr}.sql.gz
        #是否存在当天日期命名的目录
        if [ ! -d ${bakdatadir}/${date_dir} ]; then
                mkdir -p ${bakdatadir}/${date_dir}
        fi
        #移动到当天日期命名的目录中
        mv dbbak_${all_datestr}.tar ${bakdatadir}/${date_dir}
        #
        echo "bak_all finish dbbak_${all_datestr}.tar ====================" >> ${bakfile}
}

#备份所有数据库-不会定时删除
bak_all_persist()
{
        #日期字符串
        all_datestr=$(date "+%Y_%m_%d_%H_%M_%S")
        #
        echo "bak_all_persist begin ${all_datestr} ====================" >> ${bakfile}
        #所有的数据库
        alldb=`mysql -u${dbuser} -h ${dbip} -p${dbpasswd} -e "show databases"`
        for dbname in ${alldb}; do
               {
                #只备份指定前缀的数据库
                if [[ ${dbname} == ${pre_name} ]]; then
                     bak_single ${dbname} ${all_datestr}
                fi
              }&
        done
           
        #等待所有数据库备份完成
        wait

        #压缩
        tar czvf dbpersistbak_${all_datestr}.tar *_${all_datestr}.sql.gz
        #删除
        rm *_${all_datestr}.sql.gz
        #是否存在当天日期命名的目录
        if [ ! -d ${persist_path} ]; then
                mkdir -p ${persist_path}
        fi
        #移动持久化备份的目录中
        mv dbpersistbak_${all_datestr}.tar ${persist_path}
        #
        echo "bak_all_persist finish dbbak_${all_datestr}.tar ====================" >> ${bakfile}
}

#检查过期备份
check_date_out()
{
        #当前目录
        curpath=`pwd`
        #当前日期
        curdate=$(date "+%Y%m%d")
        #最早的保存日期
        lastdate=`date -d "${curdate} - ${dateoutday} day" +%Y%m%d`
        #进入备份目录
        cd ${bakdatadir}
        #目录列表
        pathlst=`ls`
        #检查目录是否过期，删除已过期的目录
        for tmpdate in ${pathlst[*]}; do
                if [[ ${tmpdate} -le ${lastdate} ]]; then
                        rm -rf ${tmpdate}
                        echo "check_date_out, curdate:${curdate} delete ${tmpdate} " >> ${bakfile}
                fi
        done

        #回到当前目录
        cd ${curpath}
}

case "$1" in
   s)
        bak_single $2 $3
        ;;
   a)
        bak_all
        ;;
   p)
        bak_all_persist
        ;;
  chk) 
        check_date_out
        ;;
   *)
    echo "Please use correct command..."
        ;;
esac

函数功能

备份是以数据库为单位进行备份的，先备份单个数据库，然后再把所有的备份数据库打包一起

bak_single函数 表示备份单个数据，传入参数是需要备份的数据库名字和日期字符串，备份文件名由这两个参数构成，也就是说，备份的文件名由 数据库名 + 日期 组成，比如：test1_2021_08_16_10_05_30.sql表示 test1数据库的备份文件，备份的时间是 2021_08_16_10_05_30

bak_all函数 表示备份所有的数据库，不需要传入参数，先执行SQL语句 show databases查询出所有的数据库，然后过滤出我们需要备份的数据库，脚本中需要备份的数据库名都是以 test开头的，具体的过滤规则可以按照各自的需求自行修改

bak_all函数 for 循环体中 { 和 } 以及它们后面的 & 表示启动一个新进程并执行大括号中间的命令，也即每个数据库启动一个进程进行备份，for 循环结束之后的 wait 命令表示等待for循环中所有进程结束，也就是等待所有数据库备份全部完成之后，才会执行 wait 后面的命令

全部备份完成之后，会创建一个以当前日期命名的目录，并打包所有备份的数据库的SQL脚本，放到此目录中，同时删除原始的备份文件

这里采用的是分库备份，分库备份的好处是：如果所有库都备份成一个备份文件时，恢复其中一个库的数据是比较麻烦的，所以采用分库备份，利于恢复单库数据

check_date_out函数是检查备份目录是否过期，如果过期的话，直接删除过期的目录，脚本开头的变量 dateoutday指定了备份保留的天数

bak_all_persist函数是持久备份，备份过程和 bak_all函数一样，只不过这里是备份到另一个持久数据的目录中，脚本开头的变量 persist_path指定了持久备份目录，目录里面的备份文件不会自动删除，需要手工去删除

持久化目录的主要应用场景：有时线上数据库表有数据校正或者表格结构有变动，为了防止误操作，再执行操作之前，先调用 bak_all_persist函数备份下数据库，这样，即使出现误操作，还能恢复数据

备份参数说明

• –single-transaction

此选项会将隔离级别设置为可重复读 ( REPEATABLE READ ), 让整个数据在dump过程中保证数据的一致性，且不会锁表，这个选项对导出 InnoDB 的数据表很有用

• –no-create-db

正常导出的SQL脚本中会有类似 CREATE DATABASE语句，加了 --no-create-db选项之后就没有此语句了

• -C

服务器传给客户端的过程中先压缩再传递

• -R

导出存储过程以及自定义函数

在 mysqldump 导出数据库SQL脚本之后，sed -i “s/USE/-- USE/” KaTeX parse error: Expected group after '_' at position 6: {sdb}_̲{datestr}.sql 命令的作用是注释掉SQL脚本中的 USE DATABASE XXX 语句，这个也比较实用的，有时候线上数据会导入到内网，重现线上的一些BUG，但是内网可能已经有一个同名的数据库了，如果注释了这行语句，就可以导入到其他数据库，否则，需要先手工处理SQL脚本，然后再导入

如何使用

假如备份MySQL脚本的名字是 bak.sh, 下面是脚本的使用方法

• 备份对单个数据库

备份 logindb数据库

./bak.sh logindb "2021_08_16_10_05_30"

执行上述命令后，会在当前目录下生成名为 logindb_2021_08_16_10_05_30.sql.gz的文件

• 备份所有数据库

./bak.sh a

• 检查备份保留时间

./bak.sh chk

• 持久化备份

./bak.sh p

添加定时任务

要实现自动备份功能，还需要添加定时任务，间隔指定时间调用备份脚本，执行 ctrontab -e命令，输入以下语句

*/10 * * * * /data/sqlbak/bak.sh a
*/15 * * * * /data/sqlbak/bak.sh chk

上述定时任务是 每10分钟备份一次所有数据库，每 15 分钟检查一次过期的备份，当然，具体的备份策略根据具体的场景不同，可以根据实际情况调整

小结

本文提供了一个自动备份MySQL的shell脚本，该脚本稍作修改就可以直接用于生产环境，具体的备份策略，比如多久备份一次，备份要保留多长时间等 根据实际情况自行调整即可