mysql手动同步工具，mk-table-sync

原创

inbank2012 2013-05-30 18:23:19 博主文章分类：【数据库】 ©著作权

文章标签 mysql手动同步工具 mk-table-sync 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者inbank2012的原创作品，请联系作者获取转载授权，否则将追究法律责任

1、wget http://ftp.cuhk.edu.hk/pub/packages/perl/CPAN/authors/id/T/TI/TIMB/DBI-1.616.tar.gz

[root@localhost ~]# tar zxvf DBI-1.616.tar.gz

[root@localhost ~]# cd DBI-1.616

[root@localhost ~]# perl Makefile.PL && make && make install

2、wget http://search.cpan.org/CPAN/authors/id/C/CA/CAPTTOFU/DBD-mysql-4.019.tar.gz

tar zxvf DBD-mysql-4.019.tar.gz

[root@localhost ~]# cd DBD-mysql-4.019

[root@localhost DBD-mysql-4.019]# perl Makefile.PL --mysql_config=/usr/bin/mysql_config

make

make test 会报错

PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t

t/00base....................ok 1/6

# Failed test 'use DBD::mysql;'

# in t/00base.t at line 21.

# Tried to use 'DBD::mysql'.

# Error: Can't load '/home/zengxiong/maatkit/DBD-mysql-4.020/blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: libmysqlclient.so.16: cannot open shared object file: No such file or directory at /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/DynaLoader.pm line 230.

# at (eval 6) line 2

# Compilation failed in require at (eval 6) line 2.

# BEGIN failed--compilation aborted at t/00base.t line 21.

t/00base....................NOK 2FAILED--Further testing stopped: Unable to load DBD::mysql

make: *** [test_dynamic] Error 9

--解决

make test出错了，如果报错像上面一样，那么就执行

[root@localhost DBD-mysql-4.019]# cp /usr/local/mysql/lib/* /usr/lib （32位解决办法）

[root@localhost DBD-mysql-4.019]# cp /usr/local/mysql/lib/* /usr/lib64/(如果64位否则是lib)

如果上面这一步没做，则到安装maatkit的时候，在perl Makefile.PL会出现警告：prerequisite DBD::mysql 1 not found.

Checking if your kit is complete...

Looks good

Warning: prerequisite DBD::mysql 1 not found.

Writing Makefile for maatkit

然后再继续make test，发现之前的错误: *** [test_dynamic] Error 9 并没有了

t/00base....................ok

t/10connect.................ok

t/20createdrop..............ok

t/25lockunlock..............ok

t/29warnings................ok

t/30insertfetch.............ok

t/31insertid................ok

t/32insert_error............ok

t/35limit...................ok

t/35prepare.................ok

t/40bindparam...............ok

t/40bindparam2..............ok

t/40blobs...................ok

t/40catalog.................ok

t/40keyinfo.................ok

t/40listfields..............ok

t/40nulls...................ok

t/40nulls_prepare...........ok

t/40numrows.................ok

t/40server_prepare..........ok

t/40server_prepare_error....ok

t/40types...................ok

t/41bindparam...............ok

t/41blobs_prepare...........ok

t/42bindparam...............ok

t/50chopblanks..............ok

t/50commit..................ok

t/51bind_type_guessing......ok

t/52comment.................ok

t/53comment.................ok

t/55utf8....................ok

t/60leaks...................skipped

all skipped: Skip $ENV{SLOW_TESTS} is not set

t/65types...................ok

t/70takeimp.................ok

t/71impdata.................ok

t/75supported_sql...........ok

t/76multi_statement.........# Testing multicall against SQL_DBMS_VER: 5.5.31-log

t/76multi_statement.........ok

t/80procs...................ok

t/85init_command............ok

t/86_bug_36972..............ok

All tests successful, 1 test skipped.

Files=40, Tests=838, 2 wallclock secs ( 1.42 cusr + 0.25 csys = 1.67 CPU)

3、wget http://maatkit.googlecode.com/files/maatkit-7486.tar.gz

tar zxvf maatkit-7486.tar.gz

[root@localhost ~]# cd maatkit-7486

[root@localhost maatkit-7486]# perl Makefile.PL

Checking if your kit is complete...

Looks good

Writing Makefile for maatkit

[root@localhost maatkit-7486]# make install

【测试】

在2台机器上分别创建数据库和表，然后再一台机器上（A）导入数据，另外一台机器（B）只要创建了数据库和表就行了

直接用这个工具手动同步

在B上执行

mk-table-sync --execute --print --charset=utf8 --no-check-slave --no-check-triggers -d test h=192.168.1.7,u=test,p=123456 h=192.168.1.22,u=test,p=123456

INSERT INTO `qq`.`tswc_cats`(`cat_id`, `cat_name`, `pid`, `type`, `sort`) VALUES ('1', '图解教程', '0', '1', '0') /*maatkit src_db:qq src_tbl:tswc_cats src_dsn:A=utf8,h=192.168.1.7,p=...,u=test dst_db:qq dst_tbl:tswc_cats dst_dsn:A=utf8,h=192.168.1.22,p=...,u=test lock:0 transaction:0 changing_src:0 replicate:0 bidirectional:0 pid:5952 user:root host:localhost.localdomain*/;

INSERT INTO `qq`.`tswc_cats`(`cat_id`, `cat_name`, `pid`, `type`, `sort`) VALUES ('2', '看盘技巧', '0', '1', '0') /*maatkit src_db:qq src_tbl:tswc_cats src_dsn:A=utf8,h=192.168.1.7,p=...,u=test dst_db:qq dst_tbl:tswc_cats dst_dsn:A=utf8,h=192.168.1.22,p=...,u=test lock:0 transaction:0 changing_src:0 replicate:0 bidirectional:0 pid:5952 user:root host:localhost.localdomain*/;

然后再B上查看数据是否已经插入

另外也可以在B上对数据进行修改，然后再手动执行同步

DELETE FROM `qq`.`tswc_cats` WHERE `cat_id`='4' LIMIT 1 /*maatkit src_db:qq src_tbl:tswc_cats src_dsn:A=utf8,h=192.168.1.7,p=...,u=test dst_db:qq dst_tbl:tswc_cats dst_dsn:A=utf8,h=192.168.1.22,p=...,u=test lock:0 transaction:0 changing_src:0 replicate:0 bidirectional:0 pid:5983 user:root host:localhost.localdomain*/;

INSERT INTO `qq`.`tswc_cats`(`cat_id`, `cat_name`, `pid`, `type`, `sort`) VALUES ('100', '看盘技巧123', '0', '1', '0') /*maatkit src_db:qq src_tbl:tswc_cats src_dsn:A=utf8,h=192.168.1.7,p=...,u=test dst_db:qq dst_tbl:tswc_cats dst_dsn:A=utf8,h=192.168.1.22,p=...,u=test lock:0 transaction:0 changing_src:0 replicate:0 bidirectional:0 pid:5983 user:root host:localhost.localdomain*/;

--它这就是先执行删除旧数据，然后再插入新数据

主从同步的时候，从出现了错误，这个时候，可以先slave stop，然后再手动执行同步主，查看主的show master status；如果是主的数据实时写入，则从在手动同步的时候，需要在执行途中进行主从进程的同步操作，不然即使同步完了，从与主的数据也有可能不一样