Pada tulisan kali ini akan dijelaskan tentang cara membackup database MySQL menggunakan tools Percona-Xtrabackup.

Scenario Backup : Full dan Incremetal

Compatible : MariaDB, MySQL, Percona Server/Percona Xtradb-cluster

Percona-xtrabackup Version 2.3

Full Backup dilakukan setiap hari pukul 00:01

Incremental Backup dilakukan per 1 jam antara jam 01:00 – 23:00

Proses backup memanfaatkan crond agar dapat berjalan terjadwal

Full Backup Script

#!/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/local/script/backup_mysql/bin
TIMESTAMP=$( date +%Y_%m_%d )
BASEDIR="/usr/local/script/backup_mysql"
LOGDIR="${BASEDIR}/log"
USER="usr_backup"
PASSWORD="password123"
BASE_BACKDIR_FULL="/backup/BACKUPDIR/${TIMESTAMP}/full"
LOGFILE="${LOGDIR}/${TIMESTAMP}.fullbackup.log"

# Create log directory if not exist
/bin/mkdir -p ${LOGDIR} ${BASE_BACKDIR_FULL}

# Do full backup
/usr/bin/innobackupex --user=${USER} --password=${PASSWORD} --no-lock --port=3306 --parallel=24 --no-timestamp ${BASE_BACKDIR_FULL} 2> >(tee -a ${LOGFILE} >&2)

Karena proses backup dilakukan di master server, maka ditambahkan opsi –no-lock agar tidak terjadi locking table saat backup berlangsung serta menambahkan juga opsi –parallel untuk memanfaatkan semua core processor saat membackup, sehingga waktu backup bisa lebih cepat

Incremental Backup Script

#!/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
USER="usr_backup"
PASSWORD="password123"
TIMESTAMP=$( date +%Y_%m_%d )
BASE_BACKDIR="/backup/BACKUPDIR/${TIMESTAMP}"
BASE_BACKDIR_FULL="${BASE_BACKDIR}/full"
BASE_BACKDIR_INC="${BASE_BACKDIR}/inc_"
BASEDIR="/usr/local/script/backup_mysql"
LOGDIR="${BASEDIR}/log"

# Remove any empty incremental directory
find ${BASE_BACKDIR} -type d -empty -delete

# Create log directory if not exist
/bin/mkdir -p ${LOGDIR}

# Check incremental directory
COUNTER=$( ls ${BASE_BACKDIR} | grep -c inc_ )
COUNT=$(( ${COUNTER}+1 ))

LOGFILE="${LOGDIR}/${TIMESTAMP}.inc${COUNT}.log"

# Check if full backup directory is not exist
if [[ ! -d ${BASE_BACKDIR_FULL} ]];then
  echo "Full backup directory does not exist..Exiting" | tee -a ${LOGFILE}
  exit 1
fi

# Check if previous incremental backup process is still running
if [[ $( ps aux | grep -v grep | grep -c innobackupex ) -gt 0 ]];then
  echo "Previous backup process is still running..Exiting" | tee -a ${LOGFILE}
  exit 1
fi

# Check if copying process is still running
if [[ $( ps aux | grep -v grep | grep -c "/usr/bin/rsync -avp --progress /backup/BACKUPDIR/" ) -gt 0 ]];then
  echo "Copying process is still running..Exiting" | tee -a ${LOGFILE}
  exit 1
fi

# Set counter
BACKDIR_INC=${BASE_BACKDIR_INC}${COUNT}

# Check if directory is not corrupted
if [[ $( du -hs ${BACKDIR_INC} | awk '{print $1}' | sed 's/[^0-9]*//g' ) -le 80 ]];then
  echo "Removing corrupted incremental directory" | tee -a ${LOGFILE}
  rm -rf ${BACKDIR_INC}
fi

# Do
if [[ $COUNTER -eq 0 ]];then
  INCREMENTAL_BASEDIR=${BASE_BACKDIR_FULL}  
else
  INCREMENTAL_BASEDIR=${BASE_BACKDIR_INC}${COUNTER}
fi

# Do incremental backup
/usr/bin/innobackupex --user=${USER} --password=${PASSWORD} --no-lock --no-timestamp --parallel=24 --incremental-basedir=${INCREMENTAL_BASEDIR} --incremental $BACKDIR_INC 2> >(tee -a ${LOGFILE} >&2)

Demikian semoga bermanfaat

Advertisements