多功能mysql备份shell

桃扇骨 2022-08-20 14:29 149阅读 0赞

**最新代码在https://github.com/crjfans/mysqlbackup**

\#!/bin/bash

\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\# script name: mysqlbackup.sh                                            \#  
\# function: use this script to make mysql backups                      \#  
\# useage: mysqlbackup.sh -a \[-e -d -e ...\]                               \#  
\# version: v0.1                                                        \#  
\# created date: 2016/1/6                                               \#  
\# author: JayZhou                                                      \#  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
  
\#set -e: if the execution result of then instruction is not equle 0 ,then exit automatically  
\#set -e  
\#set -u: if the variable to be used is not be define,you will get an error message.  
set -u  
PREFIX\_ERROR="ERROR"  
PREFIX\_WARNING="WARNING"  
PREFIX\_NOTICE="NOTICE"  
EXIT\_NORMAL=0  
EXIT\_ERROR=1  
CONFIG\_DIR=/etc  
CONFIG\_FILE=$\{CONFIG\_DIR\}/mysqlbackup.cfg  
TEMP\_FILE\_FLAG=temp  
CONFIG\_FILE\_TEMP=$\{CONFIG\_FILE\}.$\{TEMP\_FILE\_FLAG\}  
BACKUP\_START\_MSG='echo \[\`date +%Y%m%d-%H:%M:%S\`\]:START to backup...'  
BACKUP\_FINISH\_MSG='echo \[\`date +%Y%m%d-%H:%M:%S\`\]:FINISH backup!'  
TIME='date +%Y%m%d'  
  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\# routine name: usage\_notes  
\# function: to show how to use the script  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
function usage\_notes()  
\{  
echo "  Usage: \`basename $0\` \[OPTION\] \[DB1 DB2...\] \[TABLE1 TABLE2...\] \[...\]"  
echo "  Options:  
    -a           Generate backups of all databases.  
    -e           Generate backups of each database.  
    -d value\[s\]  Generate backups of single/multi database\[s\].  
                 Example: -d 'mydb1 mydb2'  
    -t value\[s\]  Generate backups of single/multi table\[s\] of single database.  
                 Example: -t 'mydb1 mytable1'  
    -b           Generate backups of binlog for each database.  
    -h           Generate backups of binlog use copy command.  
    -c value     Clean up dump backups beyond n(n must be integer) days ago.  
                 Example: -c 7  
    -f value     Clean up binlog backups beyond n(n must be integer) days ago.  
                 Example: -f 7  
    -g           Flush binary logs  
    -p           Create mysql password for dumping  
  Options must be given as -option \['value1 \[value2\] ...'\] or -option value, not -option=value.  
"  
exit 0  
\}  
  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\# routine name: exec\_msg  
\# function: to judge whether the command called executed successfully  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
function exec\_msg()  
\{  
    EXEC\_MSG\_STATUS=$1  
    EXEC\_MSG\_SUCCESS=\`echo $2 | awk -F"@" '\{print $1\}'\`  
    EXEC\_COMMAND\_SUCCESS=\`echo $2 | awk -F"@" '\{print $2\}'\`  
    EXEC\_MSG\_ERROR=\`echo $3 | awk -F"@" '\{print $1\}'\`  
    EXEC\_COMMAND\_ERROR=\`echo $3 | awk -F"@" '\{print $2\}'\`  
        if \[ $\{EXEC\_MSG\_STATUS\} -eq 0 \]; then  
            if \[ ! -z $\{EXEC\_MSG\_SUCCESS:0:1\} \]; then  
                    echo "$\{PREFIX\_NOTICE\}:$\{EXEC\_MSG\_SUCCESS\}"  
            if \[ ! -z $\{EXEC\_COMMAND\_SUCCESS:0:1\} \]; then  
                        eval $\{EXEC\_COMMAND\_SUCCESS\}  
            fi  
        fi  
        else  
            if \[ ! -z $\{EXEC\_MSG\_ERROR:0:1\} \]; then  
                    echo "$\{PREFIX\_ERROR\}:$\{EXEC\_MSG\_ERROR\}"  
            if \[ ! -z $\{EXEC\_COMMAND\_ERROR:0:1\} \]; then  
                        eval $\{EXEC\_COMMAND\_ERROR\}  
            fi  
        fi  
                exit $EXIT\_ERROR  
        fi  
\}  
  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\# routine name: create\_passwd  
\# function: create password file for dumping  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
function create\_passwd()  
\{  
    if \[ ! -d $DIR\_PASSWD \]; then  
        mkdir -p $DIR\_PASSWD  
    fi  
        echo -n "Please enter MySQL(user=root)'s password:"  
        read -s -a MYSQL\_PASSWD  
    echo ""  
    $CMD\_MYSQL -uroot -p$MYSQL\_PASSWD -N -s -e "select user()" >/dev/null 2>&1  
    exec\_msg "$?" "" "the password of root is wrong!"  
    echo "root $MYSQL\_PASSWD" |  $CMD\_OPENSSL aes-128-cbc -k $HOSTNAME -base64 > $FILE\_PASSWD && chmod 600 $FILE\_PASSWD  
    exec\_msg "$?" "create the password file successfully!" ""  
\}  
  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\# routine name: create\_config\_file  
\# function: create a template config file  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
function create\_config\_file()  
\{  
        \#initialize config file  
        if \[ ! -w $CONFIG\_DIR \]; then  
                echo "$PREFIX\_ERROR:create $CONFIG\_FILE fail,no privilege to write $CONFIG\_DIR!"  
                exit $EXIT\_ERROR  
        fi  
        echo "$PREFIX\_ERROR:Not found $\{CONFIG\_FILE\} ,create a template now..."  
        echo '\#mysql data directory  
DIR\_MYSQL="/var/lib/mysql"  
  
\#the directory where you want to save the mysql dumping files  
DIR\_BACKUP="/tmp/backupdb"  
  
\#the directory where you want to save the mysql binlog files  
DIR\_BACKUP\_BINLOG="$DIR\_BACKUP/binlog"  
  
\#the directory where you want to save the password of mysql  
DIR\_PASSWD="$DIR\_MYSQL/etc"  
  
\#the file you want to save the password of mysql  
FILE\_PASSWD="$DIR\_PASSWD/passwordfile"  
  
\#the file contain all the names of binlog files  
FILE\_BINLOG\_INDEX="$DIR\_MYSQL/mysql-bin.index"  
  
\#the location of the "mysqldump" command  
CMD\_MYSQLDUMP="/usr/bin/mysqldump"  
  
\#the location of the "mysqlbinlog" command  
CMD\_MYSQLBINLOG="/usr/bin/mysqlbinlog"  
  
\#the location of the "mysql" command  
CMD\_MYSQL="/usr/bin/mysql"  
  
\#the location of the "openssl" command used to encrypt password  
CMD\_OPENSSL="/usr/bin/openssl"  
  
\#the databases specified will be dumped  
LIST\_INCLUDE\_DB\_DUMP="(mydb1|mydb2|mydb3)"  
  
\#the binlog files backups of databases specified will be generated  
LIST\_INCLUDE\_DB\_BINLOG="(mydb1|mydb2|mydb3)"  
  
\#the socket file of mysql you want to connect  
SOCKET="/var/lib/mysql/mysql.sock"' > $CONFIG\_FILE\_TEMP  
        echo "$PREFIX\_NOTICE:create $CONFIG\_FILE\_TEMP successfully!"  
        echo "$PREFIX\_WARNING:you need to \[1\]change the file name from $CONFIG\_FILE\_TEMP to $CONFIG\_FILE \[2\]change the default values against your actual conditions!"  
        exit $EXIT\_NORMAL  
\}  
  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\# routine name: check\_init\_value  
\# function: check initial variables  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
function check\_init\_value()  
\{  
    \#check password file  
    if \[ ! -f $FILE\_PASSWD \]; then  
        echo "$PREFIX\_ERROR:Not found the password file,please create it by: $0 -p"  
        exit 0  
    fi  
    \#check backup dir  
    if \[ ! -d $DIR\_BACKUP \]; then  
        echo "$PREFIX\_WARNING:not found the backup directory,create it now..."  
        mkdir -p $DIR\_BACKUP  
        exec\_msg "$?" "create backup directory successfully!" ""  
    fi  
\}  
  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\# routine name: init\_var  
\# function: import variables in mysqlbackup.cnf  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
function init\_var()  
\{  
        if \[ -r $CONFIG\_FILE \]; then  
                \#import variables to current environment  
                . $CONFIG\_FILE  
        exec\_msg "$?" "" "Initialize variables failed!"  
        elif \[ -r $CONFIG\_FILE\_TEMP \]; then  
        echo "$\{PREFIX\_ERROR\}:you must ensure all variables in $CONFIG\_FILE\_TEMP are correct,and then change the name of $CONFIG\_FILE\_TEMP to $CONFIG\_FILE"  
        exit 1  
        else  
        if \[ ! -f $CONFIG\_FILE \]; then  
            create\_config\_file  
        else  
                    echo "$PREFIX\_ERROR:You have no permission to read $CONFIG\_FILE !"  
            exit 0  
        fi  
        fi  
\}  
  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\# routine name: flush\_bin\_log  
\# function: Initialize the CMD\_DUMP command  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
function flush\_bin\_log()  
\{  
        \#get user and password from password file  
        USER=\`cat $FILE\_PASSWD | $CMD\_OPENSSL aes-128-cbc -d -k $HOSTNAME -base64 | awk '\{print $1\}'\`  
        PASSWD=\`cat $FILE\_PASSWD | $CMD\_OPENSSL aes-128-cbc -d -k $HOSTNAME -base64 | awk '\{print $2\}'\`  
        \#if turned on the log\_bin,add a parameter:'--master-data=2' to CMD\_DUMP  
        \`$CMD\_MYSQL -u$USER -p$PASSWD -N -s -e "flush binary logs"\`  
        exec\_msg "$?" "successfully flush binary logs!" "failed to flush binary logs! check your password!"  
\}  
  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\# routine name: init\_dump  
\# function: Initialize the CMD\_DUMP command  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
function init\_dump()  
\{  
    \#get user and password from password file  
    USER=\`cat $FILE\_PASSWD | $CMD\_OPENSSL aes-128-cbc -d -k $HOSTNAME -base64 | awk '\{print $1\}'\`  
    PASSWD=\`cat $FILE\_PASSWD | $CMD\_OPENSSL aes-128-cbc -d -k $HOSTNAME -base64 | awk '\{print $2\}'\`  
    \#if turned on the log\_bin,add a parameter:'--master-data=2' to CMD\_DUMP  
    LOGBIN\_STATUS=\`$CMD\_MYSQL -u$USER -p$PASSWD -N -s -e "SHOW VARIABLES LIKE 'log\_bin'" | awk '\{print $2\}'\`  
    exec\_msg "$?" "" "failed to get the mysql variable:log\_bin! check your password!"  
    if \[ $LOGBIN\_STATUS = "ON" \]; then  
        MASTER='--master-data=2'  
    else  
        MASTER=' '  
    fi  
    \#Initialize the CMD\_DUMP command  
    CMD\_DUMP="$CMD\_MYSQLDUMP -u$USER -p$PASSWD -x -R $MASTER --socket=$SOCKET --default-character-set=utf8"  
\}  
  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\# routine name: backup\_all  
\# function: generate backup for all databases  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
function backup\_all()  
\{  
    init\_dump  
    DUMP\_FILE=$\{DIR\_BACKUP\}/\`$\{TIME\}\`.$\{HOSTNAME\}.all.sql  
    eval $\{BACKUP\_START\_MSG\}  
    $CMD\_DUMP -A  > $DUMP\_FILE  
    exec\_msg "$?" "generate backup:$DUMP\_FILE successfully!" "fail to generate backup!@rm -rf $DUMP\_FILE"  
    DUMP\_FILE\_NEW=$\{DIR\_BACKUP\}/\`tail -n 1 $DUMP\_FILE |awk '\{print $5"."$6\}'\`.$\{HOSTNAME\}.all.sql  
    mv $DUMP\_FILE $DUMP\_FILE\_NEW  
    gzip -f $DUMP\_FILE\_NEW  
    exec\_msg "$?" "" "fail to compress backup!"  
    sleep 5  
    eval $\{BACKUP\_FINISH\_MSG\}  
\}  
  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\# routine name: backup\_each  
\# function: generate backup for each database  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
function backup\_each()  
\{  
    init\_dump  
    eval $BACKUP\_START\_MSG  
    for db in $($CMD\_MYSQL -u$USER -p$PASSWD -N -s -e "SHOW DATABASES"|egrep -w $LIST\_INCLUDE\_DB\_DUMP)  
    do  
        DUMP\_FILE=$\{DIR\_BACKUP\}/\`$\{TIME\}\`.$\{HOSTNAME\}.$\{db\}.sql  
        $CMD\_DUMP $db --databases > $DUMP\_FILE  
        exec\_msg "$?" "generate backup:$DUMP\_FILE successfully!" "fail to generate backup!@rm -rf $DUMP\_FILE"  
        DUMP\_FILE\_NEW=$\{DIR\_BACKUP\}/\`tail -n 1 $DUMP\_FILE |awk '\{print $5"."$6\}'\`.$\{HOSTNAME\}.$\{db\}.sql  
        mv $DUMP\_FILE $DUMP\_FILE\_NEW  
        gzip -f $DUMP\_FILE\_NEW  
        exec\_msg "$?" "" "fail to compress backup!"  
    done  
    eval $\{BACKUP\_FINISH\_MSG\}  
\}  
  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\# routine name: backup\_db  
\# function: generate backup for database\[s\] selected  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
function backup\_db()  
\{  
    init\_dump  
    \#use "grep" to remove head spaces and tail spaces,use "sed" to replace spaces between strings to one colon  
    DUMP\_FILE\_POSTFIX=\`echo "$\{OPTARG\}" | grep -o "\[^ \]\\+\$ \\+\[^ \]\\+\$\*" | sed 's/\[ \]\[ \]\*/:/g'\`.sql  
    DUMP\_FILE=$\{DIR\_BACKUP\}/\`$\{TIME\}\`.$\{DUMP\_FILE\_POSTFIX\}  
    eval $BACKUP\_START\_MSG  
    $CMD\_DUMP --databases $OPTARG > $DUMP\_FILE  
    exec\_msg "$?" "generate backup:$DUMP\_FILE successfully!" "fail to generate backup!@rm -rf $DUMP\_FILE"  
    DUMP\_FILE\_NEW=$\{DIR\_BACKUP\}/\`tail -n 1 $DUMP\_FILE | awk '\{print $5"."$6\}'\`.$\{DUMP\_FILE\_POSTFIX\}  
    mv $DUMP\_FILE $DUMP\_FILE\_NEW  
    gzip -f $DUMP\_FILE\_NEW  
    exec\_msg "$?" "" "fail to compress backup!"  
    eval $\{BACKUP\_FINISH\_MSG\}  
\}  
  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\# routine name: backup\_dt  
\# function: generate backup for database table\[s\]  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
function backup\_dt()  
\{  
    init\_dump  
    \#use "grep" to remove head spaces and tail spaces,use "sed" to replace spaces between strings to one colon  
    DUMP\_TB=\`echo "$\{OPTARG\}" | grep -o "\[^ \]\\+\$ \\+\[^ \]\\+\$\*" | sed 's/\[ \]\[ \]\*/:/g'|awk -F":" '\{$1="";print\}'|grep -o "\[^ \]\\+\$ \\+\[^ \]\\+\$\*" | sed 's/\[ \]\[ \]\*/:/g'\`  
    DUMP\_DB=\`echo "$\{OPTARG\}" | grep -o "\[^ \]\\+\$ \\+\[^ \]\\+\$\*" | sed 's/\[ \]\[ \]\*/:/g'|awk -F":" '\{print $1\}'\`  
    DUMP\_FILE\_POSTFIX=$\{HOSTNAME\}.$\{DUMP\_DB\}.$\{DUMP\_TB\}.sql  
    DUMP\_FILE=$\{DIR\_BACKUP\}/\`$\{TIME\}\`.$\{DUMP\_FILE\_POSTFIX\}  
    $CMD\_DUMP $OPTARG > $DUMP\_FILE  
    exec\_msg "$?" "generate backup:$DUMP\_FILE successfully!" "fail to generate backup!@rm -rf $DUMP\_FILE"  
    DUMP\_FILE\_NEW=$\{DIR\_BACKUP\}/\`tail -n 1 $DUMP\_FILE |awk '\{print $5"."$6\}'\`.$\{DUMP\_FILE\_POSTFIX\}  
    mv $DUMP\_FILE $DUMP\_FILE\_NEW  
    gzip -f $DUMP\_FILE\_NEW  
    exec\_msg "$?" "" "fail to compress backup!"  
    eval $\{BACKUP\_FINISH\_MSG\}  
\}  
  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\# routine name: backup\_binlog  
\# function: generate backup for binlog  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
function backup\_binlog()  
\{  
    USER=\`cat $FILE\_PASSWD | $CMD\_OPENSSL aes-128-cbc -d -k $HOSTNAME -base64 | awk '\{print $1\}'\`  
    PASSWD=\`cat $FILE\_PASSWD | $CMD\_OPENSSL aes-128-cbc -d -k $HOSTNAME -base64 | awk '\{print $2\}'\`  
    BACPUP\_BINLOG\_START\_TIME=$(date -d "24 hour ago $(date +%Y%m%d)" +%Y-%m-%d\\ %H:%M:%S)  
    BACPUP\_BINLOG\_STOP\_TIME=$(date -d "1 second ago $(date +%Y%m%d)" +%Y-%m-%d\\ %H:%M:%S)  
    \#check whether the binlog backup dir exists,and create one  
    if \[ ! -d $DIR\_BACKUP\_BINLOG \]; then  
                echo "$PREFIX\_NOTICE:create the binlog backup directory \[$DIR\_BACKUP\_BINLOG\] now..."  
                mkdir -p $DIR\_BACKUP\_BINLOG  
                exec\_msg "$?" "create binlog backup directory successfully!" ""  
        fi  
    \#check whether the binlog index file exists  
    if \[ ! -r $\{FILE\_BINLOG\_INDEX\} \]; then  
                exec\_msg "1" "" "the file $\{FILE\_BINLOG\_INDEX\} is not found,please check $\{CONFIG\_FILE\} or my.cnf file."  
        fi  
    eval $BACKUP\_START\_MSG  
    for db in $($CMD\_MYSQL -u$USER -p$PASSWD -N -s -e "SHOW DATABASES"|egrep -w $LIST\_INCLUDE\_DB\_BINLOG)  
    do  
        BACKUP\_BINLOG\_FILE=$\{DIR\_BACKUP\_BINLOG\}/$(date -d "24 hour ago" +%Y%m%d).$\{HOSTNAME\}.$\{db\}.binlog  
        \#clean the BACKUP\_BINLOG\_FILE  
        > $BACKUP\_BINLOG\_FILE  
        for BINLOG\_FILE in $(awk -F'/' '\{print $NF\}' $FILE\_BINLOG\_INDEX)  
        do  
            BINLOG\_FILE=$(dirname $\{FILE\_BINLOG\_INDEX\})/$BINLOG\_FILE  
            $CMD\_MYSQLBINLOG -d $db --start-datetime="$BACPUP\_BINLOG\_START\_TIME" --stop\_datetime="$BACPUP\_BINLOG\_STOP\_TIME" $BINLOG\_FILE  >> $BACKUP\_BINLOG\_FILE  
            exec\_msg "$?" "" "fail to generate binlog backup,from $\{BINLOG\_FILE\} to $\{BACKUP\_BINLOG\_FILE\}!@rm -rf $BACKUP\_BINLOG\_FILE"  
            gzip -f $BACKUP\_BINLOG\_FILE  
            exec\_msg "$?" "" "fail to compress binlog backup!"  
        done  
        exec\_msg "$?" "generate binlog backup:$BACKUP\_BINLOG\_FILE successfully!" ""  
    done  
    eval $\{BACKUP\_FINISH\_MSG\}  
\}  
  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\# routine name: backup\_binlog  
\# function: generate backup for binlog  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
function backup\_binlog\_file()  
\{  
    \#flush binary logs  
    flush\_bin\_log  
        \#check whether the binlog backup dir exists,and create one  
        if \[ ! -d $DIR\_BACKUP\_BINLOG \]; then  
                echo "$PREFIX\_NOTICE:create the binlog backup directory \[$DIR\_BACKUP\_BINLOG\] now..."  
                mkdir -p $DIR\_BACKUP\_BINLOG  
                exec\_msg "$?" "create binlog backup directory successfully!" ""  
        fi  
        \#check whether the binlog index file exists  
        if \[ ! -r $\{FILE\_BINLOG\_INDEX\} \]; then  
                exec\_msg "1" "" "the file $\{FILE\_BINLOG\_INDEX\} is not found,please check $\{CONFIG\_FILE\} or my.cnf file."  
        fi  
    \#find begin and end index of binlog file to backup  
    BACK\_BINLOG\_INDEX=$DIR\_BACKUP\_BINLOG/mysql-bin-backup.index  
    if \[ -r $BACK\_BINLOG\_INDEX \];then  
        BACKED\_BINLOG\_FILE=$(tail -n 1 $BACK\_BINLOG\_INDEX)  
        BACK\_BINLOG\_INDEX\_BEGIN\_NUM=$(grep -n $BACKED\_BINLOG\_FILE $FILE\_BINLOG\_INDEX | awk -F':' '\{print $1\}')  
        if \[ -z $BACK\_BINLOG\_INDEX\_BEGIN\_NUM \];then  
            BACK\_BINLOG\_INDEX\_BEGIN\_NUM=0  
        fi  
    else  
        BACK\_BINLOG\_INDEX\_BEGIN\_NUM=0  
    fi  
    BACK\_BINLOG\_INDEX\_END\_NUM=$(wc -l $\{FILE\_BINLOG\_INDEX\} | awk '\{print $1\}')  
    BINLOG\_FILE=""  
    \#begin backup binlog file  
        eval $BACKUP\_START\_MSG  
        for BINLOG\_FILE in $(cat $FILE\_BINLOG\_INDEX | awk "NR > $BACK\_BINLOG\_INDEX\_BEGIN\_NUM && NR < $BACK\_BINLOG\_INDEX\_END\_NUM")  
        do  
            BINLOG\_FILE=$(dirname $\{FILE\_BINLOG\_INDEX\})/$BINLOG\_FILE  
            BACKUP\_BINLOG\_FILE=$DIR\_BACKUP\_BINLOG/$(date +%Y%m%d%H%M%S).$\{HOSTNAME\}.\`basename $\{BINLOG\_FILE\}\`  
            cp $BINLOG\_FILE $BACKUP\_BINLOG\_FILE  
            exec\_msg "$?" "" "fail to generate binlog backup:fail to copy $BINLOG\_FILE to $DIR\_BACKUP\_BINLOG"  
        echo \`basename $\{BINLOG\_FILE\}\` >> $BACK\_BINLOG\_INDEX  
        gzip -f $BACKUP\_BINLOG\_FILE  
        exec\_msg "$?" "" "fail to compress binlog backup!"  
        exec\_msg "$?" "generate binlog backup:$\{BACKUP\_BINLOG\_FILE\} successfully!" ""  
    done  
    if \[ -z $BINLOG\_FILE \];then  
        echo "$PREFIX\_NOTICE:no binlog file need to be backup."  
    fi  
        eval $\{BACKUP\_FINISH\_MSG\}  
\}  
  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\# routine name: backup\_dump\_clean  
\# function: clean up dumping backups beyond n days  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
function backup\_dump\_clean()  
\{  
    DELETE\_CNT=\`find $\{DIR\_BACKUP\} -mtime +$OPTARG -type f  | wc -l\`  
    if \[ $\{DELETE\_CNT\} -gt 0 \]; then  
        echo "$PREFIX\_NOTICE:find $\{DELETE\_CNT\} dumping-backups $OPTARG days ago from $\{DIR\_BACKUP\}, need to delete."  
        find $\{DIR\_BACKUP\} -mtime +$OPTARG -type f |xargs -i rm \{\} -f  
        exec\_msg "$?" "clean up $\{DELETE\_CNT\} dumping backup files successfully!" "fail to clean up the dumping backup files!"  
    else  
        echo "$PREFIX\_NOTICE:dumping-backups $OPTARG days ago are not found in $\{DIR\_BACKUP\}, need not to delete."  
    fi  
\}  
  
  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\# routine name: backup\_binlog\_clean  
\# function: clean up binlog backups beyond n days  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
function backup\_binlog\_clean()  
\{  
        DELETE\_CNT=\`find $\{DIR\_BACKUP\_BINLOG\} -mtime +$OPTARG -type f  | wc -l\`  
    if \[ $\{DELETE\_CNT\} -gt 0 \]; then  
        echo "$PREFIX\_NOTICE:find $\{DELETE\_CNT\} binlog-backups $OPTARG days ago from $\{DIR\_BACKUP\_BINLOG\}, need to delete."  
            find $\{DIR\_BACKUP\_BINLOG\} -mtime +$OPTARG -type f |xargs -i rm \{\} -f  
            exec\_msg "$?" "clean $\{DELETE\_CNT\} binlog backup files successfully!" "fail to clean the binlog backup files!"  
    else  
        echo "$PREFIX\_NOTICE:binlog-backups $OPTARG days ago are not found in $\{DIR\_BACKUP\_BINLOG,\} need not to delete."  
    fi  
\}  
  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\# script entry  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
if \[ $\# -eq 0 \]; then  
        usage\_notes  
else  
        init\_var  
        while getopts :aed:t:bhc:f:pg varname  
        do  
        case $varname in  
                    a)  check\_init\_value; backup\_all; ;;  
                    e)  check\_init\_value; backup\_each; ;;  
                    d)  check\_init\_value; backup\_db; ;;  
                    t)  check\_init\_value; backup\_dt; ;;  
                    b)  check\_init\_value; backup\_binlog; ;;  
                    h)  check\_init\_value; backup\_binlog\_file; ;;  
                    c)  check\_init\_value; backup\_dump\_clean; ;;  
                    f)  check\_init\_value; backup\_binlog\_clean; ;;  
                    g)  check\_init\_value; flush\_bin\_log; ;;  
                    p)  create\_passwd; ;;  
                    \*)  echo "$PREFIX\_ERROR:wrong parameters"; usage\_notes;  
        esac  
    done  
fi