mysqlhotcopy 热备工具体验与总结

今天有空尝试了一下MYSQLHOTCOPY这个快速热备MYISAM引擎的工具。
(本文是针对单个服务器的情况，以后将会加入多服务器相关操作 )
他和MYSQLDUMP的比较：
1、前者是一个快速文件意义上的COPY，后者是一个数据库端的SQL语句集合。
2、前者只能运行在数据库目录所在的机器上，后者可以用在远程客户端。
3、相同的地方都是在线执行LOCK TABLES 以及 UNLOCK TABLES
4、前者恢复只需要COPY备份文件到源目录覆盖即可，后者需要倒入SQL文件到原来库中。( source 或者/. 或者 mysql < 备份文件)
用MYSQLHOTCOPY备份的步骤：
1、有没有PERL-DBD模块安装
我的机器上：
[ root@ localhost data] # rpm -qa |grep perl-DBD | grep MySQL

perl- DBD- MySQL- 3. 0007- 1. fc6
2、在数据库段分配一个专门用于备份的用户
mysql> grant select , reload, lock tables on * . * to 'hotcopyer' @ 'localhost' identified by '123456' ;
Query OK, 0 rows affected ( 0. 00 sec)

mysql> flush privileges;
Query OK, 0 rows affected ( 0. 00 sec)

3、在/etc/my.cnf或者登陆用户的个人主文件.my.cnf里面添加
[ mysqlhotcopy]
interactive- timeout
user= hotcopyer
password= 123456
port= 3306
4、开始备份。
[ root@ localhost ~ ] # mysqlhotcopy t_girl t_girl_new

Locked 4 tables in 0 seconds.
Flushed tables ( ` t_girl`. ` category`, `t_girl`. ` category_part`, `t_girl`. ` id`, `t_girl`. ` parent`) in 0 seconds.
Copying 22 files. . .
Copying indices for 0 files. . .
Unlocked tables.
mysqlhotcopy copied 4 tables ( 22 files) in 5 seconds ( 5 seconds overall) .

备份后的目录：
[ root@ localhost data] # du -h | grep t_girl

213M . / t_girl
213M . / t_girl_copy
[ root@ localhost ~ ] #

5、MYSQLHOTCOPY用法详解。
1)、mysqlhotcopy 原数据库名，新数据库名
[ root@ localhost ~ ] # mysqlhotcopy t_girl t_girl_new

Locked 4 tables in 0 seconds.
Flushed tables ( ` t_girl`. ` category`, `t_girl`. ` category_part`, `t_girl`. ` id`, `t_girl`. ` parent`) in 0 seconds.
Copying 22 files. . .
Copying indices for 0 files. . .
Unlocked tables.
mysqlhotcopy copied 4 tables ( 22 files) in 5 seconds ( 5 seconds overall) .
2)、mysqlhotcopy 原数据库名，备份的目录
[ root@ localhost ~ ] # mysqlhotcopy t_girl /tmp/

Locked 4 tables in 0 seconds.
Flushed tables ( ` t_girl`. ` category`, `t_girl`. ` category_part`, `t_girl`. ` id`, `t_girl`. ` parent`) in 0 seconds.
Copying 22 files. . .
Copying indices for 0 files. . .
Unlocked tables.
mysqlhotcopy copied 4 tables ( 22 files) in 6 seconds ( 6 seconds overall) .
3)、对单个表支持正则表达式
( 除了id 表外)
[ root@ localhost data] # mysqlhotcopy t_girl./~id/

Using copy suffix '_copy'
Locked 3 tables in 0 seconds.
Flushed tables ( ` t_girl`. ` category`, `t_girl`. ` category_part`, `t_girl`. ` parent`) in 0 seconds.
Copying 19 files. . .
Copying indices for 0 files. . .
Unlocked tables.
mysqlhotcopy copied 3 tables ( 19 files) in 6 seconds ( 6 seconds overall) .
[ root@ localhost data] #


4)、可以把记录写到专门的表中。具体察看帮助。
perldoc mysqlhostcopy

mysql> create database hotcopy;
Query OK, 1 row affected ( 0. 03 sec)
mysql> use hotcopy
Database changed
mysql> create table checkpoint( time_stamp timestamp not null, src varchar( 32) , dest varchar( 60) , msg varchar( 255) ) ;
Query OK, 0 rows affected ( 0. 01 sec)
同时记得给hotcopyer用户权限。
mysql> grant insert on hotcopy. checkpoint to hotcopyer@ 'localhost' ;
Query OK, 0 rows affected ( 0. 00 sec)

mysql> flush privileges;
Query OK, 0 rows affected ( 0. 00 sec)

mysql> /q
Bye
重复第三步的操作

[ root@ localhost ~ ] # mysqlhotcopy t_girl./~id/ --allowold --checkpoint hotcopy.checkpoint

Using copy suffix '_copy'
Existing hotcopy directory renamed to '/usr/local/mysql/data/t_girl_copy_old'
Locked 3 tables in 0 seconds.
Flushed tables ( ` t_girl`. ` category`, `t_girl`. ` category_part`, `t_girl`. ` parent`) in 0 seconds.
Copying 19 files. . .
Copying indices for 0 files. . .
Unlocked tables.
mysqlhotcopy copied 3 tables ( 19 files) in 12 seconds ( 13 seconds overall) .


默认保存在数据目录下/ t_girl_copy/
看看记录表。
mysql> use hotcopy;
Database changed
mysql> select * from checkpoint;
+ - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - +
| time_stamp | src | dest | msg |
+ - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - +
| 2008- 03- 11 14: 44: 58 | t_girl | / usr/ local / mysql/ data/ t_girl_copy | Succeeded |
+ - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - +
1 row in set ( 0. 00 sec)

5)、支持增量备份。
[ root@ localhost ~ ] # mysqlhotcopy t_girl./~id/ --allowold --checkpoint hotcopy.checkpoint --addtodest t_girl_new

Locked 3 tables in 0 seconds.
Flushed tables ( ` t_girl`. ` category`, `t_girl`. ` category_part`, `t_girl`. ` parent`) in 0 seconds.
Copying 19 files. . .
Copying indices for 0 files. . .
Unlocked tables.
mysqlhotcopy copied 3 tables ( 19 files) in 7 seconds ( 7 seconds overall) .