Use the Maatkit tool to check and synchronize MySQL master-slave data

MAatkitis a set of open source toolkits forMySQLhelp with the daily management of the WhichMk-table-checksumcan be used to detectMasterand theslavethe table structure and data are consistent. At the same time, throughMk-table-synctool to repair unsynchronized data when the master-slave data is found to be inconsistent.

Lab Environment:

master:192.168.189.143:3306

slave:192.168.189.144:3306

1: Installation Maatikit tools.

installation Perl Environment:

#wget http://packages.sw.be/perl-TermReadKey/perl-TermReadKey-2.30-3.el5.rf.x86_64.rpm #rpm-IVH perl-termreadkey-2.30-3.el5.rf.x86_64.rpm

installation Maatikit :

Download Maaikit tool source package, and upload the server.

#tar-zxvf maatkit-7540.tar.gz #cd maatkit-7540 #perl makefile.pl #make Install

after the installation is complete , /usr/bin under Existence mk- the beginning of the tool command:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6F/74/wKiom1Wc2ZqR59yqAAMhFhPUDPI869.jpg "title=" 1.jpg " Width= "height=" border= "0" hspace= "0" vspace= "0" style= "WIDTH:750PX;HEIGHT:79PX;" alt= " Wkiom1wc2zqr59yqaamhfhpudpi869.jpg "/>

2: Analog master-Slave library data is out of sync.

Inserting a piece of data from the library makes the master-slave data out of sync, as follows:

Master: Slave:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6F/71/wKioL1Wc26Xg-ENRAADi370lQT4476.jpg "style=" float: none; "title=" 2.jpg "alt=" wkiol1wc26xg-enraadi370lqt4476.jpg "/>650) this.width=650; src= http://s3.51cto.com/ Wyfs02/m02/6f/74/wkiom1wc2dxrgh1paafj3aeivsu880.jpg "title=" 3.jpg "width=" 324 "height=" 310 "border=" 0 "hspace=" 0 " Vspace= "0" style= "width:324px;height:310px;" alt= "wkiom1wc2dxrgh1paafj3aeivsu880.jpg"/>

3: Use the mk-table-checksum tool to check that the database data is consistent:

#mk-table-checksum h=192.168.189.143,u=root,p=123456,p=3306 h=192.168.189.144,u=root,p=123456,p=3306-d cmhtest

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6F/71/wKioL1Wc3BHgaEEYAAEneRhPgIg823.jpg "title=" 4.jpg " Width= "height=" border= "0" hspace= "0" vspace= "0" style= "WIDTH:700PX;HEIGHT:57PX;" alt= " Wkiol1wc3bhgaeeyaaenerhpgig823.jpg "/>

Check cmhtest data from the library, and found two servers Checksum inconsistent values, which means that the data is out of sync.

4 : Use the Mk-checksum-filter tool to see exactly which table data is not synchronized:

#mk-table-checksum h=192.168.189.143,u=root,p=123456,p=3306 h=192.168.189.144,u=root,p=123456,p=3306-d cmhtest | Mk-checksum-filter

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6F/71/wKioL1Wc31TTZ-MVAADBa03pXSI431.jpg "title=" 5.jpg " Width= "height=" border= "0" hspace= "0" vspace= "0" style= "width:700px;height:50px;" alt= " Wkiol1wc31ttz-mvaadba03pxsi431.jpg "/>

you can see that the table is not synchronized, for T1 .

5: Use the mk-table-sync tool to synchronize data.

#mk-table-sync--execute--print--no-check-slave--transaction--databases cmhtest h=192.168.189.143,u=root,p=123456 H=192.168.189.144,u=root,p=123456delete from ' cmhtest '. ' T1 ' WHERE ' id ' = ' 5 ' and ' name ' = ' zhangs ' LIMIT 1/*maatkit src_db: Cmhtest src_tbl:t1 src_dsn:h=192.168.189.143,p=...,u=root dst_db:cmhtest dst_tbl:t1 dst_dsn:h=192.168.189.144,p= ... , U=root lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:28358 user:root host:node1*/;

sql :

When using the mk-table-sync command, be aware of the order in which the MySQL server is connected, before the server is the source server, after which the server is synchronized.

6: View results:

Master:slave:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/6F/71/wKioL1Wc38nwcuezAADmOzIj768787.jpg "style=" width : 324px;height:318px; "title=" 6.jpg "width=" 324 "height=" 318 "border=" 0 "hspace=" 0 "vspace=" 0 "alt=" Wkiol1wc38nwcuezaadmozij768787.jpg "/>650) this.width=650;" Src= "http://s3.51cto.com/wyfs02/M01/6F/75/ Wkiom1wc3fmqqc1naadmozij768195.jpg "style=" width:324px;height:318px; "title=" 7.jpg "width=" 324 "height=" 318 "border = "0" hspace= "0" vspace= "0" alt= "wkiom1wc3fmqqc1naadmozij768195.jpg"/>

you can see from the library instance cmhtest the table on is already synchronized with the main library.

Because the Maatkit Toolkit online is difficult to find, the attachment is the tool I downloaded before the installation package, to provide everyone to download.

This article is from the Linux OPS blog, so be sure to keep this source http://icenycmh.blog.51cto.com/4077647/1672080

Use the Maatkit tool to check and synchronize MySQL master-slave data