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 # #*********************************************************************************# # 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 |
