Shulou(Shulou.com)05/31 Report--

What this article shares to you is about how to experience and summarize the hot spare tools for mysqlhotcopy. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Try MYSQLHOTCOPY, a fast hot standby MYISAM engine tool when you have time today.

(this article is for the case of a single server, and multi-server related operations will be added in the future.)

His comparison with MYSQLDUMP:

1. The former is a COPY in the sense of a fast file, while the latter is a collection of SQL statements at the end.

2. The former can only run on the machine where the database directory is located, while the latter can be used on remote clients.

3. The same thing is to execute LOCK TABLES and UNLOCK TABLES online.

4. The former only needs to overwrite the COPY backup files to the source directory, while the latter needs to dump the SQL files into the original library. (source or. Or

< 备份文件) 用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 identified by 123456

Query OK, 0 rows affected (0.00 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

3. Add to / etc/my.cnf or login user's personal master file .my.cnf

[mysqlhotcopy]

Interactive-timeout

User=hotcopyer

Password=123456

Port=3306

4. Start backup.

[root@localhost ~] # mysqlhotcopy t_girl t_girl_new

Locked 4 tables in 0 seconds.

Flushed tables (`t _ room`.`parent`, `t_ room`.`category _ part`, `t_ room`.`id`, `t_ room`.`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).

Directory after backup:

[root@localhost data] # du-h | grep t_girl

213M. / t_girl

213M. / t_girl_copy

[root@localhost ~] #

5. Detailed explanation of the usage of MYSQLHOTCOPY.

1), mysqlhotcopy original database name, new database name

[root@localhost ~] # mysqlhotcopy t_girl t_girl_new

Locked 4 tables in 0 seconds.

Flushed tables (`t _ room`.`parent`, `t_ room`.`category _ part`, `t_ room`.`id`, `t_ room`.`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 original database name, backup directory

[root@localhost ~] # mysqlhotcopy t_girl / tmp/

Locked 4 tables in 0 seconds.

Flushed tables (`t _ room`.`parent`, `t_ room`.`category _ part`, `t_ room`.`id`, `t_ room`.`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) support regular expressions for a single table

(except for id table)

[root@localhost data] # mysqlhotcopy t_girl./~id/

Using copy suffix _ copy

Locked 3 tables in 0 seconds.

Flushed tables (`t_ room`.`roomy`, `t_ room`.`category _ part`, `t_ room`.`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) the record can be written into a special table. Take a look at the help.

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)

At the same time, remember to give hotcopyer user rights.

Mysql > grant insert on hotcopy.checkpoint to

Query OK, 0 rows affected (0.00 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Mysql > Q

Bye

Repeat the operation of step 3

[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_ room`.`roomy`, `t_ room`.`category _ part`, `t_ room`.`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).

Saved by default in the data directory / t_girl_copy/

Look at the record sheet.

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) incremental backup is supported.

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

Locked 3 tables in 0 seconds.

Flushed tables (`t_ room`.`roomy`, `t_ room`.`category _ part`, `t_ room`.`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), wait for others to be tested before release.

The above is how to experience and summarize the hot spare tools of mysqlhotcopy. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.