Xtrabackup有两个主要的工具：xtrabackup、innobackupex
xtrabackup只能备份InnoDB和XtraDB两种数据表，支持在线热备份，不会锁表
innobackupex则封装了xtrabackup，同时可以备份MyISAM数据表
直接下载二进制版本
一、建立测试数据
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
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)
可以看出，数据完全被恢复了。
完成！