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="[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]