利用mysqldump实现分库分表备份的shell脚本-Toy模板网

这篇具有很好参考价值的文章主要介绍了利用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