Confluence mysql percona backup 설정

percona repository 인스톨

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

yum install http://repo.percona.com/centos/7/RPMS/x86_64/Percona-Server-selinux-56-5.6.42-rel84.2.el7.noarch.rpm

xtrabackup 인스톨

yum install percona-xtrabackup-22

cd /usr/local/mysql

mkdir backup_logs backup_tmp bak support-files

 

innobackupex.sh

#!/bin/bash
 
source ~/.bash_profile
 
## -----------------------------------------------------------------------------
## Set variables
## -----------------------------------------------------------------------------
STIME=`date "+%Y-%m-%d %T"`
SSEC=`date +%s`
 
SERVICE_NAME=infraDB
HOST_NAME=`hostname | awk '{ split($1,array,"."); print array[1]}'`
USER_NAME=backupuser
USER_PWD=qordjqdhkd!
MYSQL_CNF=/etc/my.cnf
OPT_CNF=--defaults-file=${MYSQL_CNF}
OPT_SLAVE=--slave-info
OPT_SAFE_SLAVE=--safe-slave-backup
#LOCK_WAIT_TIMEOUT="--lock-wait-timeout=2400"
#LOCK_WAIT_THRESHOLD="--lock-wait-threshold=30"
#KILL_LONG_QUERIES_TIMEOUT="--kill-long-queries-timeout=20"
#KILL_LONG_QUERIES_TYPE="--kill-long-query-type=select"
OPT_STREAM=--stream=xbstream
OPT_COMPRESS=--compress
OPT_PV=
TEMP_DIR=/usr/local/mysql/backup_tmp/
BACKUP_DIR=/usr/local/mysql/bak/
BACKUP_TIME=`date +%Y%m%d_%H%M%S`
BACKUP_FILE_SURFFIX=
BACKUP_RTN_DAYS=2
 
if [ "${OPT_STREAM}" == "--stream=xbstream" ]; then
    BACKUP_FILE_SURFFIX=${BACKUP_FILE_SURFFIX}".xbstream"
 
    if [ "${OPT_COMPRESS}" == "--compress" ]; then
        BACKUP_FILE_SURFFIX=${BACKUP_FILE_SURFFIX}".cp"
    fi
fi
 
BACKUP_FILE=${SERVICE_NAME}_${HOST_NAME}_${BACKUP_TIME}${BACKUP_FILE_SURFFIX}
 
echo
echo "*****************************************************************"
echo "* Service Name        :" ${SERVICE_NAME}
echo "* Host Name           :" ${HOST_NAME}
echo "* Backup User         :" ${USER_NAME}
echo "* Backup Option       :" ${OPT_SLAVE} ${OPT_STREAM} ${OPT_COMPRESS} ${OPT_PV}
echo "* Temporary Directory :" ${TEMP_DIR}
echo "* SSH Remote Access   :" ${REMOTE_USER}@${REMOTE_HOST}
echo "* Backup file Name    :" ${REMOTE_PASS}${BACKUP_FILE}
echo "*****************************************************************"
echo
 
## -----------------------------------------------------------------------------
## Backup
## -----------------------------------------------------------------------------
 
echo "Starting Copy my.cnf..."
echo "cp -f ${MYSQL_CNF} ${BACKUP_DIR}${SERVICE_NAME}_${HOST_NAME}_${BACKUP_TIME}_my.cnf"
cp -f ${MYSQL_CNF} ${BACKUP_DIR}${SERVICE_NAME}_${HOST_NAME}_${BACKUP_TIME}_my.cnf
 
echo "Starting Backup Using ExtraBackup..."
echo "innobackupex ${OPT_CNF} --user=${USER_NAME} --password=${USER_PWD} ${LOCK_WAIT_TIMEOUT} ${LOCK_WAIT_THRESHOLD} ${KILL_LONG_QUERIES_TIMEOUT} ${KILL_LONG_QUERIES_TYPE} ${OPT_SLAVE} ${OPT_SAFE_SLAVE} ${OPT_STREAM} ${OPT_COMPRESS} ${BACKUP_DIR} ${OPT_PV} > ${BACKUP_DIR}${BACKUP_FILE}"
 
innobackupex ${OPT_CNF} --user=${USER_NAME} --password=${USER_PWD} ${LOCK_WAIT_TIMEOUT} ${LOCK_WAIT_THRESHOLD} ${KILL_LONG_QUERIES_TIMEOUT} ${KILL_LONG_QUERIES_TYPE} ${OPT_SLAVE} ${OPT_SAFE_SLAVE} ${OPT_STREAM} ${OPT_COMPRESS} ${BACKUP_DIR} ${OPT_PV} > ${BACKUP_DIR}${BACKUP_FILE}
 
 
##echo "Create TAR file from backup..."
##TAR_FILE_NAME="${BACKUP_DIR}backup_"`date "+%Y%m%d_%H%M%S"`.tar
##echo "cd ${TEMP_DIR}"
##cd ${TEMP_DIR}
##echo "tar -cvf ${TAR_FILE_NAME} ./*"
##tar -cvf ${TAR_FILE_NAME} ./*
##echo "rm -rf ${TEMP_DIR}*"
##rm -rf ${TEMP_DIR}*
 
echo "Copy a bakcup file into a remote storage..."
export AWS_CONFIG_FILE=/etc/awscli.conf
echo "aws s3 cp ${BACKUP_DIR}${BACKUP_FILE} s3://cocone-backup/portaldb/mysql/"
aws s3 cp ${BACKUP_DIR}${BACKUP_FILE} s3://cocone-backup/portaldb/mysql/
 
echo "Delete Old backup files and directories ${BACKUP_DIR}"
echo "find /usr/local/mysql/bak/ -ctime +${BACKUP_RTN_DAYS} -delete"
find /usr/local/mysql/bak/ -ctime +${BACKUP_RTN_DAYS} -delete
#find /usr/local/mysql/bak/ -delete
 
ETIME=`date "+%Y-%m-%d %T"`
ESEC=`date +%s`
RSEC=`echo "$ESEC - $SSEC" | bc`
 
echo
echo "*** Start Time  : " $STIME
echo "*** End Time    : " $ETIME
echo "*** Run Time    : " $RSEC "sec"

 

crontab 설정

0 4 * * * /usr/local/mysql/support-files/innobackupex.sh > /usr/local/mysql/backup_logs/innobackupex_`date +\%Y\%m\%d_\%H\%M\%S`.log 2>&1