mysqlhotcopy 热备工具体验与总结

最新推荐文章于 2021-02-02 14:22:32 发布

原创最新推荐文章于 2021-02-02 14:22:32 发布 · 141 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#MySQL #Perl #正则表达式 #RedHat

本文详细介绍使用MYSQLHOTCOPY进行数据库备份的方法，包括安装配置、授权用户、备份流程及命令选项等，支持增量备份并将操作记录入库。

用MYSQLHOTCOPY备份的步骤：
1、有没有PERL-DBD模块安装
我的机器上：
# 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、开始备份。
# 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).

备份后的目录：
# du -h | grep t_girl

213M ./t_girl
213M ./t_girl_copy
#

5、MYSQLHOTCOPY用法详解。
1)、mysqlhotcopy 原数据库名，新数据库名
# 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 原数据库名，备份的目录
# 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 表外)
# 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).
#

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
重复第三步的操作

# 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)、支持增量备份。
# 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).

mysqlhotcopy可以把執行的操作記錄入資料庫中，所以我建立了下面這個DB/Table:

CREATE DATABASE `dbinfo`;
USE dbinfo;

CREATE TABLE `checkpoint` (
`time_stamp` timestamp(14) NOT NULL,
`src` varchar(32) NOT NULL default '',
`dest` varchar(60) NOT NULL default '',
`msg` varchar(255) NOT NULL default '',
PRIMARY KEY (`time_stamp`)
) TYPE=MyISAM;

然後建立一個備份目錄。如果不指定備份目錄，mysqlhotcopy會在原來的位置備份(Redhat下面是/var/lib/mysql/)，只不過目錄名後面加上了 _old 這個尾碼。
好了一切準備妥當，測試一下：

mysqlhotcopy --checkpoint dbinfo.checkpoint --addtodest db_douzi_org /var/db_backup

簡單說明一下：
--checkpoint dbinfo.checkpoint 這個是指定存放操作記錄的資料庫/表
--addtodest 增量備份，新的備份自動覆蓋掉原來的
db_douzi_org 我要備份的資料庫名，如果有多個，依次寫就行
/var/db_backup 備份目錄