当前位置: 代码迷 >> MySQL >> xtrabackup对MySQL进展备份和恢复
  详细解决方案

xtrabackup对MySQL进展备份和恢复

热度:161   发布时间:2016-05-05 16:38:09.0
xtrabackup对MySQL进行备份和恢复

对MySQL数据库的备份与恢复是非常有必要的,它可以用很多种方法来实现,本文我们主要介绍了使用Xtrabackup来进行备份的全部过程,接下来就让我们一起来了解一下这部分内容。

Xtrabackup是percona公司的开源项目,用以实现类似innodb官方的热备份工具InnoDB Hot Backup的功能,能够非常快速地备份与恢复mysql数据库。 Xtrabackup中包含两个工具:

xtrabackup是用于热备份innodb, xtradb表中数据的工具,不能备份其他类型的表,也不能备份数据表结构;

innobackupex是将xtrabackup进行封装的perl脚本,提供了备份myisam表的能力。

由于innobackupex的功能更为全面和完善,所以,本文以innobackupex作为基础进行研究描述。

?

下载和安装

在以下地址可以下载到xtrabackup:http://www.percona.com/downloads/XtraBackup/,可以根据自己的需要选择稳定版本或者最新版本以及操作系统、源码包或者rpm包等等。

?

其中,

innobackupex是我们要使用的备份工具;

xtrabackup是被封装在innobackupex之中的,innobackupex运行时需要调用它;

xtrabackup_51是xtrabackup运行时需要调用的工具;

tar4ibd是以tar流的形式产生备份时用来打包的工具。

?

?

以下是几个备份小脚本,仅供参考:

1.完整备份

#!/bin/bash

#

DATE=`date "+%F"`

User="root"

Passwd="mysql"

IP="localhost"

databak_dir="/mysqlbackup/full/$DATE" ? #备份的目录

eMailFile=/mysqlbackup/full/email.txt

[email protected]

logFile=/mysqlbackup/full/logs/mysql-$DATE.log

Database="test_db"

?

if [ ! -d $databak_dir ] ; then

? mkdir -p $databak_dir

fi

?

echo " ? ? " > $eMailFile

echo "---------------------------------" >> $eMailFile

echo $(date +"%y-%m-%d %H:%M:%S") >> $eMailFile

?

/usr/bin/innobackupex --user=$User --password=$Passwd --defaults-file=/etc/my.cnf --database=$Database ?$databak_dir >> $eMailFile 2>&1

ls -d /mysqlbackup/full/$DATE > /mysqlbackup/full/1.txt

ls /mysqlbackup/full/$DATE > /mysqlbackup/full/2.txt

?

if [[ $? == 0 ]]; then

? ? ? echo "BackupFileName:$Database" >> $eMailFile

? ? ? echo "DataBase Backup Success" >> $eMailFile

? ? else

? ? ? echo "DataBase Backup Fail!" >> $eMailFile

? ? ? mail -s " DataBase Backup Fail " $eMail < $eMailFile ?#如果备份不成功发送邮件通知

? fi

echo "--------------------------------------------------------" >> $logFile

cat $eMailFile >> $logFile

find /mysqlbackup/full/ -name "*" -mtime +30 |xargs rm -rf

?

?

2.增量备份

#!/bin/bash

#

DATE=`date "+%F"`

User="root"

Passwd="mysql"

IP="localhost"

databak_dir="/mysqlbackup/increment/$DATE" ? #备份的目录

eMailFile=/mysqlbackup/increment/email.txt

[email protected]

logFile=/mysqlbackup/increment/logs/mysql-$DATE.log

Database="test_db"

FDIR1=`cat /mysqlbackup/full/1.txt`

FDIR2=`cat /mysqlbackup/full/2.txt`

?

if [ ! -d $databak_dir ] ; then

? mkdir -p $databak_dir

fi

?

echo " ? ? " > $eMailFile

echo "---------------------------------" >> $eMailFile

echo $(date +"%y-%m-%d %H:%M:%S") >> $eMailFile

?

/usr/bin/innobackupex --user=$User --password=$Passwd --database=$Database --incremental --incremental-basedir=$FDIR1/$FDIR2/ $databak_dir >> $eMailFile 2>&1

ls -d /mysqlbackup/increment/$DATE > /mysqlbackup/increment/1.txt

ls /mysqlbackup/increment/$DATE > /mysqlbackup/increment/2.txt

?

if [[ $? == 0 ]]; then

? ? ? echo "BackupFileName:$Database" >> $eMailFile

? ? ? echo "DataBase Increment Backup Success" >> $eMailFile

? ? else

? ? ? echo "DataBase Increment Backup Fail!" >> $eMailFile

? ? ? mail -s " DataBase Increment Backup Fail " $eMail < $eMailFile ?#如果备份不成功发送邮件通知

? fi

echo "--------------------------------------------------------" >> $logFile

cat $eMailFile >> $logFile

find /mysqlbackup/increment/ -name "*" -mtime +30 |xargs rm -rf

?

3.还原

#!/bin/bash

#

DATE=`date "+%F"`

User="root"

Passwd="mysql"

IP="localhost"

Database="test_db"

FDIR1=`cat /mysqlbackup/full/1.txt`

FDIR2=`cat /mysqlbackup/full/2.txt`

IDIR1=`cat /mysqlbackup/increment/1.txt`

IDIR2=`cat /mysqlbackup/increment/2.txt`

?

#stop mysql

/etc/init.d/mysqld stop

?

#apply full log

/usr/bin/innobackupex --user=$User --password=$Passwd --defaults-file=/etc/my.cnf ?--apply-log $FDIR1/$FDIR2/

?

#apply increment log to full data

/usr/bin/innobackupex --user=$User --password=$Passwd --defaults-file=/etc/my.cnf ?--apply-log --redo-only $FDIR1/$FDIR2/ --incremental-dir=$IDIR1/$IDIR2/

?

rm -f /data/mysqldata/ib*

rm -rf /data/mysql/3306/data/test_db

cp $FDIR1/$FDIR2/ib* /data/mysqldata

chown -R mysql:mysql /data/mysqldata/

cp -r $FDIR1/$FDIR2/$Database /data/mysql/3306/data/

chown -R mysql:mysql /data/mysql/3306/data/$Database

?

#start mysql

/etc/init.d/mysqld start

?

?

还原脚本采用的是直接复制的做法,本人使用--copy-back时,老是出现报错的情况,如下:

Original data directory is not empty! at /usr/local/xtrabackup/bin/innobackupex line 538.恢复的目录必须为空。经查官网,这是xtrabackup的一个BUG。

innobackupex did not check that MySQL datadir was empty before –copy-back was run. With this bug fix, innobackupex will now error out of the –copy-back operation if the destination is not empty, avoiding potential data loss or a strang combination of a restored backup and previous data. Bug Fixed: #737569 (Valentine Gostev)

由于我的data目录不为空,此处直接拷贝就可以了。

  相关解决方案