#!/bin/bash  
    #ocpyang@126.com  
    ########腳本說明  
    #1.默認讀取當前目錄下database.txt文件,備份部分數(shù)據(jù)庫  
    #2.如果database.txt為空,則執(zhí)行全備或備份指定的個別數(shù)據(jù)庫  
      
      
    #set parameter   
    mydumper=/usr/local/bin/mydumper  #根據(jù)自己的實際情況設置  
    dir_backup=/backup  #根據(jù)自己的實際情況設置  
    mysql_host=localhost  #根據(jù)自己的實際情況設置  
    mysql_port=33306  #根據(jù)自己的實際情況設置  
    mysql_user=root  #根據(jù)自己的實際情況設置  
    mysql_pass=123456  #根據(jù)自己的實際情況設置  
      
    now_date=`date +%Y%m%d%H%M`  
      
      
    if [ ! -d $dir_backup ]; then  
      echo -e "\e[1;31m 保存?zhèn)浞莸闹髂夸?$dir_backup不存在,將自動新建. \e[0m"  
      mkdir -p ${dir_backup}  
    fi  
      
      
    export black='\033[0m'  
    export boldblack='\033[1;0m'  
    export red='\033[31m'  
    export boldred='\033[1;31m'  
    export green='\033[32m'  
    export boldgreen='\033[1;32m'  
    export yellow='\033[33m'  
    export boldyellow='\033[1;33m'  
    export blue='\033[34m'  
    export boldblue='\033[1;34m'  
    export magenta='\033[35m'  
    export boldmagenta='\033[1;35m'  
    export cyan='\033[36m'  
    export boldcyan='\033[1;36m'  
    export white='\033[37m'  
    export boldwhite='\033[1;37m'  
      
      
    cecho ()  
      
    ## -- Function to easliy print colored text -- ##  
          
        # Color-echo.  
        # 參數(shù) $1 = message  
        # 參數(shù) $2 = color  
    {  
    local default_msg="No message passed."  
      
    message=${1:-$default_msg}  # 如果$1沒有輸入則為默認值default_msg.  
    color=${2:-black}       # 如果$1沒有輸入則為默認值black.  
      
    case $color in  
        black)  
             printf "$black" ;;  
        boldblack)  
             printf "$boldblack" ;;  
        red)  
             printf "$red" ;;  
        boldred)  
             printf "$boldred" ;;  
        green)  
             printf "$green" ;;  
        boldgreen)  
             printf "$boldgreen" ;;  
        yellow)  
             printf "$yellow" ;;  
        boldyellow)  
             printf "$boldyellow" ;;  
        blue)  
             printf "$blue" ;;  
        boldblue)  
             printf "$boldblue" ;;  
        magenta)  
             printf "$magenta" ;;  
        boldmagenta)  
             printf "$boldmagenta" ;;  
        cyan)  
             printf "$cyan" ;;  
        boldcyan)  
             printf "$boldcyan" ;;  
        white)  
             printf "$white" ;;  
        boldwhite)  
             printf "$boldwhite" ;;  
    esac  
      printf "%s\n"  "$message"  
      tput sgr0         # tput sgr0即恢復默認值  
      printf "$black"  
      
    return  
    }  
      
      
    cechon ()         
      
        # Color-echo.  
        # 參數(shù)1 $1 = message  
        # 參數(shù)2 $2 = color  
    {  
    local default_msg="No message passed."  
                    # Doesn't really need to be a local variable.  
      
    message=${1:-$default_msg}  # 如果$1沒有輸入則為默認值default_msg.  
    color=${2:-black}       # 如果$1沒有輸入則為默認值black.  
      
    case $color in  
        black)  
            printf "$black" ;;  
        boldblack)  
            printf "$boldblack" ;;  
        red)  
            printf "$red" ;;  
        boldred)  
            printf "$boldred" ;;  
        green)  
            printf "$green" ;;  
        boldgreen)  
            printf "$boldgreen" ;;  
        yellow)  
            printf "$yellow" ;;  
        boldyellow)  
            printf "$boldyellow" ;;  
        blue)  
            printf "$blue" ;;  
        boldblue)  
            printf "$boldblue" ;;  
        magenta)  
            printf "$magenta" ;;  
        boldmagenta)  
            printf "$boldmagenta" ;;  
        cyan)  
            printf "$cyan" ;;  
        boldcyan)  
            printf "$boldcyan" ;;  
        white)  
            printf "$white" ;;  
        boldwhite)  
            printf "$boldwhite" ;;  
    esac  
      printf "%s"  "$message"  
      tput sgr0         # tput sgr0即恢復默認值  
      printf "$black"  
      
    return  
    }  
      
      
      
    judegedate_01="judegedate01.`date +%y%m%d%h%m%s`.txt"  
    schema_judege01="select schema_name from information_schema.schemata ;"  
    mysql -h${mysql_host} -P${mysql_port}  -u${mysql_user} -p${mysql_pass} -e"${schema_judege01}" >${judegedate_01}  
    echo -e "\e[1;31m The databases name in current instance is: \e[0m"  
    awk 'NR==2,NR==0 { print $1}'  ${judegedate_01}  
    echo "                          "  
      
      
    #Get the current script path and create a file named database.txt   
    #in order to save the  backup  databases name.  
    filepath=$(cd "$(dirname "$0")"; pwd)  
    if [ ! -s "${filepath}/database.txt" ];then   
        echo "將在當前目錄下新建databases.txt."  
        touch ${filepath}/database.txt  
        echo "#Each line is stored a valid database name">${filepath}/database.txt  
        chmod 700 ${filepath}/database.txt  
      
    fi  
      
    #Remove the comment line  
    awk 'NR==2,NR==0 { print $1}' ${filepath}/database.txt> ${filepath}/tmpdatabases.txt  
      
      
      
    #To determine whether a file is empty  
    if [ -s ${filepath}/tmpdatabases.txt ];then  
        #開始時間  
        started_time=`date +%s`  
        echo "備份開始時間:${started_time}"  
        db_num00=`awk 'NR==1,NR==0 { print NR}' ${filepath}/tmpdatabases.txt |tail -n1`  
        echo "此次將備份${db_num00}個數(shù)據(jù)庫:"  
        echo  
      
        or_dbnum=0  
              for i in  `awk 'NR==1,NR==0 { print $1}'  ${filepath}/tmpdatabases.txt`;  
              do      
                ((or_dbnum+=1))  
                mysql_databases=$i  
                db_dpname=$dir_backup/${i}.${now_date}  
                  echo -e "\e[1;32m  mydumper開始備份第${or_dbnum}個數(shù)據(jù)庫$i..... \e[0m"  
                    sleep 2  
                    echo -e "\e[1;32m  mydumper玩命備份中.....稍等片刻.... \e[0m"  
                    ${mydumper} \  
                    --database=${mysql_databases} \  
                    --host=${mysql_host} \  
                    --port=${mysql_port} \  
                    --user=${mysql_user} \  
                    --password=${mysql_pass} \  
                    --outputdir=${db_dpname} \  
                    --no-schemas \  
                    --rows=50000 \  
                    --build-empty-files \  
                    --threads=4 \  
                    --compress-protocol \  
                    --kill-long-queries   
                    if [ "$?" -eq 0 ];then  
                    echo -e "\e[1;32m  mydumper成功將數(shù)據(jù)庫$i備份到:${db_dpname}. \e[0m"  
                    echo   
                    else  
                    echo -e "\e[1;31m 備份異常結(jié)束. \e[0m"  
                    fi  
              done  
    else  
      
            ipname=''  
            read -p "Please input you want to backup database name[a|A:ALL]:" ipname  
      
            #開始循環(huán)  
               
            #開始時間  
            started_time=`date +%s`  
            echo "備份開始時間:${started_time}"  
            if [ "$ipname" = "a" -o "$ipname" = "A" ];then  
             db_num=`awk 'NR==2,NR==0 { print NR-1}' ${judegedate_01} |tail -n1`  
             echo "此次將備份${db_num}個數(shù)據(jù)庫:"  
             echo   
             mysql_databases=$ipname  
             or_dbnum=0  
              for i in  `awk 'NR==2,NR==0 { print $1}'  ${judegedate_01}`;  
              do      
                 ((or_dbnum+=1))  
                mysql_databases=$i  
                db_dpname=$dir_backup/${i}.${now_date}  
                  echo -e "\e[1;32m  mydumper開始備份第${or_dbnum}個數(shù)據(jù)庫$i..... \e[0m"  
                    sleep 2  
                    echo -e "\e[1;32m  mydumper玩命備份中.....稍等片刻.... \e[0m"  
                    ${mydumper} \  
                    --database=${mysql_databases} \  
                    --host=${mysql_host} \  
                    --port=${mysql_port} \  
                    --user=${mysql_user} \  
                    --password=${mysql_pass} \  
                    --outputdir=${db_dpname} \  
                    --no-schemas \  
                    --rows=50000 \  
                    --build-empty-files \  
                    --threads=4 \  
                    --compress-protocol \  
                    --kill-long-queries   
                    if [ "$?" -eq 0 ];then  
                    echo -e "\e[1;32m  mydumper成功將數(shù)據(jù)庫$i備份到:${db_dpname}. \e[0m"  
                    echo   
                    else  
                    echo -e "\e[1;31m 備份異常結(jié)束. \e[0m"  
                    fi  
              done  
            else   
                echo "此次備份的數(shù)據(jù)庫名為:$ipname"  
                echo    
                #開始時間  
                STARTED_TIME=`date +%s`  
      
                mysql_databases=$ipname  
                db_dpname=$dir_backup/${mysql_databases}.${now_date}  
      
                judegedate_02="judegedate02.`date +%y%m%d%h%m%s`.txt"  
                schema_judege02="select schema_name from information_schema.schemata where schema_name='${ipname}';"  
                mysql -h${mysql_host} -P${mysql_port}  -u${mysql_user} -p${mysql_pass} -e"${schema_judege02}" >${judegedate_02}  
      
                if [ ! -s "${judegedate_02}" ];then  
                    echo "                                                                           "  
                      
                    echo -e "\e[1;31m  ******************************************************************* \e[0m"  
                    echo -e "\e[1;31m  !o(︶︿︶)o! The  schema_name ${ipname} not exits,pleae check . ~~~~(>_<)~~~~  \e[0m"  
                    echo -e "\e[1;31m  ********************************************************************** \e[0m"  
      
      
                    echo "                                                                           "  
                    rm -rf ${judegedate_01}  
                    rm -rf ${judegedate_02}  
                    exit 0  
                else  
                    echo -e "\e[1;32m  mydumper開始備份請稍等..... \e[0m"  
                    sleep 2  
                    echo -e "\e[1;32m  mydumper玩命備份中.....稍等片刻.... \e[0m"  
                    ${mydumper} \  
                    --database=${mysql_databases} \  
                    --host=${mysql_host} \  
                    --port=${mysql_port} \  
                    --user=${mysql_user} \  
                    --password=${mysql_pass} \  
                    --outputdir=${db_dpname} \  
                    --no-schemas \  
                    --rows=50000 \  
                    --build-empty-files \  
                    --threads=4 \  
                    --compress-protocol \  
                    --kill-long-queries   
                    if [ "$?" -eq 0 ];then  
                    echo -e "\e[1;32m  mydumper成功將數(shù)據(jù)庫備份到:${db_dpname}. \e[0m"  
                    else  
                    echo -e "\e[1;31m 備份異常結(jié)束. \e[0m"  
                    fi  
                      
                      
                fi  
      
      
            # 循環(huán)結(jié)束  
            fi  
      
    fi  
      
    rm -rf ${judegedate_01}  
    rm -rf ${judegedate_02}  
    rm -rf ${filepath}/tmpdatabases.txt  
      
    echo "完成于: `date +%F' '%T' '%w`"  
      
    ######################################################################################################################  
    執(zhí)行結(jié)果如下:#####################  
    The databases name in current instance is:   
    information_schema  
    mysql  
    performance_schema  
    test  
    wind  
    mpiao  
    mpadmin  
    mplog  
    Please input you want to backup database name[a|A:ALL]:A  
    備份開始時間:1431574874  
    此次將備份8個數(shù)據(jù)庫:  
      
      
      mydumper開始備份第1個數(shù)據(jù)庫information_schema.....   
      mydumper玩命備份中.....稍等片刻....   
      mydumper成功將數(shù)據(jù)庫information_schema備份到:/backup/information_schema.201505141141.   
      
      
      mydumper開始備份第2個數(shù)據(jù)庫mysql.....   
      mydumper玩命備份中.....稍等片刻....   
      mydumper成功將數(shù)據(jù)庫mysql備份到:/backup/mysql.201505141141.   
      
      
      mydumper開始備份第3個數(shù)據(jù)庫performance_schema.....   
      mydumper玩命備份中.....稍等片刻....   
      mydumper成功將數(shù)據(jù)庫performance_schema備份到:/backup/performance_schema.201505141141.   
      
      
      mydumper開始備份第4個數(shù)據(jù)庫test.....   
      mydumper玩命備份中.....稍等片刻....   
      mydumper成功將數(shù)據(jù)庫test備份到:/backup/test.201505141141.   
      
      
      mydumper開始備份第5個數(shù)據(jù)庫wind.....   
      mydumper玩命備份中.....稍等片刻....   
      mydumper成功將數(shù)據(jù)庫wind備份到:/backup/wind.201505141141.   
      
      
      mydumper開始備份第6個數(shù)據(jù)庫mpiao.....   
      mydumper玩命備份中.....稍等片刻....   
      mydumper成功將數(shù)據(jù)庫mpiao備份到:/backup/mpiao.201505141141.   
      
      
      mydumper開始備份第7個數(shù)據(jù)庫mpadmin.....   
      mydumper玩命備份中.....稍等片刻....   
      mydumper成功將數(shù)據(jù)庫mpadmin備份到:/backup/mpadmin.201505141141.   
      
      
      mydumper開始備份第8個數(shù)據(jù)庫mplog.....   
      mydumper玩命備份中.....稍等片刻....   
      mydumper成功將數(shù)據(jù)庫mplog備份到:/backup/mplog.201505141141.   
      
      
    完成于: 2015-05-14 11:42:05 4  

來自：http://blog.csdn.net/yangzhawen/article/details/45718793

標簽： Mysql 腳本 數(shù)據(jù)庫

版權(quán)申明：本站文章部分自網(wǎng)絡，如有侵權(quán)，請聯(lián)系：west999com@outlook.com
特別注意：本站所有轉(zhuǎn)載文章言論不代表本站觀點！
本站所提供的圖片等素材，版權(quán)歸原作者所有，如需使用，請與原作者聯(lián)系。