数据库分库分表备份脚本

#!/bin/bash
#引用函数
[ -f /etc/init.d/functions ] && source /etc/init.d/functions
#定义变量
Date=$(date +%F)
DB_User=root
DB_Pass=123456
#编写循环
for DB_Name in $(mysql -u$DB_User -p$DB_Pass  -e "show databases;" | sed 1d | grep -v '.*_schema')
do
    #准备备份目录
    DB_Dir=/data/backup/$DB_Name
    [ -d $DB_Dir ] || mkdir -p $DB_Dir
    #备份数据库
    mysqldump -u$DB_User -p$DB_Pass --single-transaction -R -B $DB_Name  >$DB_Dir/${Date}_${DB_Name}.sql 
    if [ $? -eq 0 ];then
        action "数据库${DB_Name}备份成功！" /bin/true
    else
        action "数据库${DB_Name}备份失败！" /bin/false
    fi
    #备份数据库中的表
    for Table_Name in $(mysql -u$DB_User -p$DB_Pass  -e "use $DB_Name;show tables;" | sed 1d)
    do
        #备份表
        mysqldump -u$DB_User -p$DB_Pass --single-transaction -R $DB_Name $Table_Name >$DB_Dir/${Date}_${DB_Name}_${Table_Name}.sql
        if [ $? -eq 0 ];then
            action "数据库${DB_Name}中的${Table_Name}表备份成功！" /bin/true
        else
            action "数据库${DB_Name}中的${Table_Name}表备份失败！" /bin/false
        fi
    done
done