mysql分库分表备份脚本

#!/bin/bash

USERNAME=root
PASSWD='rewrfefsadSa56'
PORT=30306
HOST=$1
MYSQLCMD="mysql -h $HOST -P$PORT -u$USERNAME -p$PASSWD"
DB_DUMP="mysqldump -h $HOST -P$PORT -u$USERNAME -p$PASSWD --single-transaction --source-data=2 --triggers --routines --events"
TARGET_DBS=(`$MYSQLCMD -e "show databases" |grep -Ev "Database|information_schema|mysql|performance_schema|sys"`)
DATE=$(date -d "8 hour"  +"%Y%m%d%H%M%S")
BACKDIR=$2
CHOICE_BACKDIR=/${BACKDIR}/$DATE

if [ ! -d $CHOICE_BACKDIR ];then
        mkdir -p $CHOICE_BACKDIR
fi

full_backup(){
        echo "开始全库备份" >>${CHOICE_BACKDIR}/backup.log
        $DB_DUMP -B ${TARGET_DBS[*]} |gzip >${CHOICE_BACKDIR}/full_backup.sql.gz
        if [ $? -eq 0 ];then
                echo "全库备份成功">>${CHOICE_BACKDIR}/backup.log
        else
                echo "全库备份失败">>${CHOICE_BACKDIR}/backup.log
                exit
        fi
}

dbs_backup(){
        echo "开始分库备份" >>${CHOICE_BACKDIR}/backup.log
        for db in ${TARGET_DBS[*]};do
            local dir=${CHOICE_BACKDIR}/$db
            if [ ! -d $dir ];then
                mkdir -p $dir 
            fi
                $DB_DUMP -B ${db} |gzip >${dir}/${db}.sql.gz 
            if [ $? -eq 0 ];then
                echo "${db}已经备份完成" >>${CHOICE_BACKDIR}/backup.log
            else
                echo "${db}备份失败" >>${CHOICE_BACKDIR}/backup.log
                exit 
                fi
                done
}

tables_backup(){
        echo "开始分表备份" >>${CHOICE_BACKDIR}/backup.log
    for db in ${TARGET_DBS[*]};do
        target_tables=(`$MYSQLCMD -e "show tables from $db;"|sed '1d'`)
        local dir="${CHOICE_BACKDIR}/${db}/tables"
        if [ ! -d $dir ];then
            mkdir -p $dir
        fi
        for table in ${target_tables[*]};do
            $DB_DUMP  $db $table |gzip > $dir/$table.sql.gz
            if [ $? -eq 0 ];then
                echo "${db}_${table}备份完成" >>${CHOICE_BACKDIR}/backup.log
            else
                echo "${db}_${table}备份失败" >>${CHOICE_BACKDIR}/backup.log
                exit 
            fi
        done
    done
}
mian(){
        full_backup
        dbs_backup
        tables_backup
}
mian