Hello, please sign in or register
You are here: Home

Display tables which differ between two mysql servers

BASH script to compare two databases and highlight tables which are missing or have a different number of records.

/root/mysql_slave_compare -hslavename -ppassword -uroot -Hmastername -Ppassword -Uroot

Here's the script. ./mysql_slave_compare

#!/bin/bash
#
# Restore the slave server, this script updates the contents of the slave.
# Parameters for this script are
# -h    Slave Host
# -p    Slave password
# -u    Slave Username
# -H    Master Host
# -P    Master password
# -U    Master Username
#
# GET PARAMETERS (OVERIDING DEFAULTS)
MASTERHOST='localhost'

while getopts  "h:u:p:H:U:P:" flag
do
  case "$flag" in
        h) echo "SLAVE_HOST: $OPTARG"
           SLAVEHOST=$OPTARG;;
        p) SLAVEPASS=$OPTARG;;
        u) SLAVEUSER=$OPTARG;;
        H) echo "MASTER_HOST: $OPTARG"
           MASTERHOST=$OPTARG;;
        P) MASTERPASS=$OPTARG;;
        U) MASTERUSER=$OPTARG;;
        d) echo "Databases set to $OPTARG";;
  esac
done

#CHECK CONNECTIONS TO BOTH SLAVE AND MASTER && GET THE MYSQL VERSIONS AT THE SAME TIME
SLAVEVERSION=`mysql -h$SLAVEHOST -u$SLAVEUSER -p$SLAVEPASS --vertical -e "SELECT VERSION()"`
SLAVEVERSION=${SLAVEVERSION#*:}
MASTERVERSION=`mysql -h$MASTERHOST -u$MASTERUSER -p$MASTERPASS --vertical -e "SELECT VERSION()"`
MASTERVERSION=${MASTERVERSION#*:}
if [ -z $MASTERVERSION ]; then
    echo Could not connect to Master
    exit 1
fi
if [ -z $SLAVEVERSION ]; then
    echo Could not connect to Slave
    exit 1
fi

## - GET INFORMATION FROM SLAVE
#SLAVEVERSION
echo "SLAVE_VERSION: $SLAVEVERSION"
#MASTERVERSION
echo "MASTER_VERSION: $MASTERVERSION"

#SLAVEDBS
SLAVE_DBS=`mysql --vertical -h$SLAVEHOST -u$SLAVEUSER -p$SLAVEPASS -e "SHOW SLAVE STATUS" | grep "Replicate_Do_DB"`
SLAVE_DBS=${SLAVE_DBS#*:}
SLAVE_DBS=${SLAVE_DBS%% }

if [ "$SLAVE_DBS" != "" ]; then
        SLAVE_DBS=${SLAVE_DBS//,/ }
        SLAVE_DBS=${SLAVE_DBS%% }
        SLAVE_DBS_ARR=($SLAVE_DBS)
        echo "SLAVE_DBS: $SLAVE_DBS"
else
        # The slave has not specified which tables to accept, get the list from the master and check to see which databases the slave has ommitted
        echo "Looking up the slave DBs"
        MASTER_DBS=`mysql  --batch -h$MASTERHOST -u$MASTERUSER -p$MASTERPASS -e "SHOW DATABASES"`
      
        #Slave ommits
        SLAVE_OMIT=`mysql -h$SLAVEHOST -p$SLAVEPASS -u$SLAVEUSER -e "SHOW SLAVE STATUSG" | grep "Replicate_Wild_Ignore_Table"`
        SLAVE_OMIT=${SLAVE_OMIT#*:}
        SLAVE_OMIT=${SLAVE_OMIT%% }
        SLAVE_OMIT=${SLAVE_OMIT//.%,/ }
        SLAVE_OMIT=${SLAVE_OMIT//.%/ }
        SLAVE_OMIT=${SLAVE_OMIT%% }
        SLAVE_OMIT=${SLAVE_OMIT# }
#       SLAVE_OMIT=$(SLAVE_OMIT)
        echo "Master Databases $MASTER_DBS"
        SLAVE_DBS_ARR=($MASTER_DBS)
        echo $SLAVE_DBS_ARR
fi

## - GET INFORMATION FROM MASTER
## - PREPARE
# Lock the tables on the master and slave servers.
# Turn off the slave's IO
NUM=${#SLAVE_DBS_ARR}
echo "Looping through each database and reading number of records in tables"
for ((i=0;i<$NUM;i++)); do
   DBNAME=${SLAVE_DBS_ARR[${i}]}
   if [ "$DBNAME" == "" ]; then
        echo "DB not valid";
        continue;
   fi
   for DB_OMIT in $SLAVE_OMIT; do
        if [ "$DB_OMIT" == "$DBNAME" ]; then
                echo "Omitting $DBNAME"
                continue 2;
        fi
        #echo ":$DB_OMIT:$DBNAME:"
   done
   #echo Getting information about the tables and their number of rows from $DBNAME
   MASTER_TABLES=`mysql -h$MASTERHOST -u$MASTERUSER -p$MASTERPASS --batch -e "SHOW TABLES FROM $DBNAME"`
   for TBNAME in $MASTER_TABLES; do
        #echo "$DBNAME.$TBNAME";
        SLAVE_ROWS=`mysql -h$SLAVEHOST -u$SLAVEUSER -p$SLAVEPASS --vertical -e "SHOW TABLE STATUS FROM $DBNAME LIKE '$TBNAME'G" | grep Rows`
        SLAVE_ROWS=${SLAVE_ROWS#*:}
        SLAVE_ROWS=${SLAVE_ROWS# }
        MASTER_ROWS=`mysql -h$MASTERHOST -u$MASTERUSER -p$MASTERPASS --vertical -e "SHOW TABLE STATUS FROM $DBNAME LIKE '$TBNAME'G" | grep Rows`
        MASTER_ROWS=${MASTER_ROWS#*:}
        MASTER_ROWS=${MASTER_ROWS# }
        if [ "$MASTER_ROWS" != "$SLAVE_ROWS" ]; then
                echo "$DBNAME.$TBNAME:$SLAVE_ROWS:$MASTER_ROWS"
        fi
   done
done

 

Comments

clibin
Tyrion 5c cases watched her ray ban sunglasses read. His timber...
Created 18/04/16
chenyan
Bond grinned. pandora-bracciali.it "We bcbgmax.in.net only chaussure...
Created 15/06/16
chenyan
Bond grinned. pandora-bracciali.it "We bcbgmax.in.net only chaussure...
Created 15/06/16
Title*
Comment

Prove you are not a robot

To prove you are not a robot, please type in the six character code you see in the picture below
Security confirmation codeI can't see this!
Contact
Name*
Email never shown*
Home Page

Author

Andrew Dodson
Since:Feb 2007

Comment | flag

Categories

Bookmark and Share