背景介绍：

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

This method aims to perform a full backupof a large MySQL database, as Zabbix (any version), focusing on a fast recoveryfrom 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 installXtraBackup:

http://www.percona.com/downloads/XtraBackup/LATEST/

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

Make sure that you’re using InnoDB forhistory tables at least. In my case, I have a Zabbix Database with 300GB datathat 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 atgmail.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 forrestore)

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:

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

配置定时任务：

Configure your crontab to backup every dayat 04:15am:

15 04 * * * root/var/lib/xtrabackup/mysql-fullbackup.sh >/var/lib/xtrabackup/lastrun.log2>&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

原创： Ricardo Santos

背景介绍：

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

This method aims to perform a full backupof a large MySQL database, as Zabbix (any version), focusing on a fast recoveryfrom 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 installXtraBackup:

http://www.percona.com/downloads/XtraBackup/LATEST/

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

Make sure that you’re using InnoDB forhistory tables at least. In my case, I have a Zabbix Database with 300GB datathat 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 atgmail.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 forrestore)

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:

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

配置定时任务：

Configure your crontab to backup every dayat 04:15am:

15 04 * * * root/var/lib/xtrabackup/mysql-fullbackup.sh >/var/lib/xtrabackup/lastrun.log2>&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

原创： Ricardo Santos