Perform consistency checks on replicated MySQL databases

mysql_consistency.sh

#!/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