mysqlhotcopyホットスペア体験とまとめ
27576 ワード
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) .