后台开发以及运维的同学，对于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/" ${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脚本，该脚本稍作修改就可以直接用于生产环境，具体的备份策略，比如多久备份一次，备份要保留多长时间等 根据实际情况自行调整即可