MySQL 使用XtraBackup的shell脚本介绍
2024-10-13 15:43:36
mysql_backup.sh是关于MySQL的一个使用XtraBackup做备份的shell脚本，实现了简单的完整备份和增量备份、以及邮件发送备份信息等功能。功能目前还比较简单，后续将继续完善和增加相关功能。参考了网上MySQL备份的脚本，如有不足的地方，欢迎大家拍砖！
1： 使用前，请先做一些配置选项修改，例如备份路径设置、连接数据库的账号密码等等。
2： 在BACKUP_DIR目录下，创建full、incr、logs、scripts四个目录，将mysql_backup.sh放置在scripts目录下。
#!/bin/bash
###################################################################################
# This script is used for MySQL daily backup. it is a package of Xtrabackup shell # script #
# #
# #################################################################################
# #
# ScriptName : mysql_backup.sh #
# Author : 潇湘隐者 #
# CerateDate : 2016-04-24 #
# Blogs : www.cnblogs.com/kerrycode #
# @author : kerry2008code@qq.com #
#*********************************************************************************#
# Version Modified Date Description #
#*********************************************************************************#
# V.1.0 2016-04-24 create the script for mysql daily backup #
###################################################################################
BACKUP_DATE=$(date +%Y-%m-%d_%H_%M_%S)
BACKUP_DIR=/backup/mysql
FULLBACKUP_PATH=$BACKUP_DIR/full
INCRBACKUP_PATH=$BACKUP_DIR/incr
BACKUP_LOG_PATH=$BACKUP_DIR/logs
BACKUP_KEEP_DAY=7
MYSQL_CONF=/usr/my.cnf
INNOBACKUPEX=/usr/bin/innobackupex
MYSQL_CMD=/usr/bin/mysql
MYSQL_CONNECT="--host=127.0.0.1 --user=root --password=123456 --port=3306"
MAIL_FROM="root@`hostname`"
MAIL_TO=konglb@xxx.com
error()
{
echo "$1" 1>&2
exit 1
}
#before the backup, check the system enviroment setting and mysql status and so on
mysql_backup_check()
{
if [ ! -d $FULLBACKUP_PATH ];then
mkdir -p $FULLBACKUP_PATH
fi
if [ ! -d $INCRBACKUP_PATH ];then
mkdir -p $INCRBACKUP_PATH
fi
if [ ! -d $BACKUP_LOG_PATH ];then
mkdir -p $BACKUP_LOG_PATH
fi
if [ ! -x $INNOBACKUPEX ];then
error "$INNOBACKUPEX did not exists"
fi
if [ ! -x $MYSQL_CMD ];then
error "mysql client did not exists!"
fi
mysql_status=`netstat -nl | awk 'NR>2{if ($4 ~ /.*:3306/) {print "Yes";exit 0}}'`
if [ "$mysql_status" != "Yes" ];then
error "MySQL did not start. please check it"
fi
if ! `echo 'exit' | $MYSQL_CMD -s $MYSQL_CONNECT` ; then
error "please check the user and password is correct!"
fi
}
xtra_backup()
{
if [ $# = 2 ];then
$INNOBACKUPEX --defaults-file=$MYSQL_CONF $MYSQL_CONNECT --no-timestamp $1/full_$BACKUP_DATE>$2 2>&1
elif [ $# = 3 ];then
$INNOBACKUPEX --defaults-file=$MYSQL_CONF $MYSQL_CONNECT --no-timestamp --incremental $1/incr_$BACKUP_DATE --incremental-basedir $2 >$3 2>&1
else
error "the parameter is not correct"
fi
}
lastest_fullback_dir()
{
if [ -d $1 ]; then
path=`ls -t $1 |head -n 1`
if [ $path ]; then
echo $path
else
error "lastest_fullback_dir(): 目录为空,没有最新目录"
fi
else
error "lastest_fullback_dir(): 目录不存在或者不是目录"
fi
}
mysql_full_backup()
{
xtra_backup $FULLBACKUP_PATH $BACKUP_LOG_PATH/full_$BACKUP_DATE.log
if [ "${PIPESTATUS[0]}" -ne 0 ]; then
(echo "Subject: MySQL Backup Failed: MySQL Backup failed on `hostname`";
cat ${BACKUP_LOG_PATH}/full_${BACKUP_DATE}.log;
) | /usr/sbin/sendmail -O NoRecipientAction=add-to -f${MAIL_FROM} ${MAIL_TO}
else
(echo "Subject: MySQL Backup Success: MySQL Backup failed on `hostname`";
cat ${BACKUP_LOG_PATH}/full_${BACKUP_DATE}.log;
) | /usr/sbin/sendmail -O NoRecipientAction=add-to -f${MAIL_FROM} ${MAIL_TO}
fi
cd $FULLBACKUP_PATH
ls -t | tail -n +$BACKUP_KEEP_DAY | xargs rm -rf
}
mysql_incr_backup()
{
LATEST_FULL_BACKUP=`find $FULLBACKUP_PATH -mindepth 1 -maxdepth 1 -type d -printf "%P\n" | sort -nr | head -1`
LATEST_FULL_BACKUP_TIME=`stat -c %Y $FULLBACKUP_PATH/$LATEST_FULL_BACKUP`
if [ $LATEST_FULL_BACKUP ];then
#
xtra_backup $INCRBACKUP_PATH $FULLBACKUP_PATH/`lastest_fullback_dir $FULLBACKUP_PATH` $BACKUP_LOG_PATH/incr_$BACKUP_DATE.log
else
# the first incremental backup need do full backup first
xtra_backup $FULLBACKUP_PATH $BACKUP_LOG_PATH/full_$BACKUP_DATE.log
if [ "${PIPESTATUS[0]}" -ne 0 ]; then
(echo "Subject: MySQL Backup Failed: MySQL Backup failed on `hostname`";
cat ${BACKUP_LOG_PATH}/incr_${BACKUP_DATE}.log;
) | /usr/sbin/sendmail -O NoRecipientAction=add-to -f${MAIL_FROM} ${MAIL_TO}
fi
fi
}
case $1 in
full)
mysql_backup_check
mysql_full_backup
;;
incr)
mysql_backup_check
mysql_incr_backup
;;
*)
echo "full backup parameter missed!"
echo "incr backup parameter missed!"
;;
esac
然后根据备份策略设置crontab作业,例如如下所示
-----------------------------------------------------------------分割线------------------------------------------------------------------------------
在使用这个脚本中,发现了一个小bug,没有定期清理增量备份的历史备份,修正后的脚本如下所示.
#!/bin/bash
#################################################################################
# #
# This script is used for MySQL daily backup. it is a package of Xtrabackup shell script #
# #
# ################################################################################
# #
# ScriptName : mysql_backup.sh #
# Author : Kerry #
# CerateDate : 2016-04-24 #
# Blogs : www.cnblogs.com/kerrycode #
# @author : kerry2008code@qq.com #
#********************************************************************************#
# Version Modified Date Description #
#********************************************************************************#
# V.1.0 2016-04-24 create the script for mysql daily backp #
# V.1.1 2016-07-26 fix the bug increase backup did not rm the old backupset#
##################################################################################
BACKUP_DATE=$(date +%Y-%m-%d_%H_%M_%S)
BACKUP_DIR=/backup/mysql/mysql_db_backup
FULLBACKUP_PATH=$BACKUP_DIR/full
INCRBACKUP_PATH=$BACKUP_DIR/incr
BACKUP_LOG_PATH=$BACKUP_DIR/logs
BACKUP_KEEP_DAY=30
MYSQL_CONF=/usr/my.cnf
INNOBACKUPEX=/usr/bin/innobackupex
MYSQL_CMD=/usr/bin/mysql
MYSQL_CONNECT="--host=127.0.0.1 --user=root --password=xxx--port=3306"
MAIL_FROM="root@`hostname`"
MAIL_TO="xxx@xxxx.com"
MAIL_TO_ERROR="xxxx@xxxx.com"
error()
{
echo "$1" 1>&2
exit 1
}
#before the backup, check the system enviroment setting and mysql status and so on
mysql_backup_check()
{
if [ ! -d $FULLBACKUP_PATH ];then
mkdir -p $FULLBACKUP_PATH
fi
if [ ! -d $INCRBACKUP_PATH ];then
mkdir -p $INCRBACKUP_PATH
fi
if [ ! -d $BACKUP_LOG_PATH ];then
mkdir -p $BACKUP_LOG_PATH
fi
if [ ! -x $INNOBACKUPEX ];then
error "$INNOBACKUPEX did not exists"
fi
if [ ! -x $MYSQL_CMD ];then
error "mysql client did not exists!"
fi
mysql_status=`netstat -nl | awk 'NR>2{if ($4 ~ /.*:3306/) {print "Yes";exit 0}}'`
if [ "$mysql_status" != "Yes" ];then
error "MySQL did not start. please check it"
fi
if ! `echo 'exit' | $MYSQL_CMD -s $MYSQL_CONNECT` ; then
error "please check the user and password is correct!"
fi
}
xtra_backup()
{
if [ $# = 2 ];then
$INNOBACKUPEX --defaults-file=$MYSQL_CONF $MYSQL_CONNECT --no-timestamp $1/full_$BACKUP_DATE>$2 2>&1
elif [ $# = 3 ];then
$INNOBACKUPEX --defaults-file=$MYSQL_CONF $MYSQL_CONNECT --no-timestamp --incremental $1/incr_$BACKUP_DATE --incremental-basedir $2 >$3 2>&1
else
error "the parameter is not correct"
fi
}
lastest_fullback_dir()
{
if [ -d $1 ]; then
path=`ls -t $1 |head -n 1`
if [ $path ]; then
echo $path
else
error "lastest_fullback_dir(): 目录为空,没有最新目录"
fi
else
error "lastest_fullback_dir(): 目录不存在或者不是目录"
fi
}
mysql_full_backup()
{
xtra_backup $FULLBACKUP_PATH $BACKUP_LOG_PATH/full_$BACKUP_DATE.log
if [ "${PIPESTATUS[0]}" -ne 0 ]; then
(echo "Subject: MySQL Backup Failed: MySQL Backup failed on `hostname`";
cat ${BACKUP_LOG_PATH}/full_${BACKUP_DATE}.log;
) | /usr/sbin/sendmail -O NoRecipientAction=add-to -f${MAIL_FROM} ${MAIL_TO}
else
(echo "Subject: MySQL Backup Success: MySQL Backup Succeed on `hostname`";
cat ${BACKUP_LOG_PATH}/full_${BACKUP_DATE}.log;
) | /usr/sbin/sendmail -O NoRecipientAction=add-to -f${MAIL_FROM} ${MAIL_TO}
fi
#cd $FULLBACKUP_PATH
#ls -t | tail -n +$BACKUP_KEEP_DAY | xargs rm -rf
find ${FULLBACKUP_PATH} -mtime +$BACKUP_KEEY_DAY -name "*" -exec rm -rf {} \;
}
mysql_incr_backup()
{
LATEST_FULL_BACKUP=`find $FULLBACKUP_PATH -mindepth 1 -maxdepth 1 -type d -printf "%P\n" | sort -nr | head -1`
LATEST_FULL_BACKUP_TIME=`stat -c %Y $FULLBACKUP_PATH/$LATEST_FULL_BACKUP`
if [ $LATEST_FULL_BACKUP ];then
#不是第一次增量备份，以最新的增量备份目录为base_dir
xtra_backup $INCRBACKUP_PATH $FULLBACKUP_PATH/`lastest_fullback_dir $FULLBACKUP_PATH` $BACKUP_LOG_PATH/incr_$BACKUP_DATE.log
else
# the first incremental backup need do full backup first
xtra_backup $FULLBACKUP_PATH $BACKUP_LOG_PATH/full_$BACKUP_DATE.log
if [ "${PIPESTATUS[0]}" -ne 0 ]; then
(echo "Subject: MySQL Backup Failed: MySQL Backup failed on `hostname`";
cat ${BACKUP_LOG_PATH}/incr_${BACKUP_DATE}.log;
) | /usr/sbin/sendmail -O NoRecipientAction=add-to -f${MAIL_FROM} ${MAIL_TO}
fi
fi
#cd ${INCRBACKUP_PATH}
#ls -t | tail -n +$BACKUP_KEEP_DAY | xargs rm -rf
find ${INCRBACKUP_PATH} -mtime +$BACKUP_KEEY_DAY -name "*" -exec rm -rf {} \;
}
case $1 in
full)
mysql_backup_check
mysql_full_backup
;;
incr)
mysql_backup_check
mysql_incr_backup
;;
*)
echo "full backup"
echo "incr backup"
;;
esac
最新文章
- Flask源码学习—config配置管理
- Swift学习--常量.变量.数据类型的使用(二)
- 【转】SQL Server 数据库内部版本号
- KVC与KVO的理解
- 一天搞定HTML----常用标签01
- vue 2.0 scopedSlots和slots在render函数中的应用示例
- LineRenderer实现一个画线组件
- 【学亮IT手记】使用Map代替switch...case语句
- cstdlib和stdlib.h区别
- TCP粘包，拆包及解决方法
- 003-hive安装
- 14-matlab排序
- 【TJOI2015】线性代数
- HTML(2)普通文本的修饰
- Picasso：开启大前端的未来
- Java笔记-IO流的运用
- javascript获取文件后缀名
- iOS项目开发优秀文章汇总
- .NET开源工作流RoadFlow-表单设计-数据表格
- ios 加密解密（包括base64，DES）非原创