编写脚本，使用mysqldump实现分库分表备份

分库备份：只备份用户的数据库，不备份系统原有的库。

首先需要具备MySQL环境

#!/bin/bash

#定义变量
mysql_cmd='-uroot -p123456'
exclude_db='information_schema|performance_schema|sys|mysql'
bak_path=/backup/db
[ -d ${bak_path} ] || mkdir -p ${bak_path}
#过滤备份的数据库，并过滤到指定目录
mysql ${mysql_cmd} -e 'show databases' -N | egrep -v ${exclude_db} > dbname
#循环读取
while read line
do
        mysqldump ${mysql_cmd} --set-gtid-purged=OFF -B $line | gzip > ${bak_path}/${line}_$(date +%F).sql.gz


done < dbname
#删除临时变量
rm -f dbname

检查语法问题，并运行脚本，查看结果。

 分表备份，只备份school库中的表

#!/bin/bash

mysql_cmd='-uroot -p123456'
exclude_db='information_schema|performance_schema|sys|mysql'
bak_path=/backup/db

mysql -uroot -p123456 -N -e "show tables from school" > tbname
while read tb
do
        [ -d ${bak_path}/school ] || mkdir -p ${bak_path}/school
        mysqldump ${mysql_cmd} --set-gtid-purged=OFF school $line | gzip > ${bak_path}/school/school_${line}_$(date +%F).sql.gz


done < tbname

rm -f tbname

 分库分表备份

#!/bin/bash

mysql_cmd='-uroot -p123456'
exclude_db='information_schema|performance_schema|sys|mysql'
bak_path=/backup/db

mysql ${mysql_cmd} -e 'show databases' -N | egrep -v "${exclude_db}" > dbname

while read line
do
        [ -d ${bak_path}/$line ] || mkdir -p ${bak_path}/$line
        mysqldump ${mysql_cmd} --set-gtid-purged=OFF -B $line | gzip >  ${bak_path}/${line}/${line}_$(date +%F).sql.gz
        mysql -uroot -p123456 -N -e "show tables from $line" > tbname

        while read tb
        do
                mysqldump ${mysql_cmd} --set-gtid-purged=OFF  $line $tb | gzip > ${bak_path}/$line/${line}_${tb}_$(date +%F).sql.gz

        done < tbname

done < dbname

rm -f dbname tbname

检查语法问题，并运行脚本

 

查看