Experience and summary of]mysqlhotcopy hot spare tools

Experience and summary of]mysqlhotcopy hot spare tools

I'm free today to try out mysqlhotcopy this quick hot spare myisam engine tool.
(This article is for a single server and will include multiple server related actions later)
The comparison between him and mysqldump:
1, the former is a fast file in the sense of copy, the latter is a database-side SQL statement collection.
2. The former can only run on the machine where the database directory resides, the latter may be used on the remote client.
3. The same place is performed on-line lock TABLES and UNLOCK TABLES
4, the former restore only need to copy backup files to the source directory overwrite, the latter need to pour SQL files into the original library. (source or \. or MySQL < backup files)
Steps to back up with Mysqlhotcopy:
1, there is no PERL-DBD module installation
On my machine:
[Email protected] data]# Rpm-qa |grep perl-dbd | grep MySQL

Perl-dbd-mysql-3.0007-1.fc6
2. Assign a user who is dedicated to the backup in the database segment
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, in/ETC/MY.CNF or login user's personal master file. My.cnf inside Add
[Mysqlhotcopy]
Interactive-timeout
User=hotcopyer
password=123456
port=3306
4, start the backup.
[Email protected] ~]# 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 files ...
Copying indices for 0 files ...
Unlocked tables.
Mysqlhotcopy copied 4 tables (5 seconds overall) in 5 seconds.

Post-backup directory:
[Email protected] data]# Du-h | grep t_girl

213M./t_girl
213M./t_girl_copy
[Email protected] ~]#

5, Mysqlhotcopy usage detailed.
1), mysqlhotcopy original database name, new database name
[Email protected] ~]# 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 files ...
Copying indices for 0 files ...
Unlocked tables.
Mysqlhotcopy copied 4 tables (5 seconds overall) in 5 seconds.
2), mysqlhotcopy the original database name, backup directory
[Email protected] ~]# 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 files ...
Copying indices for 0 files ...
Unlocked tables.
Mysqlhotcopy copied 4 tables (6 seconds overall) in 6 seconds.
3), supports regular expressions on a single table
(except for the ID table)
[Email protected] 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 files ...
Copying indices for 0 files ...
Unlocked tables.
Mysqlhotcopy copied 3 tables (6 seconds overall) in 6 seconds.
[Email protected] data]#


4), the record can be written into a special table. See the Help for details.
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 (+), dest varchar, msg varchar (255));
Query OK, 0 rows affected (0.01 sec)
Also remember to give Hotcopyer user permissions.
Mysql> Grant Insert on hotcopy.checkpoint to [email protected] ' localhost ';
Query OK, 0 rows Affected (0.00 sec)

mysql> flush Privileges;
Query OK, 0 rows Affected (0.00 sec)

Mysql> \q
Bye
Repeat the third step of the operation

[Email protected] ~]# 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 files ...
Copying indices for 0 files ...
Unlocked tables.
Mysqlhotcopy copied 3 tables (files) in seconds (seconds overall).


Saved by default in the data directory/t_girl_copy/
Look at the record table.
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), support incremental backup.
[Email protected] ~]# 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 files ...
Copying indices for 0 files ...
Unlocked tables.
Mysqlhotcopy copied 3 tables (7 seconds overall) in 7 seconds.
6), other waiting to be tested and then released ...

Source: http://www.cublog.cn/u/29134/showart_493525.html