背景介绍: 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: 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: xsbr/zabbixzone/blob/master/mysql-fullbackup.sh 备份脚本: #!/bin/bash # # mysql-fullbackup.sh # v0.1 - 20120921 # # Full Backup for Zabbix w/MySQL # # Author: Ricardo Santos ( ) # 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
|