It’s been a while since last post, I’ve been busy and so had been away from blogging.

from past 2 months, I was working extensively with Percona Mysql and since many applications of ours are into replication environment, I had to come up with scripts considering the manual

work and time team members were putting in this.

Their were mainly two issues I was concerned about,

  • considerable time chewing up for syncing tables and to figure out the differences.
  • also a manual eye on the screen to check.

considering this, came up with simple script.

Prerequisite :

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

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

Privileges:

before that create another username with password to use for syncing tables to slaves from master.

Example: User: maatkit, Password: mat007

Server A : Master

Server B: Slave1

Server C: Slave2

On both the slaves: grant all privileges to master with username maatkit as shown below,

mysql> grant all privileges on *.* TO ‘maatkit’@’master-ip’ IDENTIFIED BY ‘mat007’;

mysql> FLUSH privileges;

I have not use mk-table-checksum since I really don’t believe in checking checksum for discrepancies. had been many occurrences where  records and size are same but still checksum value is different.

In this script, I am just considering number of records though script can be modified as per requirement and also let me know, will make the modification.

Sample output of  the script given below,

1

2

Script: single DB, 2 slaves.

DB=test_db
TBL=`mysql -e “SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘$DB’;” | grep -v table_name`
USERNAME=maatkit
PASSWORD=mat007
SLAVE1=10.1.1.3
SLAVE2=10.1.1.4
THRESHCOUNT=2
SYNCMD=/root/./mk-table-sync
SLAVES=( 10.1.1.3 10.1.1.4 )
RESULT=/root/report.log
echo “<b>Fetching the Records before the sync…</b>” >> $RESULT
echo “<center>” >> $RESULT
echo “<h4><i>Discrepancies in Number of records</i></h3>” >> $RESULT
echo “<HR ALIGN=”CENTER” SIZE=”3″ WIDTH=”70%” NOSHADE>” >> $RESULT
echo “<TABLE BORDER=4 ALIGN=center CELLPADDING=10 CELLSPACING=2>” >> $RESULT
echo “<caption><b>DB Name: $DB</b></caption>” >> $RESULT
echo “<TR>” >> $RESULT
echo “<TH WIDTH=”5%”>Table</TH>” >> $RESULT
echo “<TH WIDTH=”5%”>Master</TH>” >> $RESULT
echo “<TH WIDTH=”5%”>Primary Slave: $SLAVE1</TH>” >> $RESULT
echo “<TH WIDTH=”5%”>Secondary Slave: $SLAVE2</TH>” >> $RESULT
echo “</TR>” >> $RESULT

for TABLE in $TBL;
do
MASTERREC=`mysql -e “select count(*) from $DB.$TABLE;” | awk ‘FNR == 2 {print}’`
SLAVEREC1=`mysql -h$SLAVE1 -u$USERNAME -p$PASSWORD -e “select count(*) from $DB.$TABLE;” | awk ‘FNR == 2 {print}’`
SLAVEREC2=`mysql -h$SLAVE2 -u$USERNAME -p$PASSWORD -e “select count(*) from $DB.$TABLE;” | awk ‘FNR == 2 {print}’`

THRESH1=`expr $MASTERREC – $SLAVEREC1`
THRESH2=`expr $MASTERREC – $SLAVEREC2`

if [[ “$THRESH1” -gt “$THRESHCOUNT” || “$THRESH2” -gt “$THRESHCOUNT” ]];
then
DIFFTABLE+=( $TABLE )
echo “<TR>” >> $RESULT
echo “<TD ALIGN=”center”><PRE>$TABLE</PRE></TD>” >> $RESULT
echo “<TD ALIGN=”center”><PRE>$MASTERREC</PRE></TD>” >> $RESULT
echo “<TD ALIGN=”center”><PRE>$SLAVEREC1</PRE></TD>” >> $RESULT
echo “<TD ALIGN=”center”><PRE>$SLAVEREC2</PRE></TD>” >> $RESULT
echo “</TR>” >> $RESULT
fi
done
echo “</TABLE>” >> $RESULT
echo “</center>” >> $RESULT

for SLAVE in “${SLAVES[@]}”
do
echo “<br>” >> $RESULT
echo “<center><b><FONT COLOR=RED>Slave: $SLAVE</FONT></b></center>” >> $RESULT
echo “<br>” >> $RESULT

for DIFF in “${DIFFTABLE[@]}”
do
echo “<i>Syncing records on Master to Slave, $SLAVE</i>” >> $RESULT
echo “<PRE>`$SYNCMD u=$USERNAME,p=$PASSWORD,h=localhost,D=$DB,t=$DIFF $SLAVE –execute –verbose`</PRE>” >> $RESULT
echo “<br>” >> $RESULT
echo “<i>Sync completed, After sync ..</i>” >> $RESULT
AFTSYNMASTER=`mysql -e “select count(*) from $DB.$DIFF;” | awk ‘FNR == 2 {print}’`
AFTSYNSLAVE2=`mysql -h$SLAVE -u$USERNAME -p$PASSWORD -e “select count(*) from $DB.$DIFF;” | awk ‘FNR == 2 {print}’`

THRESHHOLD=`expr $AFTSYNMASTER – $AFTSYNSLAVE2`

if [ “$THRESHHOLD” -gt “$THRESHCOUNT” ];
then
echo “<FONT COLOR=RED>Sync failed for $DB.$DIFF… still diffrence greater than $THRESHCOUNT …</FONT>” >> $RESULT
echo “<FONT COLOR=RED>IT please check ..</FONT>” >> $RESULT
else
echo “<PRE>” >> $RESULT
echo “” >> $RESULT
echo -e “\t\t\t On Master \t\t\t\t\t On Slave: $SLAVE” >> $RESULT
echo “” >> $RESULT
echo -e “\t\t\t Number of Records: $AFTSYNMASTER \t\t\t Number of Records: $AFTSYNSLAVE2” >> $RESULT
echo “<FONT COLOR=”GREEN”><b>sync completed successfully for $DIFF</b></FONT>” >> $RESULT
echo -e “\t\t\t ==========================================================================” >> $RESULT
echo “</PRE>” >> $RESULT
fi
done
done

(echo -e “From: syn-report@example.com \nTo: abc@gmail.com,xyz@example.com \nMIME-Version: 1.0 \nSubject: sync from master to slave on $HOSTNAME \nContent-Type: text/html \n”; cat $RESULT) | /usr/sbin/sendmail -t
rm -f $RESULT