mysql按照每个表一个备份文件的逻辑备份脚本和检查报警脚本

下面是逻辑备份脚本:
1.首先通过information_schema.tables获取所有数据库名称
2.然后根据日期创建临时备份目录,根据数据库名进行循环,获取每个数据库下面的表名集合
3.然后进行循环备份.按照表级备份完成后
4.然后进行压缩和删除.
5.最后进行写日志操作,方便出错预警和定位问题
6.注意事项:一定要谨记,备份命令加上–single-transaction参数可以不锁定表导出!

#!/bin/sh
#set -x
#-----------------------------------------------------------
# Usage: This script is used to backup the mysql database
#        which using for the mail of the 
#        ChinaMobile's Online Application Store Development
# Name: mysqldump_backup.sh
# Autor: xianyezhao
# modify:
# Create_date: 28/04/2019
#  sript location:/data/backup/mysql/table.192.168.1.200.mysqldump
#chmod mysqldump_backup.sh to 711
# crontab info 0 1 * * * /data/backup/mysql/table.192.168.1.200.mysqldump/scripts/mysqldump_backup.sh 
#-----------------------------------------------------------

set -x
# configure the environment variables
. ~/.bash_profile
export TMPDIR=/tmp

# configure the work directory
GZIP_BIN=/usr/bin/gzip
#WORKPATH mesns the workpath
WORKPATH=/data/backup/mysql/table.192.168.1.200.mysqldump
WORKDATE=`date +%Y%m%d`
# BASEDIR means the filesystem mounted
BASEDIR="/data" 
SPACE_RATE=85        #space rate 85
SPACE_ROOM=10485760  #10GB
BACKUP_FILE_NAME="mysqldump_backup"
MYSQLDUMP="/data/software/mysql/product/bin/mysqldump"
MYSQL="/data/software/mysql/product/bin/mysql"
BACKUP_USER="dbmon"
BACKUP_PASSWD="******"
BACKUP_HOST="192.168.1.200"
BACKUP_PORT="3306"
ALL_DATABASES="$($MYSQL -u$BACKUP_USER -h$BACKUP_HOST  -p$BACKUP_PASSWD -P$BACKUP_PORT -Bse"select distinct table_schema from information_schema.tables where table_schema not in ('information_schema','performance_schema','test')")"


MAIL_BIN="python /home/mysql/scripts/mail/mail.py"
MAIL_TO="[email protected]"
MAIL_SUB="mysqldump_backup Report ${SHOW_HOST}: ${DATE}"
ANA_FILE=${WORKPATH}/maillog/"maillog"${DATE}.log
ANA_FILE_TEXT=""
##end set environment

#sent mail last day perfomance alert
my_sentmail()
{
   ${MAIL_BIN}  -s "${MAIL_SUB}" -t "${MAIL_TO}" -b "${ANA_FILE_TEXT}" -a "${ANA_FILE}"
}


# Step 2: To check the backup work directory room
# if nfs then $1 and $4
SPACE_ROOM_CK=`df -k | grep "$BASEDIR" | awk '{print $2}'`
SPACE_USED_RATE=`df -k | grep "$BASEDIR" | awk '{print $5}' |awk -F% '{print $1}'`
if [ ${SPACE_USED_RATE} -lt ${SPACE_RATE} ] && [ ${SPACE_ROOM_CK} -gt ${SPACE_ROOM} ]
then
    echo "There have enough room for backup,let goto backup our database now" >> ${WORKPATH}/log/${BACKUP_FILE_NAME}_$WORKDATE.log
else
    echo "There have not enough room for our backup work,sadly to heard that" >> ${WORKPATH}/log/${BACKUP_FILE_NAME}_$WORKDATE.log
    echo -e "The backup task fail cause for there have not enough space room for backup on directory \nDatabase's IP is ${BACKUP_HOST}" > ${WORKPATH}/maillog/mail_dba_${WORKDATE}.log
####    my_sentmail 0
    exit 0
fi


# Step 3: To backup the database
# delete old backup data,keep 4 copys 
cd $WORKPATH/old_bk_dir
keepday=`ls -l|grep MYSQLBACKUP|wc -l`
if [ $keepday -gt 6 ]
then
 rm -fr `ls -lt|grep MYSQLBACKUP|tail -n 1|awk '{print $9}'`
fi
# move the old backup data to the old backup storage directory
cd ${WORKPATH}/new_bk_dir
mv -f MYSQLBACKUP* ${WORKPATH}/old_bk_dir
##make  dir
TMPDIR="MYSQLBACKUP"`date '+%Y%m%d'`
if [ ! -f  ${TMPDIR} ]
then
mkdir ${TMPDIR}
fi


# start backup all databases
cd $TMPDIR
for DATABASE_NAME in ${ALL_DATABASES}
do
   ALL_TABLES="$($MYSQL -u$BACKUP_USER -h$BACKUP_HOST  -p$BACKUP_PASSWD -P$BACKUP_PORT -Bse"select distinct table_name from information_schema.tables where table_schema in ('${DATABASE_NAME}')")"
   cd ${WORKPATH}/new_bk_dir/${TMPDIR}
   mkdir -p ${DATABASE_NAME}
   cd ${WORKPATH}/new_bk_dir/${TMPDIR}/${DATABASE_NAME}
   for DB_TABLE in ${ALL_TABLES}
   do
       ${MYSQLDUMP} -u${BACKUP_USER} -p${BACKUP_PASSWD}  -h${BACKUP_HOST} -P${BACKUP_PORT} --single-transaction ${DATABASE_NAME} ${DB_TABLE}> ${DATABASE_NAME}.${DB_TABLE}_${WORKDATE}.sql
   
       # compress the backup data
       ${GZIP_BIN} -c ${DATABASE_NAME}.${DB_TABLE}_${WORKDATE}.sql > ${DATABASE_NAME}.${DB_TABLE}_${WORKDATE}.gz

       # delete the uncompress backup data
       rm -f ${DATABASE_NAME}.${DB_TABLE}_${WORKDATE}.sql
       echo "${DATABASE_NAME}.${DB_TABLE} is sucessed" >> ${WORKPATH}/log/${BACKUP_FILE_NAME}_$WORKDATE.log
    done
done

echo "mysqldump_backup is ok" >> ${WORKPATH}/log/${BACKUP_FILE_NAME}_$WORKDATE.log

exit 0

根据上面日志记录,编写脚本进行钉钉和邮件报警.邮件和钉钉需要单独创建python脚本:

#!/bin/sh
set -x
#-----------------------------------------------------------
# Usage: This script is used to backup the mysql database
# Name: mysqldump_backup.sh
# Autor: xianyezhao
# modify:
# Create_date: 15/10/2019
# deploy date:20140925
#  sript location: /data/backup/mysql
#chmod mysqldump_backup.sh to 711
#-----------------------------------------------------------

. ~/.bash_profile

WORKPATH=/data/backup/mysql/table.192.168.1.200.mysqldump
WORKDATE=`date +%Y%m%d`
BACKUP_FILE_NAME="mysqldump_backup"
SHOW_HOST="192.168.1.200_qzd_dev_db"

MAIL_BIN="/usr/bin/python /home/mysql/scripts/mail/mail.py"
MAIL_TO="[email protected]"
MAIL_SUB="Error! failure! mysqldump_backup is failure Report ${SHOW_HOST}: ${DATE}"
ANA_FILE=${WORKPATH}/maillog/"maillog"${DATE}.log
ANA_FILE_TEXT=""

DINGDING_BIN="/home/mysql/scripts/notification/notification.sh"
DINGDING_TO="135*******4"
DINGDING_SUBJECT="mysqldump备份失败"
DINGDING_TEXT="192.168.1.200_qzd_dev_db的备份失败"
##end set environment

#sent mail last day perfomance alert
my_sentmail()
{
   ${MAIL_BIN}  -s "${MAIL_SUB}" -t "${MAIL_TO}" -b "${ANA_FILE_TEXT}" -a "${ANA_FILE}"
}

MYSQLDUMP_BACKUP_FLAG=`grep "mysqldump_backup is ok" ${WORKPATH}/log/${BACKUP_FILE_NAME}_$WORKDATE.log|wc -l`
SUCESSED_BACKUP_FLAG=`grep "is sucessed" ${WORKPATH}/log/${BACKUP_FILE_NAME}_$WORKDATE.log|wc -l`
if [ ${MYSQLDUMP_BACKUP_FLAG} -lt 1 ] || [ ${SUCESSED_BACKUP_FLAG} -lt 1 ] 
then
    ANA_FILE_TEXT="mysqldump is failure,Please call DBA to check "${SHOW_HOST}
    echo "mysqldump is failure,Please call DBA to check "${SHOW_HOST} >> ${WORKPATH}/maillog/"maillog"${DATE}.log
    my_sentmail
    #dingding alert 
    ${DINGDING_BIN} ${DINGDING_TO} ${DINGDING_SUBJECT} ${DINGDING_TEXT}
fi