mysql-table-sync를 이용한 MySQL 데이터 동기화

#  MySQL Toolkit, http://mysqltoolkit.sourceforge.net/
# mysql-table-sync, http://www.xaprb.com/blog/2007/03/18/introducing-mysql-table-sync/

# 설치 전 요구사항
- Linux, WIndows
- perl
- perl DBI, DBD 모듈

# 설치

shell> tar zxvf mysql-table-sync-0.9.2.tar.gz 
shell> cd mysql-table-sync-0.9.2 
 
shell> perl Makefile.PL 
shell> make install 
 

# 설치 후 작업
- 소스 데이터 연결을 위한 환경설정

MySQL 서버가 설치된 서버에서, 
 
shell> ln -s my.cnf .my.cnf 
 

# 옵션

$ mysql-table-sync --help 
Usage: /usr/bin/mysql-table-sync [OPTION].. <source> <dest> 
 
  --algorithm     -a   Algorithm to use (topdown, bottomup) 
  --[no]analyze   -A   Analyze (find/fix/print) in bottom-up algorithm (default) 
  --branchfactor  -B   Branch factor for bottom-up algorithm (default 128) 
  --bufferresults      Fetch all rows from MySQL before comparing 
  --[no]build     -U   Build tables for bottom-up algorithm (default) 
  --[no]cleanup   -C   Clean up scratch tables for bottom-up algorithm (default) 
  --[no]collate   -O   Use MySQL to compare strings if necessary (default) 
  --columns       -c   Comma-separated column list 
  --debug         -b   Print debugging output to STDOUT 
  --deleteinsert  -l   Convert all UPDATES to DELETE and INSERT 
  --drilldown     -d   Drilldown groupings for top-down algorithm 
  --engine        -E   Storage engine for bottom-up tables 
  --execute       -x   Execute queries required to sync 
  --forupdate     -f   Use SELECT FOR UPDATE or LOCK IN SHARE MODE for checksums 
  --help               Show this help message 
  --[no]lock      -k   Lock tables when beginning work 
  --maxcost       -m   Maximum rowcount before aborting 
  --onlydo        -o   Only do INS/UPD/DEL (default: all) 
  --prefix        -P   Tablename prefix for bottom-up algorithm 
  --print         -p   Print all sync queries to STDOUT 
  --queries       -q   Make debugging output executable SQL 
  --replicate     -r   Change on master. Implies --strategy=r 
  --separator     -e   Separator for CONCAT_WS 
  --singletxn     -1   Do in a single transaction 
  --size          -S   Table size in bottom-up algorithm, not usually needed 
  --strategy      -s   Query strategy when syncing (r=replace, s=ins/upd/del) 
  --temp          -T   Use temporary tables in bottom-up algorithm (default: no) 
  --timeoutok     -t   Keep going if --wait fails 
  --verbose       -v   Explain differences found; specify up to three times 
  --[no]verify    -V   Verify checksum compatibility across servers (default) 
  --wait          -w   Make slave wait for master pos (implies --lock) 
 
/usr/bin/mysql-table-sync finds and resolves data differences between two MySQL tables. 
<source> and <dest> are data sources in the format 
   user:pass@host:port/database.table:key 
Everything but the table name is optional, and defaults will be read from your 
environment and your .my.cnf file if possible.  Values for <dest> default to the 
values for <source>. 
 
For more details, please read the documentation: 
 
   perldoc /usr/bin/mysql-table-sync 
 

# 소스 테이블과 대상 테이블간 부분 검색 (실제 동기화 작업은 일어나지 않고, 동기화를 위한 스크립트만 출력해준다.)

$ mysql-table-sync --print ptop:ptoppw@10.30.40.131:3306/ptop.my_box ptop:ptoppw@10.30.40.132:3306/ptop.my_box 
UPDATE `ptop`.`my_box` SET `session_id`='60331',`page_id`='5981',`title`='My 利앷텒',`box_list_id`='42',`pos_col`='0',`pos_row`='-63.5625',`param1`=NULL,`param2`=NULL,`param3`=NULL,`param4`=NULL,`param5`=NULL,`param_text`=NULL WHERE `id` = '434072'; 
UPDATE `ptop`.`my_box` SET `session_id`='60331',`page_id`='5981',`title`='怨꾩궛湲?,`box_list_id`='1121',`pos_col`='0',`pos_row`='-67.5625',`param1`='http://wm.daum.net/widget/flash/Calculator.swf',`param2`=NULL,`param3`='185',`param4`='290',`param5`=NULL,`param_text`=NULL WHERE `id` = '434082'; 
INSERT INTO `ptop`.`my_box`(`id`,`session_id`,`page_id`,`title`,`box_list_id`,`pos_col`,`pos_row`,`param1`,`param2`,`param3`,`param4`,`param5`,`param_text`) VALUES('423482','60331','5981','My 利앷텒','42','1','-6.87408447265625',NULL,NULL,NULL,NULL,NULL,NULL); 
INSERT INTO `ptop`.`my_box`(`id`,`session_id`,`page_id`,`title`,`box_list_id`,`pos_col`,`pos_row`,`param1`,`param2`,`param3`,`param4`,`param5`,`param_text`) VALUES('427971','60331','5981','Alice in Wonderland','5','1','-12.8740844726561','http://in-wonderland.tistory.com/rss','summary','10','','',NULL); 
INSERT INTO `ptop`.`my_box`(`id`,`session_id`,`page_id`,`title`,`box_list_id`,`pos_col`,`pos_row`,`param1`,`param2`,`param3`,`param4`,`param5`,`param_text`) VALUES('434081','60331','5981','BBC News | News Front Page | World Edition ','521','0','-59.5625','http://news.bbc.co.uk/rss/newsonline_world_edition/front_page/rss.xml',NULL,NULL,NULL,NULL,NULL); 
[mysql@spf-per-db01 ~]$ 
 

# 소스 테이블 기준으로 대상 테이블과 싱크 맞추기 (동기화를 위한 스크립트 실행한다. 단, 단일 트랜잭션으로 처리한다.)

$ mysql-table-sync --print --execute --singletxn ptop:ptoppw@10.30.40.131:3306/ptop.my_box ptop:ptoppw@10.30.40.132:3306/ptop.my_box

 

 

[출처] Mixellaneous | 김영우 (http://mixellaneous.tistory.com/94)