×

扫描二维码登录本站

标签: 暂无标签
背景介绍:
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 ( )
# h
#
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





上一篇:MySQL监控:怎样在zabbix上自定义key
下一篇:运维篇--CMDB机柜平台如何结合zabbix告警展示
monicazhang

写了 2297 篇文章,拥有财富 12859,被 21 人关注

您需要登录后才可以回帖 登录 | 立即注册
B Color Link Quote Code Smilies

成为第一个吐槽的人

Powered by ITIL  © 2001-2025
返回顶部