This is an old revision of the document!

Circular multimaster replication is poorly documented in the mysql circles. So putting together a script like this requires a lot of info hunting and testing. Right now it survives sql-bench with random node shutdowns, but I can't guarantee it won't eat your data ;) Feel free to improve with logging etc.

#!/bin/bash

#This working example assumes A->B->C->D->A replicaton setup
#with nodes named node-1, node-2, node-3 and node-4.
#
#see http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html
#and http://www.mysqlperformanceblog.com/2008/07/07/how-show-slave-status-relates-to-change-master-to
#also take a look at http://www.maatkit.org/

#### Variables ##################################

MYSQLBIN="/usr/bin/mysql"
ADMIN="/usr/bin/mysqladmin"
#user, pass, certs etc go into params
MYSQLPARAMS=""

#who should my master be

case "`hostname`" in
  node-1)
        MYMASTER="node-4"
        MASTERLIST="node-3:node-2"
        ;;
  node-2)
        MYMASTER="node-1"
        MASTERLIST="node-4:node-3"
        ;;
  node-3)
        MYMASTER="node-2"
        MASTERLIST="node-1:node-4"
        ;;
  node-4)
        MYMASTER="node-3"
        MASTERLIST="node-2:node-1"
        ;;
  *)
        echo "cannot figure out whom I belong"
        exit 1
        ;;
esac

#### Procedures #################################

query()
{
        echo "$@" | $MYSQLBIN -E $MYSQLPARAMS
}

query_host()
{
        echo "$2" | $MYSQLBIN -E -h $1 $MYSQLPARAMS
}

get_master_host()
{
        query "show slave status" | grep Master_Host | awk '{ print $2 }'
}

get_master_logfile()
{
        query_host "$1" "show master status" | grep File | awk '{ print $2 }'
}

get_master_logpos()
{
        query_host "$1" "show master status" | grep Position | awk '{ print $2 }'
}

change_master()
{
        query "slave stop"
        query "change master to master_host=\"$1\", master_log_file=\"$2\", master_log_pos=$3"
        query "slave start"
}

change_master_to()
{
        FILE=`get_master_logfile $1`
        POSITION=`get_master_logpos $1`
echo changing master to $1,$FILE,$POSITION
        change_master $1 $FILE $POSITION
}


check()
{
        $ADMIN -h $1 ping > /dev/null 2>&1
        return $?
}

check_current_master()
{
        failcnt=0
        check `get_master_host`
        [ $? -ne 0 ] && let "failcnt += 1"
        return $failcnt
}


#### Logic ######################################

check localhost
if [ $? -ne 0 ]
then
        echo local is dead, no point in continuing
        exit 1
fi

if [ "$MYMASTER" = "`get_master_host`" ]
then
        #normal situation
        check_current_master
        if [ $? -ne 0 ]
        then
                #my master is down, determine next best
                check "`echo $MASTERLIST | cut -f1 -d:`"
                if [ $? -ne 0 ]
                then
                        check "`echo $MASTERLIST | cut -f2 -d:`"
                        if [ $? -ne 0 ]
                        then
                                #looks like we are offline. no master candidate is accessible
                                echo panic
                        else
                                #two masters are not accessible. possible split brain scenario
                                echo split brain
                                NEWMASTER=`echo $MASTERLIST | cut -f2 -d:`
                                change_master_to $NEWMASTER
                        fi
                else
                        #failover to first on list
                        NEWMASTER=`echo $MASTERLIST | cut -f1 -d:`
                        change_master_to $NEWMASTER
                fi
        else
                #everything ok
                echo alles gute
        fi
else
        #failover situation, we must look at failing back
        echo want to fail back from `get_master_host` to $MYMASTER
        check $MYMASTER
        if [ $? -ne 0 ]
        then
                echo but $MYMASTER is not yet back
                #also check the current master
                check_current_master
                if [ $? -ne 0 ]
                then
                        #uhh now what
                        [ "`echo $MASTERLIST | cut -f1 -d:`" = "`get_master_host`" ] && REMAINING="`echo $MASTERLIST | cut -f2 -d:`" || REMAINING="`echo $MASTERLIST | cut -f1 -d:`"
                        echo $REMAINING is still there
                        change_master_to $REMAINING
                fi
        else
                change_master_to $MYMASTER
        fi
fi