[转载]mysqlhotcopy 热备工具体验与总结_MySQL

今天有空尝试了一下MYSQLHOTCOPY这个快速热备MYISAM引擎的工具。<br>(本文是针对单个服务器的情况，以后将会加入多服务器相关操作)<br>他和MYSQLDUMP的比较：<br>1、前者是一个快速文件意义上的COPY，后者是一个数据库端的SQL语句集合。<br>2、前者只能运行在数据库目录所在的机器上，后者可以用在远程客户端。<br>3、相同的地方都是在线执行LOCK TABLES 以及 UNLOCK TABLES<br>4、前者恢复只需要COPY备份文件到源目录覆盖即可，后者需要倒入SQL文件到原来库中。(source 或者/.或者 mysql 用MYSQLHOTCOPY备份的步骤：<br>1、有没有PERL-DBD模块安装<br style="color: #009902;">我的机器上：<br>[root@localhost data]# rpm -qa |grep perl-DBD | grep MySQL<br><br>perl-DBD-MySQL-3.0007-1.fc6<br>2、在数据库段分配一个专门用于备份的用户<br>mysql> grant select,reload,lock tables on *.* to 'hotcopyer'@'localhost' identified by '123456';<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> flush privileges;<br>Query OK, 0 rows affected (0.00 sec)<br><br>3、在/etc/my.cnf或者登陆用户的个人主文件.my.cnf里面添加<br>[mysqlhotcopy]<br>interactive-timeout<br>user=hotcopyer<br>password=123456<br>port=3306<br>4、开始备份。<br>[root@localhost ~]# mysqlhotcopy t_girl t_girl_new<br><br>Locked 4 tables in 0 seconds.<br>Flushed tables (`t_girl`.`category`, `t_girl`.`category_part`, `t_girl`.`id`, `t_girl`.`parent`) in 0 seconds.<br>Copying 22 files...<br>Copying indices for 0 files...<br>Unlocked tables.<br>mysqlhotcopy copied 4 tables (22 files) in 5 seconds (5 seconds overall).<br><br>备份后的目录：<br>[root@localhost data]# du -h | grep t_girl<br><br>213M ./t_girl<br>213M ./t_girl_copy<br>[root@localhost ~]# <br><br>5、MYSQLHOTCOPY用法详解。<br>1)、mysqlhotcopy 原数据库名，新数据库名<br>[root@localhost ~]# mysqlhotcopy t_girl t_girl_new<br><br>Locked 4 tables in 0 seconds.<br>Flushed tables (`t_girl`.`category`, `t_girl`.`category_part`, `t_girl`.`id`, `t_girl`.`parent`) in 0 seconds.<br>Copying 22 files...<br>Copying indices for 0 files...<br>Unlocked tables.<br>mysqlhotcopy copied 4 tables (22 files) in 5 seconds (5 seconds overall).<br>2)、mysqlhotcopy 原数据库名，备份的目录<br>[root@localhost ~]# mysqlhotcopy t_girl /tmp/<br><br>Locked 4 tables in 0 seconds.<br>Flushed tables (`t_girl`.`category`, `t_girl`.`category_part`, `t_girl`.`id`, `t_girl`.`parent`) in 0 seconds.<br>Copying 22 files...<br>Copying indices for 0 files...<br>Unlocked tables.<br>mysqlhotcopy copied 4 tables (22 files) in 6 seconds (6 seconds overall).<br>3)、对单个表支持正则表达式<br>(除了id 表外)<br>[root@localhost data]# mysqlhotcopy t_girl./~id/ <br><br>Using copy suffix '_copy'<br>Locked 3 tables in 0 seconds.<br>Flushed tables (`t_girl`.`category`, `t_girl`.`category_part`, `t_girl`.`parent`) in 0 seconds.<br>Copying 19 files...<br>Copying indices for 0 files...<br>Unlocked tables.<br>mysqlhotcopy copied 3 tables (19 files) in 6 seconds (6 seconds overall).<br>[root@localhost data]# <br><br><br style="color: #0080ff;">4)、可以把记录写到专门的表中。具体察看帮助。<br>perldoc mysqlhostcopy<br><br>mysql> create database hotcopy;<br>Query OK, 1 row affected (0.03 sec)<br>mysql> use hotcopy<br>Database changed<br>mysql> create table checkpoint(time_stamp timestamp not null,src varchar(32),dest varchar(60), msg varchar(255));<br>Query OK, 0 rows affected (0.01 sec)<br>同时记得给hotcopyer用户权限。<br>mysql> grant insert on hotcopy.checkpoint to hotcopyer@'localhost';<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> flush privileges;<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> /q<br>Bye<br>重复第三步的操作<br><br>[root@localhost ~]# mysqlhotcopy t_girl./~id/ --allowold --checkpoint hotcopy.checkpoint<br><br>Using copy suffix '_copy'<br>Existing hotcopy directory renamed to '/usr/local/mysql/data/t_girl_copy_old'<br>Locked 3 tables in 0 seconds.<br>Flushed tables (`t_girl`.`category`, `t_girl`.`category_part`, `t_girl`.`parent`) in 0 seconds.<br>Copying 19 files...<br>Copying indices for 0 files...<br>Unlocked tables.<br>mysqlhotcopy copied 3 tables (19 files) in 12 seconds (13 seconds overall).<br><br><br>默认保存在数据目录下/t_girl_copy/<br>看看记录表。<br>mysql> use hotcopy; <br>Database changed<br>mysql> select * from checkpoint;<br>+---------------------+--------+-----------------------------------+-----------+<br>| time_stamp | src | dest | msg |<br>+---------------------+--------+-----------------------------------+-----------+<br>| 2008-03-11 14:44:58 | t_girl | /usr/local/mysql/data/t_girl_copy | Succeeded | <br>+---------------------+--------+-----------------------------------+-----------+<br>1 row in set (0.00 sec)<br><br>5)、支持增量备份。<br>[root@localhost ~]# mysqlhotcopy t_girl./~id/ --allowold --checkpoint hotcopy.checkpoint --addtodest t_girl_new<br><br>Locked 3 tables in 0 seconds.<br>Flushed tables (`t_girl`.`category`, `t_girl`.`category_part`, `t_girl`.`parent`) in 0 seconds.<br>Copying 19 files...<br>Copying indices for 0 files...<br>Unlocked tables.<br>mysqlhotcopy copied 3 tables (19 files) in 7 seconds (7 seconds overall).<br>6)、其它的等待测试过了再发布。。。

出处：http://www.cublog.cn/u/29134/showart_493525.html