Data Sanity Check in mySQL replication

Hi Friends,

Today, we will see the method to check data/database schema sanity in mySQL replication architecture. This method describes to check data / schema validation in one to many slave relationship. It may be very useful for the daily basic monitoring purpose to ensuring for data integrity of the database.

mySQL data integrity check-

Open a file in vi or vim editor-

vi check_db_data_sync.sh

##################### This script checks checksum between each replicating tables of

### master and slave database server ##

#! /bin/bash

REPLICATING_TABLES=path_of_replicating_table_list_file
now=`date +%d-%m-%Y_%T`
DIR=path_of_log

## Below CNF are used for the authentication purpose
RLogin_CNF="/etc/rlogin.cnf"
Master_CNF="/etc/master.cnf"
SLAVE_CNF="/etc/slave.cnf" 

## mySQL function 

function check_db_sync()
{
    `mysql --defaults-file=$RLogin_CNF -e "show processlist;" | grep -w "repl"  | awk '{print $3}' | awk -F: '{print $1}' | sort | uniq > /tmp/j1.txt`
    if [ `wc -l /tmp/j1.txt | awk '{print $1}'` -ge 1 ];then

        for slave in `cat /tmp/j1.txt`
        do
            c1=0;
            c2=0;
            echo -e "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
            echo -e "For SLAVE: \t$slave at $now";
            echo -e "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
            for tab in `cat $REPLICATING_TABLES`
            do
                ############# Check checksum in Master Server ###############
                `mysql --defaults-file=$Master_CNF --database cmdb -e "checksum table $tab;"  > master_"$tab".csv`
                ############# Check checksum in Slave Servers ################
                `mysql --defaults-file=$SLAVE_CNF -h$slave --database cmdb -e "checksum table $tab;" > slave_$slave_"$tab".csv`
                mchksum=`tail -1 master_$tab.csv | awk '{print $2}'`
                schksum=`tail -1 slave_$slave_$tab.csv | awk '{print $2}'`
                if [ $mchksum -eq $schksum ]; then
                    c1=`expr $c1 + 1`
                else
                    echo -e "Mis-match Table:\t$tab";
                    c2=`expr $c2 + 1`
                fi
            done
            c=`expr $c1 + $c2`;
            echo -e "Total Table Found: \t$c";
            echo -e "Table data match : \t$c1";
            echo -e "Table data Mis-match : \t$c2";
            mv master_$tab.csv master_$tab_$now.csv_done
            mv slave_$slave_$tab.csv slave_$slave_$tab_$now.csv_done
        done
    else
        echo -e "Slave servers are not found";
    fi
}

## call mySQL function
check_db_sync
rm /tmp/j1.txt
cd $DIR
rm [ms]*_done 

## close vi editor

Now change the mode of the file by using "chmod" linux command, as 

chmod +x check_db_data_sync.sh 

## Execute-

/bin/sh path_of_file/check_db_data_sync.sh