#!/bin/bash
#########################
#File name:db_fen.sh
#Version:v1.0
#Email:admin@test.com
#Created time:2023-07-29 09:18:52
#Description:
#########################
# MySQL連接信息
db_user="root"
db_password="RedHat@123"
db_cmd="-u${db_user} -p${db_password}"
exclude_db="information_schema|mysql|performance_schema|sys|Database"
bak_dir=/backup/db/mysqldump_test/
# 判斷備份路徑是否存在
[ -d ${bak_dir} ] || mkdir ${bak_dir}
# 查找出需要備份的數(shù)據(jù)庫
mysql ${db_cmd} -e"show databases" -N 2>/dev/null | egrep -v "$exclude_db" > dbtmp
# 循環(huán)遍歷數(shù)據(jù)庫列表
while read db
do
#對(duì)數(shù)據(jù)庫進(jìn)行備份
mysqldump ${db_cmd} --set-gtid-purged=off $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
#根據(jù)數(shù)據(jù)庫進(jìn)行分表備份
[ -d ${bak_dir}/$db ] || mkdir -p ${bak_dir}/$db
# 獲取數(shù)據(jù)庫中的表名列表
mysql ${db_cmd} $db -e "SHOW TABLES;" | grep -v "Tables_in" > tbtmp
# 循環(huán)遍歷表名列表
while read tb
do
# 對(duì)表進(jìn)行備份
mysqldump ${db_cmd} --set-gtid-purged=off $db $tb 2>/dev/null | gzip > ${bak_dir}/$db/`date +%F`-$db-$tb.sql.gz
# 判斷是否備份成功
if [ "$?" -eq 0 ]; then
echo "Backup of $db.$tb successful!"
else
echo "Backup of $db.$tb failed!"
fi
done < tbtmp
done < dbtmp
#刪除臨時(shí)文件
rm -rf tbtmp
rm -rf dbtmp
文章來源：http://www.zghlxwxcb.cn/news/detail-622706.html
?文章來源地址http://www.zghlxwxcb.cn/news/detail-622706.html
到了這里，關(guān)于數(shù)據(jù)庫的分庫分表的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
