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

mysql/tablesizecheck.txt · Last modified: 24/02/2016 16:00 by andrew