mysql dba首选备份工具xtrabackup

 

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 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)

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

 

完成！