mydumper备份数据库脚本

2018-07-20    来源:open-open

容器云强势上线!快速搭建集群,上万Linux镜像随意使用
    #!/bin/bash  
    #ocpyang@126.com  
    ########脚本说明  
    #1.默认读取当前目录下database.txt文件,备份部分数据库  
    #2.如果database.txt为空,则执行全备或备份指定的个别数据库  
      
      
    #set parameter   
    mydumper=/usr/local/bin/mydumper  #根据自己的实际情况设置  
    dir_backup=/backup  #根据自己的实际情况设置  
    mysql_host=localhost  #根据自己的实际情况设置  
    mysql_port=33306  #根据自己的实际情况设置  
    mysql_user=root  #根据自己的实际情况设置  
    mysql_pass=123456  #根据自己的实际情况设置  
      
    now_date=`date +%Y%m%d%H%M`  
      
      
    if [ ! -d $dir_backup ]; then  
      echo -e "\e[1;31m 保存备份的主目录:$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.  
        # 参数 $1 = message  
        # 参数 $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.  
        # 参数1 $1 = message  
        # 参数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}个数据库:"  
        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}个数据库$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成功将数据库$i备份到:${db_dpname}. \e[0m"  
                    echo   
                    else  
                    echo -e "\e[1;31m 备份异常结束. \e[0m"  
                    fi  
              done  
    else  
      
            ipname=''  
            read -p "Please input you want to backup database name[a|A:ALL]:" ipname  
      
            #开始循环  
               
            #开始时间  
            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}个数据库:"  
             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}个数据库$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成功将数据库$i备份到:${db_dpname}. \e[0m"  
                    echo   
                    else  
                    echo -e "\e[1;31m 备份异常结束. \e[0m"  
                    fi  
              done  
            else   
                echo "此次备份的数据库名为:$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成功将数据库备份到:${db_dpname}. \e[0m"  
                    else  
                    echo -e "\e[1;31m 备份异常结束. \e[0m"  
                    fi  
                      
                      
                fi  
      
      
            # 循环结束  
            fi  
      
    fi  
      
    rm -rf ${judegedate_01}  
    rm -rf ${judegedate_02}  
    rm -rf ${filepath}/tmpdatabases.txt  
      
    echo "完成于: `date +%F' '%T' '%w`"  
      
    ######################################################################################################################  
    执行结果如下:#####################  
    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个数据库:  
      
      
      mydumper开始备份第1个数据库information_schema.....   
      mydumper玩命备份中.....稍等片刻....   
      mydumper成功将数据库information_schema备份到:/backup/information_schema.201505141141.   
      
      
      mydumper开始备份第2个数据库mysql.....   
      mydumper玩命备份中.....稍等片刻....   
      mydumper成功将数据库mysql备份到:/backup/mysql.201505141141.   
      
      
      mydumper开始备份第3个数据库performance_schema.....   
      mydumper玩命备份中.....稍等片刻....   
      mydumper成功将数据库performance_schema备份到:/backup/performance_schema.201505141141.   
      
      
      mydumper开始备份第4个数据库test.....   
      mydumper玩命备份中.....稍等片刻....   
      mydumper成功将数据库test备份到:/backup/test.201505141141.   
      
      
      mydumper开始备份第5个数据库wind.....   
      mydumper玩命备份中.....稍等片刻....   
      mydumper成功将数据库wind备份到:/backup/wind.201505141141.   
      
      
      mydumper开始备份第6个数据库mpiao.....   
      mydumper玩命备份中.....稍等片刻....   
      mydumper成功将数据库mpiao备份到:/backup/mpiao.201505141141.   
      
      
      mydumper开始备份第7个数据库mpadmin.....   
      mydumper玩命备份中.....稍等片刻....   
      mydumper成功将数据库mpadmin备份到:/backup/mpadmin.201505141141.   
      
      
      mydumper开始备份第8个数据库mplog.....   
      mydumper玩命备份中.....稍等片刻....   
      mydumper成功将数据库mplog备份到:/backup/mplog.201505141141.   
      
      
    完成于: 2015-05-14 11:42:05 4  

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

标签: Mysql 脚本 数据库

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点!
本站所提供的图片等素材,版权归原作者所有,如需使用,请与原作者联系。

上一篇:php图形图像处理基础

下一篇:HttpClient通过GET和POST获取网页内容