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

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

使用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

centos 5.5 下安装

MySQL升级了5.5.8，XtraBackup需要对应升级为1.5

1.安装percona-release
wget http://www.percona.com/downloads/XtraBackup/XtraBackup-1.5/RPM/rhel5/i386/xtrabackup-1.5-11.rhel5.i386.rpm
rpm -ivh xtrabackup-1.5-11.rhel5.i386.rpm

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

三、建立测试数据
1.调整my.cnf参数
vi /etc/my.cnf
innodb_data_home_dir = /home/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_per_table = 0
innodb_buffer_pool_size = 2G
innodb_log_group_home_dir = /home/mysql/data
innodb_log_file_size = 1900M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_thread_concurrency = 16
innodb_flush_method = O_DIRECT
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_io_capacity = 500
innodb_purge_threads = 1
innodb_max_dirty_pages_pct = 90

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

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`)
) ENGINE=MyISAM CHARACTER SET=utf8;
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=utf8;
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 test_myisam <addTest.sql
mysql -uroot
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 /home/mysql-backup/
innobackupex-1.5.1 --defaults-file=/etc/my.cnf --no-lock --user=root --databases="test_myisam test_innodb" /home/mysql-backup/ 2>/home/mysql-backup/logs/innodb.log

xtrabackup_50 Ver 1.3 Rev 148 for 5.0.91 unknown-linux-gnu (x86_64)
Copying ./ibdata1
     to /home/mysql-backup/2010-012-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用户备份
备份的目标目录是/home/mysql-backup/，innobackupex-1.5.1将在该目录下生成备份
--no-lock备份时不锁定表，--databases="test_myisam test_innodb"用于指定要备份的数据库
这里的2>/home/mysql-backup/logs/innodb.log，是将备份过程中的输出信息重定向到/home/mysql-backup/logs/innodb.log

2.压缩(tar gzip)备份
innobackupex-1.5.1 --defaults-file=/etc/my.cnf --no-lock --user=root --databases="test_myisam test_innodb" --stream=tar /home/mysql-backup/ 2>/home/mysql-backup/logs/innodb.log | gzip > /home/mysql-backup/innodb.tar.gz

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

3.模拟恢复过程
mysql -uroot
drop database test_myisam;
drop database test_innodb;
cd /home/mysql/data
rm ibdata1
rm ib_logfile*
echo "1" >/proc/sys/vm/drop_caches

innobackupex-1.5.1 --apply-log --defaults-file=/etc/my.cnf --no-lock --user=root --databases="test_myisam test_innodb" /home/mysql-backup/2010-12-23_16-08-15
innobackupex-1.5.1 --copy-back --defaults-file=/etc/my.cnf --no-lock --user=root --databases="test_myisam test_innodb" /home/mysql-backup/2010-12-23_16-08-15

service mysqld stop
chown -R mysql:root /home/mysql/data
service mysqld 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 /home/mysql-backup/xtrabackup/2010-12-23
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/home/mysql-backup/xtrabackup/2010-12-23
cp -r /home/mysql/data/test_innodb /home/mysql-backup/xtrabackup/2010-12-23

模拟恢复过程
mysql -uroot
drop database test_innodb;
cd /home/mysql/data
rm ibdata1
rm ib_logfile*
echo "1" >/proc/sys/vm/drop_caches
xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/home/mysql-backup/xtrabackup/2010-12-23
service mysqld stop
cp /home/mysql-backup/xtrabackup/2010-12-23/ib* /home/mysql/data
cp -r /home/mysql-backup/xtrabackup/2010-12-23/test_innodb /home/mysql/data
chown -R mysql:root /home/mysql/data
service mysqld start

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

mkdir -p /home/mysql-backup/xtrabackup/2010-12-24-incre
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/home/mysql-backup/xtrabackup/2010-12-24-incre --incremental-basedir=/home/mysql-backup/xtrabackup/2010-12-23

模拟恢复过程
mysql -uroot
drop database test_innodb;
cd /home/mysql/data
rm ibdata1
rm ib_logfile*
echo "1" >/proc/sys/vm/drop_caches
#预备备份
xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/home/mysql-backup/xtrabackup/2010-12-23
#合并增量备份
xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/home/mysql-backup/xtrabackup/2010-12-23 --incremental-dir=/home/mysql-backup/xtrabackup/2010-12-24-incre

cp /home/mysql-backup/xtrabackup/2010-12-23/ib* /home/mysql/data
cp -r /home/mysql-backup/xtrabackup/2010-12-23/test_innodb /home/mysql/data
chown -R mysql:root /home/mysql/data
service mysqld restart

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

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