Check Table sizes between replication master and slave
#!/bin/bash
# Written Andrew Stringer 01/02/2015
# Purpose is to check table sizes on a master and slave replication server
# to ensure they are the same size prior to swapping slave to master.
#$1 should be the database to test
if [ "$1" = "" ]
then
echo "Usage compare-table-size.sh [databaase name]"
exit 1
fi
MASTER='primarydb.example.pri'
SLAVE='slavedb.example.pri'
DATABASE=$1
#Get list of tables in database
/usr/bin/mysql -h ${MASTER} -u <USER> -p<YOUR-PW> -N -B -e "use ${DATABASE}; show tables;" > "${MASTER}-${DATABASE}-tables.txt" 2>/dev/null
echo "Master is ${MASTER}, Slave is ${SLAVE}"
for TABLE in `cat ${MASTER}-${DATABASE}-tables.txt`
do
TABLECOUNTMASTER=`/usr/bin/mysql -h ${MASTER} -u <USER> -p<YOUR-PW> -N -B -e "SELECT COUNT(*) FROM ${DATABASE}.${TABLE};" 2>/dev/null `
TABLECOUNTSLAVE=`/usr/bin/mysql -h ${SLAVE} -u <USER> -p<YOUR-PW> -N -B -e "SELECT COUNT(*) FROM ${DATABASE}.${TABLE};" 2>/dev/null `
if [ ${TABLECOUNTMASTER} = ${TABLECOUNTSLAVE} ]
then
STATUS="OK"
else
STATUS="WARNING!!"
fi
echo "${STATUS} ${DATABASE}.${TABLE} - ${TABLECOUNTMASTER} ${TABLECOUNTSLAVE}"
done
exit 0