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