Replication consistency check

How to verify that slaves are in sync with the master. There is an easy way to achieve this is to use mk-table-checksum. In this article I'll explain, how to verify slave database with master database.

Assumption:

=========

Master database server: 192.168.168.1

Slave database server: 192.168.168.2

Downloading

=========

we need to download mk-table-checksum onto slave database i.e. 192.168.168.2

wget http://www.maatkit.org/get/toolname/mk-table-checksum

chmod +x mk-table-checksum

Once downloaded, it is ready to use, no installation is needed.

Run this command: this will print connection information and exit

./mk-table-checksum 192.168.168.1 192.168.168.2 --user=root --ask-pass --chunk-size 500000 --databases test --explain-hosts

Output:

Server 192.168.168.1:

h=192.168.168.1,u=root

Server 192.168.168.2:

h=192.168.168.2,u=root

mk-table-checksum assumes the first server is the master and others are slave.

--user: user for login,

--ask-pass: prompt for a password when connecting to mysql servers, in this example it will prompt for both database servers

--chunk-size: Approximate number of rows or size of data to checksum at a time. In this example it's 50000

--database: checksum this comma-separated list of databases.In this example we decided to checksum only test database.

Now it's the time to run above command without --explain-hosts

./mk-table-checksum 192.168.168.1 192.168.168.2 --user=root --ask-pass --chunk-size 500000 --databases test

Output:

DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG

allsaints table_1 0 192.168.168.2 MyISAM 2 53fa3b06 0 0 NULL NULL

allsaints table_1 0 192.168.168.1 MyISAM 2 53fa3b06 0 0 NULL NULL

DATABASE

The database the table is in.

TABLE

The table name.

CHUNK

The chunk (see --chunk-size). Zero if you are not doing chunked checksums.

HOST

The server's hostname.

ENGINE

The table's storage engine.

COUNT

The table's row count, unless you specified to skip it.

CHECKSUM

The table's checksum, unless you specified to skip it or the table has no rows. some types of checksums will be 0 if there are no rows; others will print NULL.

TIME

The time the actual checksum and/or counting took.

WAIT

How long the checksum blocked before beginning.

STAT

The return value of MASTER_POS_WAIT().

LAG

How far the slave lags the master, as reported by SHOW SLAVE STATUS.