mysql xtrabackup(docker) 热备

@[TOC](mysql xtrabackup(docker) 热备)

一、下载镜像并运行容器

docker pull f763180872/xtrabackup

docker run -d --name mysql_backup --privileged \
-e BASE_DIR=/mnt/backup \
-e OPTION="-H127.0.0.1 -P3306 -uroot -ppasswd" \
-v /var/lib/mysql/data:/var/lib/mysql \
-v /mnt:/mnt \
--restart=always \
--net=host \
f763180872/xtrabackup

参数说明:
BASE_DIR: 备份文件存放目录
OPTION: mysql连接参数-H IP地址 -P 端口号 -u 用户名 -p 密码

备份规则为:每周三和周六凌晨三点全量备份,其余时间凌晨三点做增量更新,每次全量更新的时候,会把之前的全量与增量备份数据压缩后存放在BASE_DIR目录下,保留最近10天的备份数据,也就是最多三个全备数据

init.sh

#!/bin/bash
echo "export BASE_DIR='$BASE_DIR'" > /dockerenv
echo "export OPTION='$OPTION'" >> /dockerenv
if [ "$1" == "init" ];then
    /etc/init.d/cron start
    echo '0 3 * * 3,6 root /root/fullbak.sh > /var/log/mysql_backup.log 2>&1' >> /etc/crontab
    echo '0 */2 * * 1,2,4,5,7 root /root/incrbak.sh >> /var/log/mysql_backup.log 2>&1' >> /etc/crontab
    /root/fullbak.sh > /var/log/mysql_backup.log 2>&1
    tail -f /dev/null
else
    exec "$@"
fi

fullback.sh

#!/bin/bash

# docker需要判断环境变量文件是否存在
if [ -f /dockerenv ];then
  source /dockerenv
  env
fi

INNOBACKUPEXFULL=/usr/bin/innobackupex
TODAY=`date +%Y%m%d%H%M`
YESTERDAY=`date -d"yesterday" +%Y%m%d%H%M`

FULLBACKUPDIR=$BASE_DIR/full # 全库备份的目录
INCRBACKUPDIR=$BASE_DIR/incr # 增量备份的目录
TMPFILEDIR=$BASE_DIR/logs # 日志目录
TMPFILE="$TMPFILEDIR/innobackup_$TODAY.$$.log" # 日志文件

#############################################################################
# 打印错误信息并退出
#############################################################################
error()
{
    echo "$1" 1>&2
    exit 1
}
 
# 开始备份前检查相关的参数
if [ ! -x $INNOBACKUPEXFULL ]; then
  error "$INNOBACKUPEXFULL does not exist."
fi
 
#if [ ! -d $BASE_DIR ]; then
#  error "Backup destination folder: $BASE_DIR does not exist."
#fi
 
# 输出备份信息
echo "----------------------------"
echo
echo "$0: MySQL backup script"
echo "started: `date '+%Y-%m-%d %H:%M:%S'`"
echo
 
# 如果备份目录不存在则创建相应的全备增备目录
for i in $FULLBACKUPDIR $INCRBACKUPDIR $TMPFILEDIR; do
  if [ ! -d $i ]; then
    mkdir -pv $i
  fi
done
 
# 压缩上传前一天的备份
echo "压缩前一天的备份"
cd $BASE_DIR
tar -zcf $YESTERDAY.tar.gz ./full/ ./incr/
# scp -P 8022 $YESTERDAY.tar.gz root@192.168.10.46:/data/backup/mysql/
#if [ $? = 0 ]; then
rm -rf $FULLBACKUPDIR $INCRBACKUPDIR
echo "开始全量备份"
echo "start exec $INNOBACKUPEXFULL $OPTION $FULLBACKUPDIR > $TMPFILE 2>&1"
$INNOBACKUPEXFULL $OPTION $FULLBACKUPDIR > $TMPFILE 2>&1
#else
#  echo "远程备份失败"
#fi
 
if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then
 echo "$INNOBACKUPEXFULL failed:"; echo
 echo "---------- ERROR OUTPUT from $INNOBACKUPEXFULL ----------"
# cat $TMPFILE
# rm -f $TMPFILE
 exit 1
fi

# 这里获取这次备份的目录 
THISBACKUP=`awk -- "/Backup created in directory/ { split( \\\$0, p, \"'\" ) ; print p[2] }" $TMPFILE`
echo "THISBACKUP=$THISBACKUP"
#rm -f $TMPFILE
echo "Databases backed up successfully to: $THISBACKUP"

# Cleanup
echo "delete tar files of 10 days ago"
find $BASE_DIR/ -mtime +10 -name "*.tar.gz"  -exec rm -rf {} \;
 
echo
echo "completed: `date '+%Y-%m-%d %H:%M:%S'`"
exit 0

incrback.sh

#!/bin/bash

# docker需要判断环境变量文件是否存在
if [ -f /dockerenv ];then
  source /dockerenv
  env
fi

INNOBACKUPEXFULL=/usr/bin/innobackupex
TODAY=`date +%Y%m%d%H%M`
YESTERDAY=`date -d"yesterday" +%Y%m%d%H%M`
#OPTION="-H172.21.0.2 -uroot -p1234"
#BASE_DIR=/mnt/mysql_backup/mysql_data # 备份的主目录
#SCP_IP="10.0.0.18 10.0.0.19" # 备份同步目录
FULLBACKUPDIR=$BASE_DIR/full # 全库备份的目录
INCRBACKUPDIR=$BASE_DIR/incr # 增量备份的目录
TMPFILEDIR=$BASE_DIR/logs # 日志目录
TMPFILE="$TMPFILEDIR/innobackup_$TODAY.$$.log" # 日志文件

#############################################################################
# 打印错误信息并退出
#############################################################################
error()
{
    echo "$1" 1>&2
    exit 1
}
 
# 开始备份前检查相关的参数
if [ ! -x $INNOBACKUPEXFULL ]; then
  error "$INNOBACKUPEXFULL does not exist."
fi
 
#if [ ! -d $BASE_DIR ]; then
#  error "Backup destination folder: $BASE_DIR does not exist."
#fi
 
# 输出备份信息
echo "----------------------------"
echo
echo "$0: MySQL backup script"
echo "started: `date '+%Y-%m-%d %H:%M:%S'`"
echo "exec $INNOBACKUPEXFULL $OPTION $FULLBACKUPDIR > $TMPFILE 2>&1"
echo
 
# 如果备份目录不存在则创建相应的全备增备目录
for i in $FULLBACKUPDIR $INCRBACKUPDIR $TMPFILEDIR; do
  if [ ! -d $i ]; then
    mkdir -pv $i
  fi
done
 
# 查找最近的全备目录
LATEST_FULL=`find $FULLBACKUPDIR -mindepth 1 -maxdepth 1 -type d -printf "%P\n"`
echo "最近的全备目录为: $LATEST_FULL" 

# 如果最近的全备仍然可用执行增量备份
# 创建增量备份的目录
TMPINCRDIR=$INCRBACKUPDIR/$LATEST_FULL
mkdir -p $TMPINCRDIR
BACKTYPE="incr"
# 获取最近的增量备份目录
LATEST_INCR=`find $TMPINCRDIR -mindepth 1 -maxdepth 1 -type d | sort -nr | head -1`
echo "最近的增量备份目录为: $LATEST_INCR"
# 如果是首次增量备份,那么basedir则选择全备目录,否则选择最近一次的增量备份目录
if [ ! $LATEST_INCR ] ; then
  INCRBASEDIR=$FULLBACKUPDIR/$LATEST_FULL
else
  INCRBASEDIR=$LATEST_INCR
fi
echo "Running new incremental backup using $INCRBASEDIR as base."
echo "start exec $INNOBACKUPEXFULL $OPTION --incremental $TMPINCRDIR --incremental-basedir $INCRBASEDIR > $TMPFILE 2>&1"
$INNOBACKUPEXFULL $OPTION --incremental $TMPINCRDIR --incremental-basedir $INCRBASEDIR > $TMPFILE 2>&1

 
if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then
 echo "$INNOBACKUPEX failed:"; echo
 echo "---------- ERROR OUTPUT from $INNOBACKUPEX ----------"
 exit 1
fi

# 这里获取这次备份的目录 
THISBACKUP=`awk -- "/Backup created in directory/ { split( \\\$0, p, \"'\" ) ; print p[2] }" $TMPFILE`
echo "THISBACKUP=$THISBACKUP"
echo
echo "Databases backed up successfully to: $THISBACKUP"

echo
echo "incremental completed: `date '+%Y-%m-%d %H:%M:%S'`"
exit 0

上一篇:String.split()函数


下一篇:0001导出CSV 和EXEL 的问题变成1