mysql dba首选备份工具xtrabackup安装配置V1.1

Author:gaojinbo
Time:2010-8-18

经过一个星期的测试，文档终于可以与大家分享了。使用Xtrabackup能够非常快速地备份与恢复mysql数据库，是mysql dba的首选。Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份（备份时不影响数据读写），是商业备份工具InnoDB Hotbackup的一个很好的替代品。
Xtrabackup有两个主要的工具：xtrabackup、innobackupex

xtrabackup只能备份InnoDB和XtraDB两种数据表，支持在线热备份，不会锁表
innobackupex则封装了xtrabackup，同时可以备份MyISAM数据表

 

 

本文重点介绍ubuntu 10.04下的安装与配置

一、安装DB
apt-get update
apt-get install mysql-server

 

====================================================================

 

二、安装xtrabackup

ubuntu 10.04 下安装

支持很多ubuntu发行版通过apt-get进行安装

1.导入key
gpg –keyserver subkeys.pgp.net –recv-keys 1C4CBDCDCD2EFD2A
gpg -a –export CD2EFD2A | apt-key add –

 

2.添加源
vi /etc/apt/sources.list
deb lucid main
deb-src lucid main

 

3.安装
apt-get update
apt-get install xtrabackup

 

 

centos 5.5 下安装

1.安装percona-release
rpm -Uhv

 

2.删除以前的mysql
yum remove mysql

 

3.安装
yum install xtrabackup

 

====================================================================

 

三、建立测试数据
1.调整my.cnf参数
vi /etc/mysql/my.cnf
innodb_file_per_table = 0
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_buffer_pool_size = 2G
innodb_file_io_threads = 4

 

 

2.建立数据库
mysql -uroot -pgaojinbo
create database test_myisam CHARACTER SET=gbk;
create database test_innodb CHARACTER SET=gbk;

 

 

3.建立数据表
use test_myisam;
CREATE TABLE `t_myisam` (
`id` bigint(20) NOT NULL auto_increment,
`name` varchar(50) default NULL,
`password` varchar(150) default NULL,
`userstatus` int(2) default NULL,
PRIMARY KEY (`id`)
);

show create table t_myisam\G;

 

use test_innodb;
CREATE TABLE `t_innodb` (
`id` bigint(20) NOT NULL auto_increment,
`name` varchar(50) default NULL,
`password` varchar(150) default NULL,
`userstatus` int(2) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARACTER SET=gbk;

show create table t_innodb\G;

 

 

4.建立存储过程
vi addTest.sql
DROP procedure IF EXISTS addTest;
delimiter //
create procedure addTest(i int)
begin
declare name varchar(64);
delete from test_myisam.t_myisam where id != ’0′;
delete from test_innodb.t_innodb where id != ’0′;
While i>0 do
Set name=concat(‘test’,i);
insert into test_myisam.t_myisam(id,name,password,userstatus) values (i,name,password(name),1);
insert into test_innodb.t_innodb(id,name,password,userstatus) values (i,name,password(name),1);
Set i=i-1;
END while;
end
//
delimiter ;

说明：
addTest(i int),i为插入数据的行数，先删除以前的数据，然后再写入到2个库的2个表中，数据内容一样。

 

 

5.写入数据
mysql -uroot -pgaojinbo test_myisam mysql -uroot -pgaojinbo
use test_myisam;
call addTest(10000000);

Query OK, 1 row affected (13 min 22.87 sec)
由于使用虚拟机，2G内存，写入时间会比较长

myisam和innodb各写入1千万行,每个库占用的空间在900M左右

 

====================================================================

 

四、innobackupex备份与恢复
1.普通备份
mkdir -p /www/backup/db/innobackup/
innobackupex-1.5.1 –defaults-file=/etc/mysql/my.cnf –no-lock –user=root –password=gaojinbo –databases="test_myisam test_innodb" /www/backup/db/innobackup/ 2>/www/backup/db/backup1.log

 

xtrabackup_50  Ver 1.3 Rev 148 for 5.0.91 unknown-linux-gnu (x86_64)
Copying ./ibdata1
     to /www/backup/db/innobackup/2010-08-23_14-38-05/ibdata1
        …done
xtrabackup: The latest check point (for incremental): ’0:1720034804′
xtrabackup: Stopping log copying thread.
xtrabackup: Transaction log of lsn (0 1720034804) to (0 1720034804) was copied.

 

说明：
使用mysql的root用户备份，密码为ylmf
备份的目标目录是/www/backup/db/innobackup/，innobackupex-1.5.1将在该目录下生成备份
–no-lock备份时不锁定表，–databases="test_myisam test_innodb"用于指定要备份的数据库
这里的2>/u01/backup/1/1.log，是将备份过程中的输出信息重定向到/www/backup/db/backup1.log

 

 

2.压缩(tar gzip)备份
innobackupex-1.5.1 –defaults-file=/etc/mysql/my.cnf –no-lock –user=root –password=gaojinbo –databases="test_myisam test_innodb" –stream=tar /www/backup/db/innobackup/ 2>/www/backup/db/backup2.log | gzip > /www/backup/db/innobackup/2.tar.gz

这种备份时间会比较长，主要是执行压缩。
恢复过程，只需要使用tar izxvf 解压对应的文件后，操作完全同普通备份。

 

 

3.模拟恢复过程
cd /var/lib/mysql
mysql -uroot -pgaojinbo
drop database test_myisam;
drop database test_innodb;
/etc/init.d/mysql  stop
rm ibdata1
rm ib_logfile*
echo "1" >/proc/sys/vm/drop_caches
/etc/init.d/mysql start

 

innobackupex-1.5.1 –apply-log –defaults-file=/etc/mysql/my.cnf –no-lock –user=root –password=gaojinbo /www/backup/db/innobackup/2010-08-23_16-00-15
innobackupex-1.5.1 –copy-back –defaults-file=/etc/mysql/my.cnf –no-lock –user=root –password=gaojinbo /www/backup/db/innobackup/2010-08-23_16-00-15

 

/etc/init.d/mysql  stop
chown -R mysql:mysql /var/lib/mysql
chown -R mysql:root /var/lib/mysql/mysql
/etc/init.d/mysql start

通过以上步骤就能完全恢复mysql数据了，恢复过程中必须要启动mysql

 

4.检验数据后的数据
select * from test_myisam.t_myisam where id<300;
select * from test_innodb.t_innodb where id<300;
show create table test_myisam.t_myisam;
show create table test_innodb.t_innodb;

 

mysql> select count(*) from test_myisam.t_myisam;
+———-+
| count(*) |
+———-+
| 10000000 |
+———-+
1 row in set (0.00 sec)

mysql> select count(*) from test_innodb.t_innodb;
+———-+
| count(*) |
+———-+
| 10000000 |
+———-+
1 row in set (17.78 sec)

为何查询时间相差这么大？
myisam 的表rows 记录在表信息中，innodb 的要扫描表

 

 

====================================================================

 

五、xtrabackup备份与恢复
xtrabackup只备份InnoDB数据文件，表结构是不备份的，所以恢复的时候，你必须有对应表结构文件(.frm)

1.普通备份
mkdir -p /www/backup/db/xtrabackup/2010-8-23
xtrabackup –defaults-file=/etc/mysql/my.cnf –backup –target-dir=/www/backup/db/xtrabackup/2010-8-23
cp -r /var/lib/mysql/test_innodb /www/backup/db/xtrabackup/2010-8-23

 

 

模拟恢复过程
cd /var/lib/mysql
mysql -uroot -pgaojinbo
drop database test_innodb;
/etc/init.d/mysql  stop
rm ibdata1
rm ib_logfile*
echo "1" >/proc/sys/vm/drop_caches

 

xtrabackup –defaults-file=/etc/mysql/my.cnf –prepare –target-dir=/www/backup/db/xtrabackup/2010-8-23
cp /www/backup/db/xtrabackup/2010-8-23/ib* /var/lib/mysql
cp -r /www/backup/db/xtrabackup/2010-8-23/test_innodb /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
chown -R mysql:root /var/lib/mysql/mysql
/etc/init.d/mysql start

 

 

2.增量备份
修改存储过程，新增500万条数据（略）
mysql> select count(*) from t_innodb;
+———-+
| count(*) |
+———-+
| 15000000 |
+———-+
1 row in set (5.23 sec)

 

mkdir -p /www/backup/db/xtrabackup/2010-8-23-incre
xtrabackup –defaults-file=/etc/mysql/my.cnf –backup –target-dir=/www/backup/db/xtrabackup/2010-8-23-incre –incremental-basedir=/www/backup/db/xtrabackup/2010-8-23

 

 

模拟恢复过程
cd /var/lib/mysql
mysql -uroot -pgaojinbo
drop database test_innodb;
/etc/init.d/mysql  stop
rm ibdata1
rm ib_logfile*
echo "1" >/proc/sys/vm/drop_caches

 

xtrabackup –defaults-file=/etc/mysql/my.cnf –prepare –target-dir=/www/backup/db/xtrabackup/2010-8-23
xtrabackup –defaults-file=/etc/mysql/my.cnf –prepare –target-dir=/www/backup/db/xtrabackup/2010-8-23 –incremental-dir=/www/backup/db/xtrabackup/2010-8-23-incre

 

cp /www/backup/db/xtrabackup/2010-8-23/ib* /var/lib/mysql
cp -r /www/backup/db/xtrabackup/2010-8-23/test_innodb /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
chown -R mysql:root /var/lib/mysql/mysql
/etc/init.d/mysql start

 

 

3.检验恢复后的数据
mysql -uroot -pgaojinbo
use test_innodb;
mysql> select count(*) from t_innodb;
+———-+
| count(*) |
+———-+
| 15000000 |
+———-+
1 row in set (19.03 sec)

可以看出，数据完全被恢复了。