# 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)
