Mysqlhotcopy hot backup tool experience and summary _ MySQL

Mysqlhotcopy hot backup tool experience and summary bitsCN.com

Today, I am free to try MYSQLHOTCOPY, a tool for fast hot backup of the MYISAM engine.
(This article is for a single server and will be added to multiple server operations in the future)
Comparison between him and MYSQLDUMP:
1. the former is a COPY in the sense of a quick file, and the latter is a collection of SQL statements at the Database end.
2. the former can only run on the machine where the database directory is located, and the latter can be used on a remote client.
3. the lock tables and unlock tables are executed online in the same place.
4. for restoration, you only need to COPY the backup file to the source directory and overwrite it. for restoration, you need to import the SQL file to the original database. (Source or. or mysql <backup file)
Follow these steps to back up data using MYSQLHOTCOPY:
1, there is no PERL-DBD module installation
On my machine:
[Root @ localhost data] # rpm-qa | grep perl-DBD | grep MySQL

Perl-DBD-MySQL-3.0007-1.fc6
2. allocate a user dedicated for 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 log on to the user's personal main file. my. cnf, add
[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 _ girl '. 'category', 't_ gir '. 'category _ part', 't_girl '. 'id', 't_ gir '. '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

213 M./t_girl
213 M./t_girl_copy
[Root @ localhost ~] #

5. 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 _ girl '. 'category', 't_ gir '. 'category _ part', 't_girl '. 'id', 't_ gir '. '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 _ girl '. 'category', 't_ gir '. 'category _ part', 't_girl '. 'id', 't_ gir '. '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) regular expressions are supported for a single table.
(Except the id table)
[Root @ localhost data] # mysqlhotcopy t_girl ./~ Id/

Using copy suffix _ copy
Locked 3 tables in 0 seconds.
Flushed tables ('t _ girl '. 'Category','t _ gir'. 'Category _ part','t _ gir'. '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) records can be written to a special table. View the help information.
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)
Remember to grant the hotcopyer user permission.
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
Repeat 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 _ girl '. 'Category','t _ gir'. 'Category _ part','t _ gir'. '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 in the data directory by default/t_girl_copy/
Look at the record table.
Mysql> use hotcopy;
Database changed
Mysql> select * from checkpoint;
+ --------------------- + -------- + ----------------------------------- + ----------- +
| Time_stamp | src | dest | msg |
+ --------------------- + -------- + ----------------------------------- + ----------- +
| 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 _ girl '. 'Category','t _ gir'. 'Category _ part','t _ gir'. '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 the test and release again...

BitsCN.com