Differences

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

Link to this comparison view

Both sides previous revision Previous revision
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 ​hesitateCome after the recommendations of bright men and do 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>​
mysql-circular-replication-automated-handling.txt · Last modified: 2012/07/31 12:46 by greebo
CC Attribution-Noncommercial-Share Alike 4.0 International
Valid CSS Driven by DokuWiki do yourself a favour and use a real browser - get firefox!! Recent changes RSS feed Valid XHTML 1.0 ipv6 ready