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). 6)、其它的等待測試過了再釋出。。。