Differences

This shows you the differences between two versions of the page.

--- mysql-circular-replication-automated-handling [2012/07/30 20:55]
188.143.232.12 aVEQCisXGUloDIbyb
+++ mysql-circular-replication-automated-handling [2012/07/31 12:46] (current)
greebo old revision restored
@@ Line 1: / Line 1: @@
-Don't have time? Do not even have some time for for rest? Do different poplee suggest you to hire college essay writers? Don't hesitate! Come after the recommendations of bright men and do a right selection!
+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.
+<code bash>
+#!/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
+</code>