Bash Script – Script Based MySQL Table Replication on an Array of Slaves

This bash script only uses row count and MySQL checksum to determine table consistency. I created this script because I wanted a different option rather than using MySQL replication. I initially had replication set up and working between six sites but it would tend to break from time to time so I needed a different solution. In my situation, my data was only being updated once or twice a week. This script should only be used in cases where you’re not overly concerned about your data consistency and row count and checksum are sufficient enough to suit your needs. If you need a more accurate consistency check of your table data, look into the Percona Toolkit.

#!/bin/bash
#
# Sync DB Tables w/o Replication
# Author: Nathan Thomas 01/16/2014

#------#
# VARS #
#------#
# Server to sync DB from
MASTER='master01.fqdn.com'
MUSER='username'
MPASS='password'
DBNAME='mydbname'
TBLNAME='mytablename'
DUMP='/tmp/mydumpfile.sql'
LOGFILE='/var/log/mysql/mysql_repl_check.log'
MAILTO='my_email_addr@fqdn.com'

# Array of slave hostnames separated by a space
declare -a SLAVE=('slave01.fqdn.com' 'slave02.fqdn.com' 'slave03.fqdn.com')
# Array of slave usernames in same order
declare -a SUSER=('username1' 'username2' 'username3')
# Array of slave passwords in same order
declare -a SPASS=('password1' 'password2' 'password3')

#-----------#
# FUNCTIONS #
#-----------#
# Check table exists
# params: $1=username $2=password $3=hostname
function tableExists() {
        echo "`date "+%a %b%e %T"` - Called function 'tableExists' on $3." >> ${LOGFILE}
        echo "`date "+%a %b%e %T"` - Running query to verify table '${TBLNAME}' exists in database '${DBNAME}' on $3." >> ${LOGFILE}
        local QUERY="SELECT COUNT(1) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='${DBNAME}' AND TABLE_NAME='${TBLNAME}'"
        local RESULT=`mysql -u $1 -p$2 -h $3 -Bse "$QUERY" 2>&1`
        local RETVAL="$?"
        echo "`date "+%a %b%e %T"` - Query on $3 retured the following value: '${RESULT}'" >> ${LOGFILE}
        echo "`date "+%a %b%e %T"` - Exit status of last command on $3 returned: '${RETVAL}'" >> ${LOGFILE}
        if [ "${RETVAL}" -eq "0" -a "${RESULT}" -eq "0" 2>/dev/null ] ; then  # Retval 0, query returned 0, hide stderr, Table missing
                echo "`date "+%a %b%e %T"` - Table '${TBLNAME}' does not exist in database '${DBNAME}' on $3." >> ${LOGFILE}
                echo "`date "+%a %b%e %T"` - Function 'tableExists' completed successfully on $3." >> ${LOGFILE}
                return 1  # No dice
        elif [ "${RETVAL}" -eq "0" -a "${RESULT}" -eq "1" 2>/dev/null ] ; then  # Retval 0, query returned 1, hide stderr, Table exists
                echo "`date "+%a %b%e %T"` - Table '${TBLNAME}' exists in database '${DBNAME}' on $3." >> ${LOGFILE}
                echo "`date "+%a %b%e %T"` - Function 'tableExists' completed successfully on $3." >> ${LOGFILE}
                return 0  # All good
        else  # Some other error
                echo "`date "+%a %b%e %T"` - Unable to determine table '${TBLNAME}' exists in database '${DBNAME}' on $3." >> ${LOGFILE}
                echo "`date "+%a %b%e %T"` - We either encountered an error code in the SQL data, had a problem connecting to MySQL, or the last command exited with a nonzero status." >> ${LOGFILE}
                echo "`date "+%a %b%e %T"` - Function 'tableExists' did not finish successfully on $3." >> ${LOGFILE}
                return 2  # No dice
        fi
}

# Get row count
# params: $1=username $2=password $3=hostname
function getRowCount() {
        echo "`date "+%a %b%e %T"` - Called function 'getRowCount' on $3." >> ${LOGFILE}
        echo "`date "+%a %b%e %T"` - Verifying table '${TBLNAME}' exists in database '${DBNAME}' on $3." >> ${LOGFILE}
        tableExists $1 $2 $3
        if [ "$?" -eq "0" ] ; then  # Retval 0, table exists
                echo "`date "+%a %b%e %T"` - Running query to obtain row count on table '${TBLNAME}' in database '${DBNAME}' on $3." >> ${LOGFILE}
                local QUERY="SELECT COUNT(id) FROM ${TBLNAME}"
                local RESULT=`mysql -u $1 -p$2 -h $3 ${DBNAME} -Bse "${QUERY}" 2>&1`
                local RETVAL="$?"
                echo "`date "+%a %b%e %T"` - Query on $3 retured the following value: '${RESULT}'" >> ${LOGFILE}
                echo "`date "+%a %b%e %T"` - Exit status of last command on $3 returned: '${RETVAL}'" >> ${LOGFILE}
                if [ "${RETVAL}" -eq "0" -a -n "${RESULT}" -a "${RESULT}" -eq "${RESULT}" 2>/dev/null ] ; then  # Retval 0, check for nonzero string, is numeric, hide stderr
                        echo "${RESULT}"
                        echo "`date "+%a %b%e %T"` - The row count on $3 is: '${RESULT}'" >> ${LOGFILE}
                        echo "`date "+%a %b%e %T"` - Function 'getRowCount' completed successfully on $3." >> ${LOGFILE}
                        return 0  # All good
                else
                        echo "`date "+%a %b%e %T"` - Unable to retrieve row count on $3." >> ${LOGFILE}
                        echo "`date "+%a %b%e %T"` - We either encountered an error code in the SQL data, had a problem connecting to MySQL, or the last command exited with a nonzero status." >> ${LOGFILE}
                        echo "`date "+%a %b%e %T"` - Function 'tableExists' did not finish successfully on $3." >> ${LOGFILE}
                        return 1  # No dice
                fi
        fi
}

# Get table checksum
# params: $1=username $2=password $3=hostname
function getTableChecksum() {
        echo "`date "+%a %b%e %T"` - Called function 'getTableChecksum' on $3." >> ${LOGFILE}

        echo "`date "+%a %b%e %T"` - Verifying table '${TBLNAME}'s existence in database '${DBNAME}' on $3." >> ${LOGFILE}
        tableExists $1 $2 $3
        if [ "$?" -eq "0" ] ; then  # Retval 0, table exists
                echo "`date "+%a %b%e %T"` - Running query to obtain checksum on table '${TBLNAME}' in database '${DBNAME}' on $3." >> ${LOGFILE}
                local QUERY="CHECKSUM TABLE ${TBLNAME} EXTENDED'"
                local RESULT=`mysql -u $1 -p$2 -h $3 -Bse "${QUERY}" 2>&1 | awk '{ print $2 }'`
                local RETVAL="${PIPESTATUS[0]}"
                echo "`date "+%a %b%e %T"` - Query on $3 retured the following value: '${RESULT}'" >> ${LOGFILE}
                echo "`date "+%a %b%e %T"` - Exit status of last command on $3 returned: '${RETVAL}'" >> ${LOGFILE}
                if [ "${RETVAL}" -eq "0" -a -n "${RESULT}" -a "${RESULT}" -eq "${RESULT}" ] 2>/dev/null ; then  # Retval 0, check for nonzero string and if is numeric, hide stderr
                        echo "${RESULT}"
                        echo "`date "+%a %b%e %T"` - The table checksum on $3 is: '${RESULT}'" >> ${LOGFILE}
                        echo "`date "+%a %b%e %T"` - Function 'getTableChecksum' completed successfully on $3." >> ${LOGFILE}
                        return 0  # All good
                else
                        echo "`date "+%a %b%e %T"` - Unable to retrieve table checksum on $3." >> ${LOGFILE}
                        echo "`date "+%a %b%e %T"` - We either encountered an error code in the SQL data, had a problem connecting to MySQL, or the last command exited with a nonzero status." >> ${LOGFILE}
                        echo "`date "+%a %b%e %T"` - Function 'tableExists' did not finish successfully on $3." >> ${LOGFILE}
                        return 1  # No dice
                fi
        fi
}

# Backup master table
function backupMaster() {
        echo "`date "+%a %b%e %T"` - Called function 'backupMaster' on ${MASTER}." >> ${LOGFILE}
        echo "`date "+%a %b%e %T"` - Verifying table '${TBLNAME}'s existence in database '${DBNAME}' on ${MASTER}." >> ${LOGFILE}
        tableExists ${MUSER} ${MPASS} ${MASTER}
        if [ "$?" -eq "0" ] ; then  # Retval 0, table exists
                rm ${DUMP} 2>/dev/null  # Delete old dump file, hide output if doesn't exist
                echo "`date "+%a %b%e %T"` - Deleted old dump file '${DUMP}'." >> ${LOGFILE}
                echo "`date "+%a %b%e %T"` - Running mysqldump on table '${TBLNAME}' in database '${DBNAME}' on ${MASTER}." >> ${LOGFILE}
                local RESULT=`mysqldump -u ${MUSER} -p${MPASS} -h ${MASTER} ${DBNAME} ${TBLNAME} > ${DUMP} 2>&1`
                local RETVAL="$?"
                echo "`date "+%a %b%e %T"` - Mysqldump job on ${MASTER} retured the following value: '${RESULT}'" >> ${LOGFILE}
                echo "`date "+%a %b%e %T"` - Exit status of last command on ${MASTER} returned: '${RETVAL}'" >> ${LOGFILE}
                if [ "${RETVAL}" -eq "0" -a -z "${RESULT}" -a -f "${DUMP}" 2>/dev/null ] ; then  # Retval 0, check for null string and dump file exists, hide stderr
                        echo "`date "+%a %b%e %T"` - Master table successfully dumped to file '${DUMP}'." >> ${LOGFILE}
                        echo "`date "+%a %b%e %T"` - Function 'backupMaster' completed successfully on ${MASTER}." >> ${LOGFILE}
                        return 0  # All good
                else
                        echo "`date "+%a %b%e %T"` - Trouble retrieving dump file from master." >> ${LOGFILE}
                        echo "`date "+%a %b%e %T"` - We either encountered an error code from SQL, had a problem connecting to MySQL, or the last command exited with a nonzero status. There should not have been any value returned from the mysqldump job above." >> ${LOGFILE}
                        echo "`date "+%a %b%e %T"` - Function 'backupMaster' did not finish successfully on ${MASTER}." >> ${LOGFILE}
                        return 1  # No dice
                fi
        fi
}

# Drop old table
# params: $1=username $2=password $3=hostname
function dropTable() {
        echo "`date "+%a %b%e %T"` - Called function 'dropTable' on $3." >> ${LOGFILE}
        echo "`date "+%a %b%e %T"` - Verifying table '${TBLNAME}'s existence in database '${DBNAME}' on $3." >> ${LOGFILE}
        tableExists $1 $2 $3
        local RETVAL3="$?"
        echo "`date "+%a %b%e %T"` - Exit status of last command on $3 returned: '${RETVAL3}'" >> ${LOGFILE}
        if [ "${RETVAL3}" -eq "0" ] ; then  # Retval 0, table exists
                echo "`date "+%a %b%e %T"` - Running query to drop table '${TBLNAME}' in database '${DBNAME}' on $3." >> ${LOGFILE}
                local QUERY="DROP TABLE ${TBLNAME}"
                local RESULT=`mysql -u $1 -p$2 -h $3 ${DBNAME} -e "$QUERY" 2>&1`
                local RETVAL="$?"
                echo "`date "+%a %b%e %T"` - Query on $3 retured the following value: '${RESULT}'" >> ${LOGFILE}
                echo "`date "+%a %b%e %T"` - Exit status of last command on $3 returned: '${RETVAL}'" >> ${LOGFILE}
                if [ "${RETVAL}" -eq "0" -a -z "${RESULT}" 2>/dev/null ] ; then  # Retval 0 and string is null, hide stderr
                        echo "`date "+%a %b%e %T"` - Verifying table '${TBLNAME}'s existence in database '${DBNAME}' on $3." >> ${LOGFILE}
                        tableExists $1 $2 $3
                        local RETVAL2="$?"
                        echo "`date "+%a %b%e %T"` - Exit status of last command on $3 returned: '${RETVAL2}'" >> ${LOGFILE}
                        if [ "${RETVAL2}" -eq "1" ] ; then  # Retval 1, Table doesn't exist
                                echo "`date "+%a %b%e %T"` - Table '${TBLNAME}' was dropped from database '${DBNAME}' on $3." >> ${LOGFILE}
                                echo "`date "+%a %b%e %T"` - Function 'dropTable' completed successfully on $3." >> ${LOGFILE}
                                return 0  # All good
                        elif [ "${RETVAL2}" -eq "0" ] ; then  # Retval 0, Table still exists...but how??
                                echo "`date "+%a %b%e %T"` - Inexplicably, table '${TBLNAME}' still exists in database '${DBNAME}' on $3 after the last drop operation somehow. Hopefully you never get this message." >> ${LOGFILE}
                                echo "`date "+%a %b%e %T"` - Function 'dropTable' did not finish successfully on $3." >> ${LOGFILE}
                                return 1  # No dice
                        else
                                echo "`date "+%a %b%e %T"` - Unable to verify table '${TBLNAME}' exists in database '${DBNAME}' on $3." >> ${LOGFILE}
                                echo "`date "+%a %b%e %T"` - We either encountered an error code from SQL, had a problem connecting to MySQL, or the last call on function 'tableExists' exited with a status other than '0' or '1'." >> ${LOGFILE}
                                echo "`date "+%a %b%e %T"` - Function 'dropTable' did not finish successfully on $3." >> ${LOGFILE}
                                return 1  # No dice
                        fi
                else
                        echo "`date "+%a %b%e %T"` - Unable to drop table '${TBLNAME}' from database '${DBNAME}' on $3." >> ${LOGFILE}
                        echo "`date "+%a %b%e %T"` - We either encountered an error code from SQL, had a problem connecting to MySQL, or the last command exited with a nonzero status. There should not have been any value returned from the drop table query above." >> ${LOGFILE}
                        echo "`date "+%a %b%e %T"` - Function 'dropTable' did not finish successfully on $3." >> ${LOGFILE}
                        return 1  # No dice
                fi
        elif [ "${RETVAL3}" -eq "1" ] ; then  # Retval 1, table doesn't exist
                echo "`date "+%a %b%e %T"` - Table '${TBLNAME}' doesn't exist in database '${DBNAME}' on $3, no need to drop it." >> ${LOGFILE}
                echo "`date "+%a %b%e %T"` - Function 'dropTable' completed successfully on $3." >> ${LOGFILE}
                return 0  # All good
        else  # Retval 2 or ?
                echo "`date "+%a %b%e %T"` - Unable to verify table '${TBLNAME}' exists in database '${DBNAME}' on $3." >> ${LOGFILE}
                echo "`date "+%a %b%e %T"` - We either encountered an error code from SQL, had a problem connecting to MySQL, or the last call on function 'tableExists' exited with a status other than '0' or '1'." >> ${LOGFILE}
                echo "`date "+%a %b%e %T"` - Function 'dropTable' did not finish successfully on $3." >> ${LOGFILE}
                return 1  # No dice
        fi
}

# Restore table
# params: $1=username $2=password $3=hostname
function restoreTable() {
        echo "`date "+%a %b%e %T"` - Called function 'restoreTable' on $3." >> ${LOGFILE}
        echo "`date "+%a %b%e %T"` - Verifying table '${TBLNAME}' does not exist in database '${DBNAME}' on $3." >> ${LOGFILE}
        tableExists $1 $2 $3
        if [ "$?" -eq "1" ] ; then  # Retval 1, table doesn't exist, proceed
                if [ -f "${DUMP}" ] ; then  # If dump file exists, proceed
                        echo "`date "+%a %b%e %T"` - Dump file exists at '${DUMP}', attempting a restore operation." >> ${LOGFILE}
                        local RESULT=`mysql -u $1 -p$2 -h $3 ${DBNAME} < ${DUMP} 2>&1`
                        local RETVAL="$?"
                        echo "`date "+%a %b%e %T"` - Query on $3 retured the following value: '${RESULT}'" >> ${LOGFILE}
                        echo "`date "+%a %b%e %T"` - Exit status of last command on $3 returned: '${RETVAL}'" >> ${LOGFILE}
                        if [ "${RETVAL}" -eq "0" -a -z "${RESULT}" 2>/dev/null ] ; then  # Retval 0, check for null string, hide stderr
                                echo "`date "+%a %b%e %T"` - Restored table '${TBLNAME}' in database '${DBNAME}' on $3." >> ${LOGFILE}
                                echo "`date "+%a %b%e %T"` - Function 'restoreTable' completed successfully on $3." >> ${LOGFILE}
                                return 0  # All good
                        else
                                echo "`date "+%a %b%e %T"` - Failed to restore dump file on $3." >> ${LOGFILE}
                                echo "`date "+%a %b%e %T"` - We either encountered an error code from SQL, had a problem connecting to MySQL, or the last command exited with a nonzero status. There should not have been any value returned from the restore query above." >> ${LOGFILE}
                                echo "`date "+%a %b%e %T"` - Function 'restoreTable' did not finish successfully on $3." >> ${LOGFILE}
                                return 1  # No dice
                        fi
                else
                        echo "`date "+%a %b%e %T"` - No dump file exists to restore slave table on $3." >> ${LOGFILE}
                        echo "`date "+%a %b%e %T"` - Function 'restoreTable' did not finish successfully on $3." >> ${LOGFILE}
                        return 1  # No dice
                fi
        fi
}

# Check Slave Sync Status
# params: $1=username $2=password $3=hostname
function checkSlave() {
        echo "`date "+%a %b%e %T"` - Called function 'checkSlave' on $3." >> ${LOGFILE}
        local SNUMROWS=$( getRowCount $1 $2 $3 )
        if [ "$?" -eq "0" ] ; then  # Retval 0, Got the row count
                local STBLCHKSUM=$( getTableChecksum $1 $2 $3 )
                if [ "$?" -eq "0" ] ; then  # Retval 0, Got the table checksum
                        if [ "${MNUMROWS}" -eq "${SNUMROWS}" -a "${MTBLCHKSUM}" -eq "${STBLCHKSUM}" 2>/dev/null ] ; then
                                echo "`date "+%a %b%e %T"` - Table is in sync on $3." >> ${LOGFILE}
                                echo "`date "+%a %b%e %T"` - Master Row Count: ${MNUMROWS} Slave Row Count: ${SNUMROWS}" >> ${LOGFILE}
                                echo "`date "+%a %b%e %T"` - Master Table Checksum: ${MTBLCHKSUM} Slave Table Checksum: ${STBLCHKSUM}" >> ${LOGFILE}
                                echo "`date "+%a %b%e %T"` - Function 'checkSlave' completed successfully on $3." >> ${LOGFILE}
                                return 0  # Tables match
                        else
                                echo "`date "+%a %b%e %T"` - Table is out of sync on $3." >> ${LOGFILE}
                                echo "`date "+%a %b%e %T"` - Master Row Count: ${MNUMROWS} Slave Row Count: ${SNUMROWS}" >> ${LOGFILE}
                                echo "`date "+%a %b%e %T"` - Master Table Checksum: ${MTBLCHKSUM} Slave Table Checksum: ${STBLCHKSUM}" >> ${LOGFILE}
                                echo "`date "+%a %b%e %T"` - Function 'checkSlave' completed successfully on $3." >> ${LOGFILE}
                                return 1  # Tables out of sync
                        fi
                fi
        fi
}

# Re-sync Slave
# params: $1=username $2=password $3=hostname
function fixSlave() {
        echo "`date "+%a %b%e %T"` - Called function 'fixSlave' on $3." >> ${LOGFILE}
        backupMaster
        if [ "$?" -eq 0 ] ; then  # Retval 0, dumped master db
                dropTable $1 $2 $3
                if [ "$?" -eq "0" ] ; then  # Retval 0, table dropped
                        restoreTable $1 $2 $3
                        if [ "$?" -eq "0" ] ; then  # Reval 0, table restored
                                echo "`date "+%a %b%e %T"` - Table was restored. Checking consistency on $3." >> ${LOGFILE}
                                checkSlave $1 $2 $3
                                if [ "$?" -eq "0" ] ; then  # Retval 0, slave is in sync
                                        echo "`date "+%a %b%e %T"` - Function 'fixSlave' completed successfully on $3." >> ${LOGFILE}
                                        return 0  # All good in the hood home skillet
                                else
                                        echo "`date "+%a %b%e %T"` - Table restoration unsuccessful on $3." >> ${LOGFILE}
                                        echo "`date "+%a %b%e %T"` - Function 'fixSlave' completed successfully on $3." >> ${LOGFILE}
                                        return 1  # Manual intervention needed
                                fi
                        fi
                fi
        fi
}

#------#
# CODE #
#------#
echo "`date "+%a %b%e %T"` - Start of script processing." >> ${LOGFILE}
echo "`date "+%a %b%e %T"` - Getting master row count on ${MASTER}." >> ${LOGFILE}
MNUMROWS=$( getRowCount ${MUSER} ${MPASS} ${MASTER} )
if [ "$?" -eq "0" ] ; then  # Retval 0, got master row count
        echo "`date "+%a %b%e %T"` - Master row count on ${MASTER} is: '${MNUMROWS}'" >> ${LOGFILE}
        MTBLCHKSUM=$( getTableChecksum ${MUSER} ${MPASS} ${MASTER} )
        if [ "$?" -eq "0" ] ; then  # Retval 0, got master table checksum
                echo "`date "+%a %b%e %T"` - Master table checksum on ${MASTER} is: '${MTBLCHKSUM}'" >> ${LOGFILE}
                i=0  # Counter
                for hosts in "${SLAVE[@]}"  # Loop through slave array
                do
                        echo "`date "+%a %b%e %T"` - Starting checks on slave ${SLAVE[$i]}." >> ${LOGFILE}
                        checkSlave "${SUSER[$i]}" "${SPASS[$i]}" "${SLAVE[$i]}"
                        if [ "$?" -eq "0" ] ; then  # Retval 0, slave is nsync
                                echo "`date "+%a %b%e %T"` - Finished checks on slave ${SLAVE[$i]}." >> ${LOGFILE}
                                ((i++))
                        else
                                fixSlave "${SUSER[$i]}" "${SPASS[$i]}" "${SLAVE[$i]}"
                                RETVAL="$?"
                                if [ "${RETVAL}" -eq "0" ] ; then  # Retval 0, restore complete
                                        echo "`date "+%a %b%e %T"` - Restoration successful. ${SLAVE[$i]} is now in sync." >> ${LOGFILE}
                                elif [ "${RETVAL}" -eq "1" ] ; then  # Retval 1, restore failed
                                        echo "All attempts to sync the '${TBLNAME}' table in database '${DBNAME}' on server ${SLAVE[$i]} have failed.\r\nYou had better take a look at it when you get a chance." | /usr/bin/mail -s"Automated DB Restoration failed on ${SLAVE[$i]}" "${mailto}"
                                else
                                        echo "`date "+%a %b%e %T"` - An unspecified error occured while trying to fix ${SLAVE[$i]}, giving up."  >> ${LOGFILE}
                                fi
                                echo "`date "+%a %b%e %T"` - Finished checks on slave ${SLAVE[$i]}." >> ${LOGFILE}
                                ((i++))
                        fi
                done
        fi
fi
echo "`date "+%a %b%e %T"` - End of script processing." >> ${LOGFILE}

Leave a Reply