Replication consistency check

How to get replication consistency status

Tool pt-table-checksum from Percona toolkit (http://www.percona.com/software/percona-toolkit) can be used to check mysql replication consistency. It is pretty complicated tool with a lot of options. Please read manual first.

How to check consistency fast and easy:
1) In this mode, we have to specify slave (because RCZ have two slaves, for example). It can be done by creating dedicated db table:

MariaDB [percona]> show create table dsns;
 dsns  | CREATE TABLE `dsns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `dsn` varchar(255) COLLATE utf8_czech_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci

Fill table with slave information:

MariaDB [percona]> select * from dsns;
+----+-----------+-----------------------------------+
| id | parent_id | dsn                               |
+----+-----------+-----------------------------------+
|  1 |      NULL | h=192.168.55.57,u=root,p=heslo23 |
+----+-----------+-----------------------------------+
1 row in set (0.00 sec)

Checksum will be started with this command on master mysql db:

pt-table-checksum --databases=REALITY  --replicate=percona.checksum --create-replicate-table --recursion-method dsn=h=127.0.0.1,D=percona,t=dsns --nocheck-replication-filters h=localhost,u=root

--database - specify db to run on it
--tables - specify table to run on it
--replicate - Write checksum results to this table
--create-replicate-table - table are created automatically if they do not exist
--recursion-method - specify how to discovery slave. Type dsn read this info from specified table

This command generate consistency summary and shows diff. DIFF is per chunk, so if table is small enough, diff can be just 1 (difference somewhere in whole chunk(=table))

How to resolve differences:
If there is inconsistency on some tables, we can use pt-table-sync command, but it is really tricky and you MUST read docs first:
pt-table-sync manual

How to resolve massive table desync across whole db for one slave only:

WARNING: This procedure is not recommended by pt-table-sync, because changes data direct on slave, instead write it to binlog on master (which affect all connected slave servers)
WARNING2: There is much more ways to resolv this issue, this way is pretty tricky and lenghty, but ist my way. You can use this procedure as inspiration for your own way.

1) At first, we have to get list of desynced tables running pt-table-checksum across selected DB:
THIS COMMAND HAS TO RUN ON MASTER

#!/bin/bash

DBS="ADRESAR ANALYTICS ANKETY ARCH CISELNIKY DEMO DNYIR EMAILS EMAILY HRBITOV ICREA INZ LOGS OBED REALITY REKLAMA REKLAMAEN RUIAN SERVIS UIR UZIV adcycle adcyclemuzeum sagator zsj1 IMG"

for i in $DBS; do
        pt-table-checksum --databases=$i --replicate=percona.checksum --create-replicate-table h=localhost,u=root --recursion-method dsn=h=127.0.0.1,D=percona,t=dsns --nocheck-replication-filters --chunk-size-limit=4  > /root/checksum/automat/$i.txt 2> /root/checksum/automat/$i-err.txt
done

2) Now, we have to extract desynced table from pt-table-checksum results:
THIS COMMAND MAY RUN ON EVERYWHERE

for i in `ls -1 | grep -v err`; do cat $i | awk '{if($3 != 0) print $3" "$8}' | grep -e [0..9] | awk '{print $2}' >> desynced.tables ; done

3) Create appropriate command to get "repair" sql commands:
THIS COMMAND MAY RUN EVERYWHERE

for i in `cat desynced.tables`; do db=`echo $i | awk -F"." '{print $1}'`; table=`echo $i | awk -F"." '{print $2}'`; echo "pt-table-sync --print --sync-to-master h=localhost,D=$db,t=$table > /root/sqls/$db.$table.sql" >> sqlcommand; done

4) run given sqlcommand file to create appropriate sqls files:
THIS COMMAND HAS TO BE RUN ON SLAVE

source sqlcommands

5) Apply given sql files on slave server:
THIS COMMAND IS RUNNING ON SLAVE

for i in `ls -1 /root/sqls/`; do mysql </root/sqls/$i; done