Shell实现数据库分库分表备份

使用for循环实现数据库的分库分表备份

#!/bin/sh
DB_User=root
DB_passwd=123456
Backup_path=/server/scripts/mysql
Date=$(date +%F)
DB_list=$(mysql -u$DB_User -p$DB_passwd -e "show databases;"|sed 1d|grep -v '.*schema')
[ -d $Backup_path ] || mkdir -p $Backup_path
for database in $DB_list
do
mysqldump -u$DB_User -p$DB_passwd --single-transaction -B $database >${Backup_path}/${database}_${Date}.sql
if [ $? -eq 0 ];then
echo "数据库$database 备份成功!"
DB_tables=$(mysql -u$DB_User -p$DB_passwd -e "use $database;show tables;"|sed 1d)
for table in $DB_tables
do
mysqldump -u$DB_User -p$DB_passwd --single-transaction $database $table >${Backup_path}/${database}_${table}_${Date}.sql
if [ $? -eq 0 ];then
echo "数据库$database 中的$table 表备份完成 ^_^"
else
echo "数据库$database 中的$table 表备份失败 ^~^"
fi
done
else
echo "据库$database 备份失败!!"
fi
done