IT运维管理,ITIL,ITSS,ITSM,ISO20000-ITIL先锋论坛

 找回密码
 点击获取邀请码 - 立即注册

QQ登录

只需一步,快速开始

扫一扫,访问微社区

搜索
查看: 344|回复: 0

[Zabbix专区] Zabbix数据库的完全备份方案

[复制链接]
来自- 湖南娄底

参加活动:0

组织活动:12

发表于 2017-9-3 10:09:49 | 显示全部楼层 |阅读模式 来自- 湖南娄底
背景介绍:
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)
#
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

本版积分规则

选择云运维时代的王牌讲师-长河老师,助你轻松入门ITIL Foundation培训课程

QQ|小黑屋|手机版|Archiver|ITIL先锋论坛万人社区 ( 粤ICP备17056641号|网站地图

Baidu

GMT+8, 2018-5-28 12:51 , Processed in 0.169674 second(s), 34 queries .

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表