Jquery中文网 www.jquerycn.cn
Jquery中文网 >  数据库  >  mysql  >  正文 linux中mysql数据库备份(可指定备份数据库)

linux中mysql数据库备份(可指定备份数据库)

发布时间:2017-12-12   编辑:www.jquerycn.cn
jquery中文网为您提供linux中mysql数据库备份(可指定备份数据库)等资源,欢迎您收藏本站,我们将为您提供最新的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="a@qq.com"
 fi
else
 touch $CONTACTS_FILE
 #写入默认邮箱联系人,当联系人列表不存在或者为空的时候将采用此联系人接收邮件
 CONTACTS_LIST="a@qq.com"
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 把备份数据以邮件形式发送给此文件中的联系人(一行一个联系人)

#写入联系人邮箱
a@139.com

您可能感兴趣的文章:
mysql数据库备份常用命令示例
vps自动备份shell脚本代码
一例mysql数据库备份的shell脚本
linux下mysql自动备份与ftp上传的shell脚本
Mysql 备份与恢复实例教程
网站备份与数据库备份shell脚本
MySQL常用配置方法
Linux下备份与还原整个MySQL数据库
备份数据库的shell脚本
备份所有MySql数据库的Shell脚本

[关闭]