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

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

时间： 2024-09-14 04:07:20

linux中mysql数据库备份(可指定备份数据库)的相关文章

实现Linux中Mysql数据库自动备份并上传到远程FTP服务器

这篇文章很有分享价值,因为我们在实际的生产环境中需要将数据库进行自动备份,然后上传到指定的位置,当然也可以像以下文章中所讲的一样,上传到你指定的FTP服务器中,从而实现Mysql数据库自动备份并上传到远程FTP服务器的部署,在Linuxhttp://www.aliyun.com/zixun/aggregation/31093.html">技术交流群中,也有朋友会遇到这方面的问题,可以将此文分享给群友共益. 注意:任何的操作都会具有风险性,请在本机实际测试通过之后再部署到服务器环境,这样即熟

请问在游标中怎么将数据库更改到指定的数据库

问题描述 declare @DB Varchar(20)declare dd_cursor cursor for select name from [master].[dbo].sysdatabases for read onlyopen dd_cursorfetch next from dd_cursor into @DBwhile @@fetch_status = 0beginprint @DBuse @DB --这句有错 是否有别的方法达到同样的效果,更改数据库更改到指定的数据库endcl

linux中mysql自动备份脚本

其实这个mysql自动备份的小脚本非常简单,下面是这个小脚本的步骤.  代码如下 复制代码 #cd /www #mkdir mysqlbackup #vi mysqlautobackup.sh 这个mysqlautobackup.sh的文件里写入以下内容:  代码如下 复制代码 filename=`date +%Y%m%d` mysql_bin_dir/mysqldump --opt datename -u dateuser -pdatepassword | gzip > /www/mysqlb

linux中MYSQL数据库过大设定max_allowed_packet大小

晚上再捣鼓网站的时候恢复较大数据库出现一点问题,在Linux中备份的时候直接用命令备份,这样比直接从PHPMYADMIN中导出来完整度较高一些,同样的在使用恢复数据的时候出现问题.有提示"ERROR 2006 (HY000) at Line 179:MySQL server has gone away"这样的提示错误. Captain告知解决方法,是因为在Linux环境中max_allowed_packet默认设置太小导致的,数据库表比较大,默认应该是1MB,所以需要调整参数. 这里,

Linux下 mysql oracle 简单使用手册_数据库其它

安装好mysql后,配置好环境变量,然后 敲入 mysql 进入mysql 然后可以 Use mysql; Select * from user; 来查看所有的用户: Mysql 下执行脚本文件: Mysql > source xxx.sql; 1.1 两种方式增加用户:(转) 有2个不同的方法增加用户:通过使用GRANT语句或通过直接操作MySQL授权表.比较好的方法是使用GRANT语句,因为他们是更简明并且好像错误少些. 下面的例子显示出如何使用MySQL客户安装新用户.这些例子假定权限已缺

linux中mySQL的安装和配置教程

本文的Linux环境是 Red Hat 9.0,MySQL是4.0.16. 二.安装Mysql 1.下载MySQL的安装文件 安装MySQL需要下面两个文件: MySQL-server-4.0.16-0.i386.rpm MySQL-client-4.0.16-0.i386.rpm 下载地址为:http://www.mysql.com/downloads/mysql-4.0.html,打开此网页,下拉网页找到"Linux x86 RPM downloads"项,找到"Serv

linux中Mysql的登陆与设置密码步骤

linux下Mysql的登陆与设置密码(本文基于centos6.4.mysql5.7.3),mysql安装在/usr/local/mysql目录下: [root@lnmp ~]# /usr/local/mysql/bin/mysqladmin -uroot password 'admin123'    #修改mysql密码为admin123 [root@lnmp ~]# /usr/local/mysql/bin/mysql    #如果是空密码可以这样进入mysql [root@lnmp ~]#

linux中mysql不能 REPAIR TABLE修复表解决办法

REPAIR TABLE语法 REPAIR TABLE `table_name` 修复表 REPAIR TABLE 用于修复被破坏的表 在linux中使用 REPAIR TABLE `table_name` pre_forum_thread 提示 xt.pre_forum_thread repair error 1 when fixing table xt.pre_forum_thread repair Error Can't change ownership of the file '/usr

linux中MYSQL备份数据库表(多个表)

MYSQL备份数据库表 独立备份某个数据库的某个表操作: mysql> use mysql mysql> show tables; +---------------------------+ | Tables_in_mysql           | +---------------------------+ | columns_priv              | | db                        | | event                     | | f