Backuping Full Database

Posted by Ricardo Santos in backup, database, mysql, scripts, zabbixon Sep 21st, 2012 | 3 comments

Dump doesn’t work. If you have huge tables with billions of rows your backup process could become a nightmare, mainly if you need to restore it.

This method aims to perform a full backup of a large MySQL database, as Zabbix (any version), focusing on a fast recovery from disaster. So I chose XtraBackup for this task, a backup tool from Percona, which works using the hotcopy mode.

First, you need to download and install XtraBackup:
http://www.percona.com/downloads/XtraBackup/LATEST/

XtraBackup offers a lot of parameters, so this script is intented to be the simplest possible.

Make sure that you’re using InnoDB for history tables at least. In my case, I have a Zabbix Database with 300GB data that takes about 3 hours to do all backup.

Create script /var/lib/xtrabackup/mysql-fullbackup.sh:

https://github.com/xsbr/zabbixzone/blob/master/mysql-fullbackup.sh

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49

#!/bin/bash # # mysql-fullbackup.sh # v0.1 - 20120921 # # Full Backup for Zabbix w/MySQL # # Author: Ricardo Santos (rsantos at gmail.com) # http://zabbixzone.com # MYSQLUSER="YOURUSER" MYSQLPASS="YOURPASSWORD"   MYSQLCNF="/etc/my.cnf" MYSQLDIR="/var/lib/mysql"   BASEDIR="/var/lib/xtrabackup" BKPDIR="${BASEDIR}/lastbackup" BKPTEMPDIR="${BASEDIR}/tempbackup"   # Memory used in stage 2 USEMEMORY="1GB"   # create basedir mkdir -p ${BASEDIR}   # remove temporary dir if [ -d "${BKPTEMPDIR}" ]; then         rm -rf ${BKPTEMPDIR} fi   # do backup - stage 1 innobackupex --defaults-file=${MYSQLCNF} --user=${MYSQLUSER} --no-timestamp --password=${MYSQLPASS} ${BKPTEMPDIR}   # do backup - stage 2 (prepare backup for restore) innobackupex --apply-log --use-memory=${USEMEMORY} ${BKPTEMPDIR}   # backup my.cnf cp -pf ${MYSQLCNF} ${BKPTEMPDIR}/my.cnf   # keep only the lastbackup if [ -d "${BKPDIR}" ]; then       if [ -d "${BKPDIR}.old" ]; then               rm -rf ${BKPDIR}.old       fi       rm -rf ${BKPDIR} fi chown -R mysql: ${BKPTEMPDIR} mv ${BKPTEMPDIR} ${BKPDIR}

Adjust the permissions:

1	chmod +x /var/lib/xtrabackup/mysql-fullbackup.sh

Configure your crontab to backup every day at 04:15am:

1	15 04 * * * root /var/lib/xtrabackup/mysql-fullbackup.sh >/var/lib/xtrabackup/lastrun.log 2>&1

So if you need a restore, it’s very simple:

1 2 3 4 5 6 7 8 9 10

# stop MySQL service mysql stop   # move backuped files cd /var/lib mv mysql mysqlcrashed mv xtrabackup/lastbackup mysql   # start MySQL service mysql start