Skip to content

Instantly share code, notes, and snippets.

@aw
Created July 8, 2011 04:12
Show Gist options
  • Save aw/1071124 to your computer and use it in GitHub Desktop.
Save aw/1071124 to your computer and use it in GitHub Desktop.
Perform consistency checks on replicated MySQL databases
#!/bin/sh
# Script to perform consistency checks on replicated MySQL databases
#
# (c) Alex Williams - 2009 - www.alexwilliams.ca
#
# v0.1
#
# Options:
# -c Check for inconsistent slaves
#
###############
#
# Slaves *must* have reporting enabled in their my.cnf
# example:
# [mysqld]
# report-host = 172.16.0.63
# report-port = 3306
#########################
# User Defined Variables
#########################
MYSQL_HOST="172.16.0.60" # The MASTER database IP
MYSQL_PORT="3306" # The MASTER database PORT
MYSQL_USER="username"
MYSQL_PASS="password"
MYSQL_CHECKSUM="test.checksum" # The database (test) and table (checksum) to store checksum results
# Mandatory commands for this script to work.
COMMANDS="mysql mysqladmin mk-audit mk-table-checksum mk-checksum-filter awk"
##############
# Exit Codes
##############
E_INVALID_ARGS=65
E_INVALID_COMMAND=66
E_NO_SLAVES=67
E_DB_PROBLEM=68
##########################
# Script Functions
##########################
error() {
E_CODE=$?
echo "Exiting: ERROR ${E_CODE}: $E_MSG"
exit $E_CODE
}
usage() {
echo -e "MySQL Replication Consistency - version 0.1 (c) Alex Williams - www.alexwilliams.ca"
echo -e "\nOptions: "
echo -e "\t-c\tCheck for inconsistent slave(s)"
echo -e ""
exit $E_INVALID_ARGS
}
##
# Perform sanity checks before allowing the script to run
##
sanity_checks() {
##
# Verify if commands exist
##
for command in $COMMANDS
do
##
# Set the full path of the command
##
PROG=`which $command`
if [ ! ${PROG} ]; then
##
# Error message if the command doesn't exist
##
E_MSG="missing command '$command'"
return $E_INVALID_COMMAND
else
##
# Create a variable (i.e: $prog_tar)
# substitutes all - for _ (i.e: prog_mk-audit becomes prog_mk_audit)
##
E_MSG="Command not found"
eval prog_${command//-/_}=${PROG} || return
fi
done
}
###
# Check for inconsistent slaves
###
check() {
##
# Run the mk_table_checksum command
##
E_MSG="Problem running '$prog_mk_table_checksum' at the top of check() function"
$prog_mk_table_checksum --quiet --replicate=$MYSQL_CHECKSUM --create-replicate-table --empty-replicate-table h=$MYSQL_HOST,P=$MYSQL_PORT,u=$MYSQL_USER,p=$MYSQL_PASS || return $E_DB_PROBLEM
SLAVE_LIST=`$prog_mysql --user=$MYSQL_USER --password=$MYSQL_PASS -e "SHOW SLAVE HOSTS\G"`
##
# Create arrays for the slave ids, hosts, ports
# To manually create the slave arrays, do something like this instead:
#
# slave_ids=(3 4 5)
# slave_hosts=(172.16.0.63 172.16.0.64 172.16.0.65)
# slave_ports=(3306 3306 3306)
#
##
slave_ids=(`echo "$SLAVE_LIST" | grep "Server_id" | $prog_awk -F ": " '{ print $2 }'`)
slave_hosts=(`echo "$SLAVE_LIST" | grep "Host" | $prog_awk -F ": " '{ print $2 }'`)
slave_ports=(`echo "$SLAVE_LIST" | grep "Port" | $prog_awk -F ": " '{ print $2 }'`)
##
# Define the number of slaves by the number of entries in the slave_ids[] array
##
num_slaves=${#slave_ids[*]}
index=0
if [ $num_slaves -eq 0 ]; then
echo "No Replication Slaves appear in 'SHOW SLAVE HOSTS'"
return $E_NO_SLAVES
fi
##
# verify the checksums on each replicated slave
##
while [ "$index" -lt "$num_slaves" ]
do
slave_id=${slave_ids[$index]}
slave_host=${slave_hosts[$index]}
slave_port=${slave_ports[$index]}
CHECKSUM=`$prog_mk_table_checksum --replicate=$MYSQL_CHECKSUM --replicate-check 2 h=$slave_host,P=$slave_port,u=$MYSQL_USER,p=$MYSQL_PASS` || CHECKSUM="not consistent"
if [ "$CHECKSUM" ]; then
echo "Replication Slave ID $slave_id on $slave_host:$slave_port is inconsistent. Requires rebuild"
else
echo "Replication Slave ID $slave_id on $slave_host:$slave_port is consistent."
fi
let "index = $index + 1"
done
}
for arg in "$@"
do
case $arg in
-c) arg_c=true;;
*) usage;;
esac
done
if sanity_checks; then
sanity=true
if [ $arg_c ]; then
echo "Checking consistency"
check || error
else
usage
fi
else
error
fi
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment