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