本文共 4179 字,大约阅读时间需要 13 分钟。
今天有空尝试了一下MYSQLHOTCOPY这个快速热备MYISAM引擎的工具。(本文是针对单个服务器的情况,以后将会加入多服务器相关操作)他和MYSQLDUMP的比较:1、前者是一个快速文件意义上的COPY,后者是一个数据库端的SQL语句集合。2、前者只能运行在数据库目录所在的机器上,后者可以用在远程客户端。3、相同的地方都是在线执行LOCK TABLES 以及 UNLOCK TABLES4、前者恢复只需要COPY备份文件到源目录覆盖即可,后者需要倒入SQL文件到原来库中。(source 或者/.或者 mysql < 备份文件)用MYSQLHOTCOPY备份的步骤:1、有没有PERL-DBD模块安装
我的机器上:[root@localhost data]# rpm -qa |grep perl-DBD | grep MySQLperl-DBD-MySQL-3.0007-1.fc62、在数据库段分配一个专门用于备份的用户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-timeoutuser=hotcopyerpassword=123456port=33064、开始备份。[root@localhost ~]# mysqlhotcopy t_girl t_girl_newLocked 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_girl213M ./t_girl213M ./t_girl_copy[root@localhost ~]# 5、MYSQLHOTCOPY用法详解。1)、mysqlhotcopy 原数据库名,新数据库名[root@localhost ~]# mysqlhotcopy t_girl t_girl_newLocked 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 mysqlhostcopymysql> create database hotcopy;Query OK, 1 row affected (0.03 sec)mysql> use hotcopyDatabase changedmysql> 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> /qBye重复第三步的操作[root@localhost ~]# mysqlhotcopy t_girl./~id/ --allowold --checkpoint hotcopy.checkpointUsing 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 changedmysql> 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_newLocked 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).6)、其它的等待测试过了再发布。。。
转载地址:http://kqqmb.baihongyu.com/