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

#!/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:

# stop MySQL
service mysql stop


# move backuped files
cd /var/lib
mv mysql mysqlcrashed
mv xtrabackup/lastbackup mysql


# start MySQL
service mysql start

1 2 3 4 5 6 7 8 9 10

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