----1.编译安装percona-xtrabackup yum install cmake gcc gcc-c++ libaio libaio-devel automake autoconf bzr \ bison libtool ncurses-devel zlib-devel libgcrypt-devel wget http://www.percona.com/downloads/xtrabackup/xtrabackup-2.1.9/source/perc
----1.编译安装percona-xtrabackup
yum install cmake gcc gcc-c++ libaio libaio-devel automake autoconf bzr \
bison libtool ncurses-devel zlib-devel libgcrypt-devel
wget http://www.percona.com/downloads/xtrabackup/xtrabackup-2.1.9/source/percona-xtrabackup-2.1.9.tar.gz
tar xvzf percona-xtrabackup-2.1.9.tar.gz
mkdir -p /usr/local/xtrabackup/
mv percona-xtrabackup-2.1.9 /usr/local/xtrabackup/
cd /usr/local/xtrabackup/percona-xtrabackup-2.1.9
auto_download=yes ./utils/build.sh innodb56
/*****重新安装需要
rm -rf /usr/bin/innobackupex
rm -rf /usr/bin/xtrabackup
***********/
cp innobackupex /usr/bin/
cp src/xtrabackup_56 /usr/bin/
ln -s /usr/local/mysql/bin/* /usr/bin/
---2.安装相关插件
wget http://www.percona.com/downloads/percona-toolkit/latest/rpm/percona-toolkit-2.2.8-1.noarch.rpm
yum install perl-dbi
yum install perl-dbd-mysql
yum install perl-time-hires
yum install perl-io-socket-ssl
rpm -ivh percona-toolkit-2.2.8-1.noarch.rpm
*****************************************************************
普通备份和还原
*****************************************************************
----1.备份
create table t1
(
sid int not null ,
sname varchar(100) not null
)engine=innodb charset=gbk ;
delimiter //
create procedure proc1()
begin
declare i int default 0;
set i=1 ;
set autocommit=0;
while iinsert into t1 values(i,'我');
set i=i+1;
end while;
commit;
set autocommit=1;
end
//
delimiter ;
call proc1;
---2.备份数据
innobackupex --user=root --password=password --defaults-file=/usr/local/mysql/my.cnf \
--port=3306 /backup
---3.恢复数据
service mysql stop
mv /usr/local/mysql/data/innodb_data/ /wind/
或
cp -rvf innodb_data/ /wind/
----datadir数据目录和日志目录需要为空
rm -rf /usr/local/mysql/innodb_data/*
rm -rf /usr/local/mysql/data/*
rm -rf /usr/local/mysql/mysql_logs/innodb_log/*
----4.准备日志(默认使用内存100m)
innobackupex --ibbackup=xtrabackup_56 --user=root --password=password --defaults-file=/usr/local/mysql/my.cnf \
--apply-log --use-memory=4g /backup/2014-06-06_10-27-47
----5.还原数据库
innobackupex --ibbackup=xtrabackup_56 --defaults-file=/usr/local/mysql/my.cnf --copy-back /backup/2014-06-06_10-27-47
还原前数据文件和事务日志文件包括innodb的日志都需要删除.根据/et/my.cnf来确定mysql的数据位置
---6.权限设置
chown -r mysql:mysql /usr/local/mysql/
service mysql start
*****************************************************************
增量备份和还原
*****************************************************************
----1.准备数据
create database wind;
use wind;
create table t1
(
sid int not null ,
sname varchar(100) not null
)engine=innodb charset=gbk ;
delimiter //
create procedure proc1()
begin
declare i int default 0;
set i=1 ;
set autocommit=0;
while iinsert into t1 values(i,'我');
set i=i+1;
end while;
commit;
set autocommit=1;
end
//
delimiter ;
call proc1;
---2.全备数据
mkdir -p /backup/full
innobackupex --user=root --password=password --defaults-file=/usr/local/mysql/my.cnf \
--port=3306 /backup/full
----3.改变数据
create table t2
(
sid int not null ,
sname varchar(100) not null
)engine=innodb charset=gbk ;
delimiter //
create procedure proc2()
begin
declare i int default 0;
set i=1 ;
set autocommit=0;
while iinsert into t2 values(i,'今天是个好日子');
set i=i+1;
end while;
commit;
set autocommit=1;
end
//
delimiter ;
call proc2;
----4.增量备份
mkdir -p /backup/incre
innobackupex --use-memory=4g --user=root --password=password --defaults-file=/usr/local/mysql/my.cnf \
--port=3306 --incremental /backup/incre --incremental-basedir=/backup/full/2014-06-06_12-26-10
---3.恢复数据
service mysql stop
mv /usr/local/mysql/data/innodb_data/ /wind/
或
cp -rvf innodb_data/ /wind/
----datadir数据目录和日志目录需要为空
rm -rf /usr/local/mysql/innodb_data/*
rm -rf /usr/local/mysql/data/*
rm -rf /usr/local/mysql/mysql_logs/innodb_log/* 
----4.准备日志(默认使用内存100m)
---4.1 prepare完整备份集
/*语法: innobackupex --apply-log --redo-only base-dir */
innobackupex --ibbackup=xtrabackup_56 --user=root --password=password --defaults-file=/usr/local/mysql/my.cnf \
--apply-log --redo-only /backup/full/2014-06-06_12-26-10 --use-memory=4g
---4.2 prepare增量备份集
/*语法:innobackupex --apply-log --redo-only base-dir --incremental-dir= */
innobackupex --ibbackup=xtrabackup_56 --user=root --password=password --defaults-file=/usr/local/mysql/my.cnf \
--apply-log --redo-only /backup/full/2014-06-06_12-26-10 --incremental-dir=/backup/incre/2014-06-06_12-38-16 \
--use-memory=4g
---4.3再次prepare全备集，回滚那些未提交的事务
/*语法: innobackupex --apply-log base-dir */
innobackupex --ibbackup=xtrabackup_56 --user=root --password=password --defaults-file=/usr/local/mysql/my.cnf \
--apply-log /backup/full/2014-06-06_12-26-10 --use-memory=4g
----5.还原数据库
innobackupex --ibbackup=xtrabackup_56 --defaults-file=/usr/local/mysql/my.cnf \
--copy-back /backup/full/2014-06-06_12-26-10
还原前数据文件和事务日志文件包括innodb的日志都需要删除.根据/et/my.cnf来确定mysql的数据位置
---6.权限设置
chown -r mysql:mysql /usr/local/mysql/
---7.启动mysql
service mysql start