linux中mysql數據庫備份(可指定備份數據庫)

在linux中按下面方法配置就可以，此次配置mysql數據庫備份有三個文件，分別為：startup.sh、backup_list.txt、contacts_list.txt三個文件，下面我們會分別介紹這三個文件的作用.

startup.sh 備份腳本

#!/bin/bash
BASEDIR=$(cd `dirname "$0"`;pwd)
LOG_DIR=$BASEDIR/logs
TIME=$(date +%Y-%m-%d-%H-%M-%S)
TIME_DAY=$(date +%Y-%m-%d)
TIME_HOUR=$(date +%H)
#設置腳本可以運行的時間點(此項根據需求修改)
TIME_HOUR_POINT=15
#SERVER_IP寫當前服務器的IP地址,如果為空腳本將自動查找一個IP地址作為此值
SERVER_IP=
#登錄數據庫的用戶
MYSQL_USER=mysql
#密碼在當前目錄創建一個.passwd的文件將密碼寫入進去
MYSQL_PASSWD=`cat $BASEDIR/.passwd`
#數據庫的SOCK文件位置
MYSQL_SOCK=/var/lib/mysql/mysql.sock
BJG_DIR=$BASEDIR/mysql_bjg
DATA_DIR=$BASEDIR/mysql_data
#寫入要備份的數據庫列表
BACKUP_LIST_FILE=$BASEDIR/backup_list.txt
LOG_FILE=$LOG_DIR/total.log
BACKUP_ERR=$LOG_DIR/backup_err.log
CONTACTS_FILE=$BASEDIR/contacts_list.txt
#是否打開郵件提醒功能(1打開,其它值關閉)
MAILX_OPEN_CLOSE=1
#是否打開數據轉移(1打開,其它值關閉)
OPEN_TRANS_DATA=0
#設置發郵件程序路徑我這裡是mailx如果找不到的話將使用如下定義系統默認mailx或者mail發郵件
MAILX_BIN=/usr/local/mailx/mailx
#設置信任主機的信息,用來將備份轉移的
SSH_SERVER_IP="192.168.1.57"
SSH_SERVER_USER="root"
SSH_SERVER_PORT=22
#異地轉移目錄
SSH_SERVER_DIR="/home/mysql_backup"
#設置監控遠程分區,此項主要用於判斷是否有可用空間,如果剩余空間不足將不能轉移數據
SSH_SERVER_DISK="/dev/sda2"
if [ ! -e $MAILX_BIN ];then
 MAILX_BIN=mailx
else
 MAILX_BIN=mail
fi
if [ -e $CONTACTS_FILE ];then
 CONTACTS_LIST=`cat $BASEDIR/contacts_list.txt|grep -v "^#"|grep -v "^$"|xargs`
 if [ -z $CONTACTS_LIST ];then
  #寫入默認郵箱聯系人,當聯系人列表不存在或者為空的時候將采用此聯系人接收郵件
  CONTACTS_LIST="[email protected]"
 fi
else
 touch $CONTACTS_FILE
 #寫入默認郵箱聯系人,當聯系人列表不存在或者為空的時候將采用此聯系人接收郵件
 CONTACTS_LIST="[email protected]"
fi
if [ -z $SERVER_IP ];then
 SERVER_IP=`ifconfig|egrep -o 'addr:[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}'|grep -v '127.0.0.1'|cut -d ':' -f2|head -1`
fi
if [ ! -e $LOG_DIR ];then
 mkdir -p $LOG_DIR
fi
if [ ! -e $BJG_DIR ];then
 mkdir -p $BJG_DIR
fi
if [ ! -e $DATA_DIR ];then
 mkdir -p $DATA_DIR
fi
if [ -e $BACKUP_ERR ];then
 /bin/rm -f $BACKUP_ERR &>/dev/null
fi
if [ $TIME_HOUR_POINT != $TIME_HOUR ];then
 echo "$(date)|當前程序不允許在此時間段運行,請修改腳本(TIME_HOUR_POINT)值為當前小時."
 exit 0
else
if [ ! -e $BACKUP_LIST_FILE ]||[ ! -s $BACKUP_LIST_FILE ];then
 echo "$(date)|當前數據庫備份出錯,確認($BACKUP_LIST_FILE)文件是否有內容."|tee -a $LOG_FILE
 exit 0
else
 cat $BACKUP_LIST_FILE|grep -v "^#"|grep -v "^$"|while read i;do
  DB_NAME=`echo "$i"|cut -d '|' -f1`
  DB_TABLE_NAME=`echo "$i"|cut -d '|' -f2`
  DB_TABLE_IGNORE_NAME=`echo "$i"|cut -d '|' -f3`
  if [[ $DB_TABLE_NAME == "NULL" ]]||[[ $DB_TABLE_NAME == "null" ]];then
   if [[ $DB_TABLE_IGNORE_NAME == "NULL" ]]||[[ $DB_TABLE_IGNORE_NAME == "null" ]];then
    (
     mysqldump -u$MYSQL_USER -p$MYSQL_PASSWD -R -d $DB_NAME >$BJG_DIR/$DB_NAME.$TIME.sql
     if [ $? -ne 0 ];then
      echo "$(date)|數據庫($DB_NAME)表結構,存儲過程備份失敗." >>$LOG_FILE
      exit 0
     else
      echo "$(date)|數據庫($DB_NAME)表結構,存儲過程備份完成." >>$LOG_FILE
      (mysqldump -u$MYSQL_USER -p$MYSQL_PASSWD -S $MYSQL_SOCK --opt --flush-logs --single-transaction --triggers -R --database $DB_NAME||echo "mysqldump備份出錯" >$BACKUP_ERR)|(gzip > $DATA_DIR/${SERVER_IP}.$DB_NAME.$TIME_DAY.sql.gz||echo "mysql備份壓縮GZIP報錯" >$BACKUP_ERR)
      if [ -e $BACKUP_ERR ]&&[ -s $BACKUP_ERR ];then
       echo "mysqldump備份出錯或者壓縮報錯，程序退出執行" >>$LOG_FILE
       if [ $MAILX_OPEN_CLOSE -eq 1 ];then
        echo "$(date)|數據庫($DB_NAME)數據備份出錯"|$MAILX_BIN -s "$SERVER_IP數據庫備份提醒" $CONTACTS_LIST
       else
        echo "$(date)|數據庫($DB_NAME)數據備份出錯,當前設置為不觸發郵件通知." >>$LOG_FILE
       fi     
       exit 0
      else
       BACKUP_SIZE=`du -sh $DATA_DIR/$SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz|awk '{print $1}'`
       echo "$(date)|當前數據庫($DB_NAME)數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)備份壓縮完成,當前數據大小為($BACKUP_SIZE)." >>$LOG_FILE     
       if [ $MAILX_OPEN_CLOSE -eq 1 ];then
        echo "$(date)|數據庫($DB_NAME)數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)備份成功,當前數據大小為($BACKUP_SIZE)"|$MAILX_BIN -s "$SERVER_IP數據庫備份提醒" $CONTACTS_LIST
        echo "$(date)|數據庫($DB_NAME)數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)備份成功,當前數據大小為($BACKUP_SIZE),已觸發郵件通知$CONTACTS_LIST" >>$LOG_FILE
       else
        echo "$(date)|數據庫($DB_NAME)數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)備份成功,當前數據大小為($BACKUP_SIZE),當前設置為不觸發郵件通知." >>$LOG_FILE
       fi
       if [ $OPEN_TRANS_DATA -ne 1 ];then
        echo "$(date)|當前設置不轉移本地備份數據."  >>$LOG_FILE
        exit 0
       else
        BACKUP_SIZE_LOCAL=`du -sh $DATA_DIR/$SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz|awk '{print $1}'|grep -o '.$'`
        DISK_FREE_SSH=`ssh -p $SSH_SERVER_PORT $SSH_SERVER_USER@$SSH_SERVER_IP "df -TH|grep '$SSH_SERVER_DISK'"|awk '{print $5}'|grep -o '.$'`
        if [ $? -ne 0 ];then
         echo "$(date)|當前遠程服務器($SSH_SERVER_IP)端口($SSH_SERVER_PORT)連接失敗,數據轉移失敗,程序退出."  >>$LOG_FILE
         exit 0
        else
         if [ $DISK_FREE_SSH == "G" ];then
          if [ $DISK_FREE_SSH == $BACKUP_SIZE_LOCAL ];then
           BACKUP_SIZE_G=`du -sh $DATA_DIR/$SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz|awk '{print $1}'|cut -d 'G' -f1`
           DISK_FREE_SSH_G=`ssh -p $SSH_SERVER_PORT $SSH_SERVER_USER@$SSH_SERVER_IP "df -TH|grep '$SSH_SERVER_DISK'"|awk '{print $5}'|cut -d 'G' -f1`
           if [ $BACKUP_SIZE_G -lt $DISK_FREE_SSH_G ];then
            scp -P $SSH_SERVER_PORT $DATA_DIR/$SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz $SSH_SERVER_USER@$SSH_SERVER_IP:$SSH_SERVER_DIR/
            if [ $? -eq 0 ];then
             echo "$(date)|數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)成功轉移至服務器($SSH_SERVER_IP)的($SSH_SERVER_DIR)路徑." >>$LOG_FILE
             /bin/rm -f $DATA_DIR/$SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz &>/dev/null
            else
             echo "$(date)|數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)轉移失敗."  >>$LOG_FILE
             exit 0
            fi
           else
            echo "$(date)|遠程服務器($SSH_SERVER_IP)磁盤剩余空間過小,數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)轉移失敗."
            exit 0
           fi
          else
           scp -P $SSH_SERVER_PORT $DATA_DIR/$SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz $SSH_SERVER_USER@$SSH_SERVER_IP:$SSH_SERVER_DIR/
           if [ $? -eq 0 ];then
            echo "$(date)|數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)成功轉移至服務器($SSH_SERVER_IP)的($SSH_SERVER_DIR)路徑." >>$LOG_FILE
            /bin/rm -f $DATA_DIR/$SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz &>/dev/null
           else
            echo "$(date)|數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)轉移失敗."  >>$LOG_FILE
            exit 0
           fi        
          fi
         else
          echo "$(date)|遠程服務器($SSH_SERVER_IP)磁盤剩余空間過小,數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)轉移失敗."  >>$LOG_FILE
          exit 0
         fi
        fi 
       fi
      fi
     fi    
    )&
   else
    (
     mysqldump -u$MYSQL_USER -p$MYSQL_PASSWD -R -d $DB_NAME $DB_TABLE_IGNORE_NAME >$BJG_DIR/$DB_NAME.$TIME.sql
     if [ $? -ne 0 ];then
      echo "$(date)|數據庫($DB_NAME)表結構,存儲過程備份失敗." >>$LOG_FILE
      exit 0
     else
      echo "$(date)|數據庫($DB_NAME)表結構,存儲過程備份完成." >>$LOG_FILE
      (mysqldump -u$MYSQL_USER -p$MYSQL_PASSWD -S $MYSQL_SOCK --opt --flush-logs --single-transaction --triggers -R $DB_NAME $DB_TABLE_IGNORE_NAME||echo "mysqldump備份出錯" >$BACKUP_ERR)|(gzip > $DATA_DIR/${SERVER_IP}.$DB_NAME.$TIME_DAY.sql.gz||echo "mysql備份壓縮GZIP報錯" >$BACKUP_ERR)
      if [ -e $BACKUP_ERR ]&&[ -s $BACKUP_ERR ];then
       echo "mysqldump備份出錯或者壓縮報錯，程序退出執行" >>$LOG_FILE
       if [ $MAILX_OPEN_CLOSE -eq 1 ];then
        echo "$(date)|數據庫($DB_NAME)數據備份出錯"|$MAILX_BIN -s "$SERVER_IP數據庫備份提醒" $CONTACTS_LIST
       else
        echo "$(date)|數據庫($DB_NAME)數據備份出錯,當前設置為不觸發郵件通知." >>$LOG_FILE
       fi     
       exit 0
      else
       BACKUP_SIZE=`du -sh $DATA_DIR/$SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz|awk '{print $1}'`
       echo "$(date)|當前數據庫($DB_NAME)數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)備份壓縮完成,當前數據大小為($BACKUP_SIZE)." >>$LOG_FILE     
       if [ $MAILX_OPEN_CLOSE -eq 1 ];then
        echo "$(date)|數據庫($DB_NAME)數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)備份成功,當前數據大小為($BACKUP_SIZE)"|$MAILX_BIN -s "$SERVER_IP數據庫備份提醒" $CONTACTS_LIST
        echo "$(date)|數據庫($DB_NAME)數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)備份成功,當前數據大小為($BACKUP_SIZE),已觸發郵件通知$CONTACTS_LIST" >>$LOG_FILE
       else
        echo "$(date)|數據庫($DB_NAME)數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)備份成功,當前數據大小為($BACKUP_SIZE),當前設置為不觸發郵件通知." >>$LOG_FILE
       fi
       if [ $OPEN_TRANS_DATA -ne 1 ];then
        echo "$(date)|當前設置不轉移本地備份數據."  >>$LOG_FILE
        exit 0
       else
        BACKUP_SIZE_LOCAL=`du -sh $DATA_DIR/$SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz|awk '{print $1}'|grep -o '.$'`
        DISK_FREE_SSH=`ssh -p $SSH_SERVER_PORT $SSH_SERVER_USER@$SSH_SERVER_IP "df -TH|grep '$SSH_SERVER_DISK'"|awk '{print $5}'|grep -o '.$'`
        if [ $? -ne 0 ];then
         echo "$(date)|當前遠程服務器($SSH_SERVER_IP)端口($SSH_SERVER_PORT)連接失敗,數據轉移失敗,程序退出."  >>$LOG_FILE
         exit 0
        else
         if [ $DISK_FREE_SSH == "G" ];then
          if [ $DISK_FREE_SSH == $BACKUP_SIZE_LOCAL ];then
           BACKUP_SIZE_G=`du -sh $DATA_DIR/$SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz|awk '{print $1}'|cut -d 'G' -f1`
           DISK_FREE_SSH_G=`ssh -p $SSH_SERVER_PORT $SSH_SERVER_USER@$SSH_SERVER_IP "df -TH|grep '$SSH_SERVER_DISK'"|awk '{print $5}'|cut -d 'G' -f1`
           if [ $BACKUP_SIZE_G -lt $DISK_FREE_SSH_G ];then
            scp -P $SSH_SERVER_PORT $DATA_DIR/$SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz $SSH_SERVER_USER@$SSH_SERVER_IP:$SSH_SERVER_DIR/
            if [ $? -eq 0 ];then
             echo "$(date)|數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)成功轉移至服務器($SSH_SERVER_IP)的($SSH_SERVER_DIR)路徑." >>$LOG_FILE
             /bin/rm -f $DATA_DIR/$SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz &>/dev/null
            else
             echo "$(date)|數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)轉移失敗."  >>$LOG_FILE
             exit 0
            fi
           else
            echo "$(date)|遠程服務器($SSH_SERVER_IP)磁盤剩余空間過小,數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)轉移失敗."
            exit 0
           fi
          else
           scp -P $SSH_SERVER_PORT $DATA_DIR/$SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz $SSH_SERVER_USER@$SSH_SERVER_IP:$SSH_SERVER_DIR/
           if [ $? -eq 0 ];then
            echo "$(date)|數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)成功轉移至服務器($SSH_SERVER_IP)的($SSH_SERVER_DIR)路徑." >>$LOG_FILE
            /bin/rm -f $DATA_DIR/$SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz &>/dev/null
           else
            echo "$(date)|數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)轉移失敗."  >>$LOG_FILE
            exit 0
           fi        
          fi
         else
          echo "$(date)|遠程服務器($SSH_SERVER_IP)磁盤剩余空間過小,數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)轉移失敗."  >>$LOG_FILE
          exit 0
         fi
        fi 
       fi
      fi
     fi    
    )&
   fi
  else
   if [[ $DB_TABLE_IGNORE_NAME == "NULL" ]]||[[ $DB_TABLE_IGNORE_NAME == "null" ]];then
    (
     mysqldump -u$MYSQL_USER -p$MYSQL_PASSWD -R -d $DB_NAME $DB_TABLE_NAME >$BJG_DIR/$DB_NAME.$TIME.sql
     if [ $? -ne 0 ];then
      echo "$(date)|數據庫($DB_NAME)表結構,存儲過程備份失敗." >>$LOG_FILE
      exit 0
     else
      echo "$(date)|數據庫($DB_NAME)表結構,存儲過程備份完成." >>$LOG_FILE
      (mysqldump -u$MYSQL_USER -p$MYSQL_PASSWD -S $MYSQL_SOCK --opt --flush-logs --single-transaction --triggers -R $DB_NAME $DB_TABLE_NAME||echo "mysqldump備份出錯" >$BACKUP_ERR)|(gzip > $DATA_DIR/${SERVER_IP}.$DB_NAME.$TIME_DAY.sql.gz||echo "mysql備份壓縮GZIP報錯" >$BACKUP_ERR)
      if [ -e $BACKUP_ERR ]&&[ -s $BACKUP_ERR ];then
       echo "mysqldump備份出錯或者壓縮報錯，程序退出執行" >>$LOG_FILE
       if [ $MAILX_OPEN_CLOSE -eq 1 ];then
        echo "$(date)|數據庫($DB_NAME)數據備份出錯"|$MAILX_BIN -s "$SERVER_IP數據庫備份提醒" $CONTACTS_LIST
       else
        echo "$(date)|數據庫($DB_NAME)數據備份出錯,當前設置為不觸發郵件通知." >>$LOG_FILE
       fi     
       exit 0
      else
       BACKUP_SIZE=`du -sh $DATA_DIR/$SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz|awk '{print $1}'`
       echo "$(date)|當前數據庫($DB_NAME)數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)備份壓縮完成,當前數據大小為($BACKUP_SIZE)." >>$LOG_FILE     
       if [ $MAILX_OPEN_CLOSE -eq 1 ];then
        echo "$(date)|數據庫($DB_NAME)數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)備份成功,當前數據大小為($BACKUP_SIZE)"|$MAILX_BIN -s "$SERVER_IP數據庫備份提醒" $CONTACTS_LIST
        echo "$(date)|數據庫($DB_NAME)數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)備份成功,當前數據大小為($BACKUP_SIZE),已觸發郵件通知$CONTACTS_LIST" >>$LOG_FILE
       else
        echo "$(date)|數據庫($DB_NAME)數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)備份成功,當前數據大小為($BACKUP_SIZE),當前設置為不觸發郵件通知." >>$LOG_FILE
       fi
       if [ $OPEN_TRANS_DATA -ne 1 ];then
        echo "$(date)|當前設置不轉移本地備份數據."  >>$LOG_FILE
        exit 0
       else
        BACKUP_SIZE_LOCAL=`du -sh $DATA_DIR/$SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz|awk '{print $1}'|grep -o '.$'`
        DISK_FREE_SSH=`ssh -p $SSH_SERVER_PORT $SSH_SERVER_USER@$SSH_SERVER_IP "df -TH|grep '$SSH_SERVER_DISK'"|awk '{print $5}'|grep -o '.$'`
        if [ $? -ne 0 ];then
         echo "$(date)|當前遠程服務器($SSH_SERVER_IP)端口($SSH_SERVER_PORT)連接失敗,數據轉移失敗,程序退出."  >>$LOG_FILE
         exit 0
        else
         if [ $DISK_FREE_SSH == "G" ];then
          if [ $DISK_FREE_SSH == $BACKUP_SIZE_LOCAL ];then
           BACKUP_SIZE_G=`du -sh $DATA_DIR/$SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz|awk '{print $1}'|cut -d 'G' -f1`
           DISK_FREE_SSH_G=`ssh -p $SSH_SERVER_PORT $SSH_SERVER_USER@$SSH_SERVER_IP "df -TH|grep '$SSH_SERVER_DISK'"|awk '{print $5}'|cut -d 'G' -f1`
           if [ $BACKUP_SIZE_G -lt $DISK_FREE_SSH_G ];then
            scp -P $SSH_SERVER_PORT $DATA_DIR/$SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz $SSH_SERVER_USER@$SSH_SERVER_IP:$SSH_SERVER_DIR/
            if [ $? -eq 0 ];then
             echo "$(date)|數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)成功轉移至服務器($SSH_SERVER_IP)的($SSH_SERVER_DIR)路徑." >>$LOG_FILE
             /bin/rm -f $DATA_DIR/$SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz &>/dev/null
            else
             echo "$(date)|數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)轉移失敗."  >>$LOG_FILE
             exit 0
            fi
           else
            echo "$(date)|遠程服務器($SSH_SERVER_IP)磁盤剩余空間過小,數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)轉移失敗."
            exit 0
           fi
          else
           scp -P $SSH_SERVER_PORT $DATA_DIR/$SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz $SSH_SERVER_USER@$SSH_SERVER_IP:$SSH_SERVER_DIR/
           if [ $? -eq 0 ];then
            echo "$(date)|數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)成功轉移至服務器($SSH_SERVER_IP)的($SSH_SERVER_DIR)路徑." >>$LOG_FILE
            /bin/rm -f $DATA_DIR/$SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz &>/dev/null
           else
            echo "$(date)|數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)轉移失敗."  >>$LOG_FILE
            exit 0
           fi        
          fi
         else
          echo "$(date)|遠程服務器($SSH_SERVER_IP)磁盤剩余空間過小,數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)轉移失敗."  >>$LOG_FILE
          exit 0
         fi
        fi 
       fi
      fi
     fi    
    )&
   else
    (
     mysqldump -u$MYSQL_USER -p$MYSQL_PASSWD -R -d $DB_NAME $DB_TABLE_NAME $DB_TABLE_IGNORE_NAME >$BJG_DIR/$DB_NAME.$TIME.sql
     if [ $? -ne 0 ];then
      echo "$(date)|數據庫($DB_NAME)表結構,存儲過程備份失敗." >>$LOG_FILE
      exit 0
     else
      echo "$(date)|數據庫($DB_NAME)表結構,存儲過程備份完成." >>$LOG_FILE
      (mysqldump -u$MYSQL_USER -p$MYSQL_PASSWD -S $MYSQL_SOCK --opt --flush-logs --single-transaction --triggers -R $DB_NAME $DB_TABLE_NAME $DB_TABLE_IGNORE_NAME||echo "mysqldump備份出錯" >$BACKUP_ERR)|(gzip > $DATA_DIR/${SERVER_IP}.$DB_NAME.$TIME_DAY.sql.gz||echo "mysql備份壓縮GZIP報錯" >$BACKUP_ERR)
      if [ -e $BACKUP_ERR ]&&[ -s $BACKUP_ERR ];then
       echo "mysqldump備份出錯或者壓縮報錯，程序退出執行" >>$LOG_FILE
       if [ $MAILX_OPEN_CLOSE -eq 1 ];then
        echo "$(date)|數據庫($DB_NAME)數據備份出錯"|$MAILX_BIN -s "$SERVER_IP數據庫備份提醒" $CONTACTS_LIST
       else
        echo "$(date)|數據庫($DB_NAME)數據備份出錯,當前設置為不觸發郵件通知." >>$LOG_FILE
       fi     
       exit 0
      else
       BACKUP_SIZE=`du -sh $DATA_DIR/$SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz|awk '{print $1}'`
       echo "$(date)|當前數據庫($DB_NAME)數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)備份壓縮完成,當前數據大小為($BACKUP_SIZE)." >>$LOG_FILE     
       if [ $MAILX_OPEN_CLOSE -eq 1 ];then
        echo "$(date)|數據庫($DB_NAME)數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)備份成功,當前數據大小為($BACKUP_SIZE)"|$MAILX_BIN -s "$SERVER_IP數據庫備份提醒" $CONTACTS_LIST
        echo "$(date)|數據庫($DB_NAME)數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)備份成功,當前數據大小為($BACKUP_SIZE),已觸發郵件通知$CONTACTS_LIST" >>$LOG_FILE
       else
        echo "$(date)|數據庫($DB_NAME)數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)備份成功,當前數據大小為($BACKUP_SIZE),當前設置為不觸發郵件通知." >>$LOG_FILE
       fi
       if [ $OPEN_TRANS_DATA -ne 1 ];then
        echo "$(date)|當前設置不轉移本地備份數據."  >>$LOG_FILE
        exit 0
       else
        BACKUP_SIZE_LOCAL=`du -sh $DATA_DIR/$SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz|awk '{print $1}'|grep -o '.$'`
        DISK_FREE_SSH=`ssh -p $SSH_SERVER_PORT $SSH_SERVER_USER@$SSH_SERVER_IP "df -TH|grep '$SSH_SERVER_DISK'"|awk '{print $5}'|grep -o '.$'`
        if [ $? -ne 0 ];then
         echo "$(date)|當前遠程服務器($SSH_SERVER_IP)端口($SSH_SERVER_PORT)連接失敗,數據轉移失敗,程序退出."  >>$LOG_FILE
         exit 0
        else
         if [ $DISK_FREE_SSH == "G" ];then
          if [ $DISK_FREE_SSH == $BACKUP_SIZE_LOCAL ];then
           BACKUP_SIZE_G=`du -sh $DATA_DIR/$SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz|awk '{print $1}'|cut -d 'G' -f1`
           DISK_FREE_SSH_G=`ssh -p $SSH_SERVER_PORT $SSH_SERVER_USER@$SSH_SERVER_IP "df -TH|grep '$SSH_SERVER_DISK'"|awk '{print $5}'|cut -d 'G' -f1`
           if [ $BACKUP_SIZE_G -lt $DISK_FREE_SSH_G ];then
            scp -P $SSH_SERVER_PORT $DATA_DIR/$SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz $SSH_SERVER_USER@$SSH_SERVER_IP:$SSH_SERVER_DIR/
            if [ $? -eq 0 ];then
             echo "$(date)|數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)成功轉移至服務器($SSH_SERVER_IP)的($SSH_SERVER_DIR)路徑." >>$LOG_FILE
             /bin/rm -f $DATA_DIR/$SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz &>/dev/null
            else
             echo "$(date)|數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)轉移失敗."  >>$LOG_FILE
             exit 0
            fi
           else
            echo "$(date)|遠程服務器($SSH_SERVER_IP)磁盤剩余空間過小,數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)轉移失敗."
            exit 0
           fi
          else
           scp -P $SSH_SERVER_PORT $DATA_DIR/$SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz $SSH_SERVER_USER@$SSH_SERVER_IP:$SSH_SERVER_DIR/
           if [ $? -eq 0 ];then
            echo "$(date)|數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)成功轉移至服務器($SSH_SERVER_IP)的($SSH_SERVER_DIR)路徑." >>$LOG_FILE
            /bin/rm -f $DATA_DIR/$SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz &>/dev/null
           else
            echo "$(date)|數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)轉移失敗."  >>$LOG_FILE
            exit 0
           fi        
          fi
         else
          echo "$(date)|遠程服務器($SSH_SERVER_IP)磁盤剩余空間過小,數據($SERVER_IP.$DB_NAME.$TIME_DAY.sql.gz)轉移失敗."  >>$LOG_FILE
          exit 0
         fi
        fi 
       fi
      fi
     fi    
    )&
   fi
  fi 
 done
fi
fi

backup_list.txt 在此可以指定要備份的數據庫

#數據庫備份規則字段說明,備份的數據庫名稱,備份的表名稱(如果不需要特別指明表填寫null即可),不需要備份的數據庫表(如果沒有需要排除的數據表填入null)
#規則案例：備份test數據庫的tb1 tb2 tb3表,但是排除tb4和tb5表的備份
#test|tb1 tb2 tb3|--ignore-table=test.tb4 --ignore-table=test.tb5
mysql|null|null
#test111|null|--ignore-table=test111.tb1
#test111|tb2|--ignore-table=test111.tb1
test111|null|NULL
#test111|tb1|null
#test111|null|--ignore-table=test111.tb1 --ignore-table=test111.tb2
#test111|tb1 tb2|null

contacts_list.txt 把備份數據以郵件形式發送給此文件中的聯系人(一行一個聯系人)

#寫入聯系人郵箱
[email protected]