MySQL Innodb Backup/Restore script using Percona Xtrabackup

In the previous article we saw how we could install and make use of the Percona Xtrabackup backup utility for MySQL. Here is a script that will create a full database backup of you Innodb engine schemas(tables) without locking any of them and with no down time. Is important to know that the script has 2 steps, one will create the initial backup piece and the second step will consolidate the initial backup by applying the log so it become "RESTORE READY" or in other words consistent. So in case you loose the database recovery using this type of backup will not need any logs to be applied to it.

 

Here is the Backup script
  • use it as template and alter the variables as per your needs
#!/bin/bash
# full-bkp.sh
# Full Backup

MYSQLUSER="user"
MYSQLPASS="password"
MYSQLSOCK="/var/lib/sock1.sock"

MYSQLCNF="/opt/mysql/5.6.16/dbs/my.cnf"
MYSQLDIR="/opt/mysql/5.6.16/usr"
MYSQLLOG="/u02/mylog"

BASEDIR="/u01/mybackup"
BKPDIR="${BASEDIR}/lastbackup"
BKPTEMPDIR="${BASEDIR}/tempbackup"

#Memory allocated for step 2
USEMEMORY="1GB"

# create bkp directory
mkdir -p ${BASEDIR}

# delete temp directory
if [ -d "${BKPTEMPDIR}" ]; then
 rm -rf ${BKPTEMPDIR}
fi

#Execute backup - Step 1
innobackupex --defaults-file=${MYSQLCNF} --user=${MYSQLUSER} --no-timestamp --socket=${MYSQLSOCK} --password=${MYSQLPASS} ${BKPTEMPDIR}

#Execute Backup - Step 2 (will apply all log and will make you backup restoure ready /no recover nedeed )
innobackupex --apply-log --use-memory=${USEMEMORY} ${BKPTEMPDIR}

#Cleanup already applied bin logs andleav only the last 8 generated logs
ls -1 ${MYSQLLOG}| grep log_bin. | tail -8| tac | tail -1 | awk -v b="'" {'print "purge binary logs to "b$1b";"'}| mysql -u${MYSQLUSER} -p${MYSQLPASS} --socket=${MYSQLSOCK}
# backup my.cnf
cp -pf ${MYSQLCNF} ${BKPTEMPDIR}/my.cnf


#Delete old backup and keep only one copy(the actual)
if [ -d "${BKPDIR}" ]; then
 if [ -d "${BKPDIR}.old" ]; then
 rm -rf ${BKPDIR}.old
 fi
 rm -rf ${BKPDIR}
fi
chown -R mysql:mysql ${BKPTEMPDIR}
mv ${BKPTEMPDIR} ${BKPDIR}

  

Now let's create the Restore Script Very important:
  • before you plan to restore you backup using xtrabackup utility make sure you do the following
  • 1 - Stop you MySQL instance
  • 2 - Remove all files located in your  "datadir"  MySQL variable(is where your data files are locates)
  • 3 - run the Restore command(will copy all the backup to the locations stated in the my.cnf file)
  • 4 - start you MySQL instance
#!/bin/bash
# restore.sh
MYSQLCNF="/opt/mysql/5.6.16/dbs/my.cnf"
MYBKP="/u01/mybackup/lastbackup"

innobackupex --copy-back --defaults-file=${MYSQLCNF} ${MYBKP}

  

I hope this was helpful. Fell free to drop a comment with your opinion.

Copyright 2020. All Rights Reserved