mydumper备份数据库脚本

#!/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  </pre>