利用mysqldump实现分库分表备份的shell脚本

一、信息摘要

linux版本：CentOS 7.9

mysql版本：MySQL 5.7.36

脚本实现功能：利用mysqldump工具实现对mysql中的数据库分库备份，和对所备份数据库中的表分表备份

二、shell脚本

#!/bin/bash
#########################
#File name:mysqlbak.sh
#Version:v1.0
#Email:peng779016@163.com
#Created time:2023-07-26 14:21:05
#Description:
#########################

#定义变量
bak_user='root'
bak_password='123456'
bak_cmd="-u${bak_user} -p${bak_password}"
exclude_db="information_schema|mysql|performance_schema|sys"
bak_dir=/backup/db

#分库备份
[ -d ${bak_dir} ] || mkdir -p ${bak_dir}
mysql ${bak_cmd} -e "show databases" -N 2>/dev/null | egrep -v "${exclude_db}" > dbtmp
while read db
do
  mysqldump ${bak_cmd} --set-gtid-purged=off -B $db 2>/dev/null | gzip > ${bak_dir}/`date +%F`-$db.sql.gz
  if [ $? -eq 0 ]
  then
    echo "database $db is being backed up ... success!"
  else
    echo "database $db is being backed up ... failure!"
  fi
  #分表备份
  [ -d ${bak_dir}/$db ] || mkdir -p ${bak_dir}/$db
  mysql ${bak_cmd} -N -e "use $db;show tables" 2>/dev/null > tbtmp
  while read tb
  do
    mysqldump ${bak_cmd} --set-gtid-purged=off $db $tb 2>/dev/null | gzip > ${bak_dir}/$db/`date +%F`-$db-$tb.spl.gz
    if [ $? -eq 0 ]
    then
      echo "table $tb is being backed up ... success!"
    else
      echo "table $tb is being backed up ... failure!"
    fi
  done < tbtmp
done < dbtmp
#删除临时文件
rm -rf dbtmp
rm -rf tbtmp

三、脚本描述

1）定义变量

定义了备份用户、备份用户密码、备份参数命令（简化代码）、不需要备份的数据库、备份目录这些变量。

2）分库备份

1. 首先检查备份目录是否存在，不存在则创建。
2. 然后提取出将要备份的数据库名到临时文件dbtmp中。
3. while循环读取临时文件dbtmp中的行数据，用mysqldump命令压缩备份每个数据库并在备份目录下产生数据库备份文件。
4. 输出备份结果信息。

3）分表备份

1. 首先检查备份目录下是否存在以数据库为名的目录，不存在则创建。
2. 然后提取出每次循环数据库中的每个表名到临时文件tbtmp中。
3. 嵌套while循环读取临时文件tbtmp中的行数据，用mysqldump命令压缩备份每个表并在备份目录/数据库名/目录下生成表备份文件。
4. 输出备份结果信息。

4）删除临时文件

最后删除临时文件。

四、脚本功能测试

1）执行shell脚本，分库分表备份mysql数据库，执行结果如下：

 2）测试备份文件的可用性

数据库备份文件正常。

表备份文件正常。

该脚本功能正常可用。