Differences
This shows you the differences between two versions of the page.
Both sides previous revision Previous revision | Last revision Both sides next revision | ||
mysql-circular-replication-automated-handling [2009/05/25 00:35] 127.0.0.1 external edit |
mysql-circular-replication-automated-handling [2012/07/30 20:55] 188.143.232.12 aVEQCisXGUloDIbyb |
||
---|---|---|---|
Line 1: | Line 1: | ||
- | 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' | + | Don' |
- | Feel free to improve with logging etc. | + | |
- | + | ||
- | <code bash> | + | |
- | #!/bin/bash | + | |
- | + | ||
- | #This working example assumes A-> | + | |
- | #with nodes named node-1, node-2, node-3 | + | |
- | # | + | |
- | #see http:// | + | |
- | #and http:// | + | |
- | #also take a look at http:// | + | |
- | + | ||
- | #### Variables ################################## | + | |
- | + | ||
- | MYSQLBIN="/ | + | |
- | ADMIN="/ | + | |
- | #user, pass, certs etc go into params | + | |
- | MYSQLPARAMS="" | + | |
- | + | ||
- | #who should my master be | + | |
- | + | ||
- | case " | + | |
- | node-1) | + | |
- | MYMASTER=" | + | |
- | MASTERLIST=" | + | |
- | ;; | + | |
- | node-2) | + | |
- | MYMASTER=" | + | |
- | MASTERLIST=" | + | |
- | ;; | + | |
- | node-3) | + | |
- | MYMASTER=" | + | |
- | MASTERLIST=" | + | |
- | ;; | + | |
- | node-4) | + | |
- | MYMASTER=" | + | |
- | MASTERLIST=" | + | |
- | ;; | + | |
- | *) | + | |
- | echo " | + | |
- | exit 1 | + | |
- | ;; | + | |
- | esac | + | |
- | + | ||
- | #### Procedures ################################# | + | |
- | + | ||
- | query() | + | |
- | { | + | |
- | echo " | + | |
- | } | + | |
- | + | ||
- | query_host() | + | |
- | { | + | |
- | echo " | + | |
- | } | + | |
- | + | ||
- | get_master_host() | + | |
- | { | + | |
- | query "show slave status" | + | |
- | } | + | |
- | + | ||
- | get_master_logfile() | + | |
- | { | + | |
- | query_host " | + | |
- | } | + | |
- | + | ||
- | get_master_logpos() | + | |
- | { | + | |
- | query_host " | + | |
- | } | + | |
- | + | ||
- | change_master() | + | |
- | { | + | |
- | query "slave stop" | + | |
- | query " | + | |
- | query "slave start" | + | |
- | } | + | |
- | + | ||
- | change_master_to() | + | |
- | { | + | |
- | FILE=`get_master_logfile $1` | + | |
- | POSITION=`get_master_logpos $1` | + | |
- | echo changing master to $1, | + | |
- | change_master $1 $FILE $POSITION | + | |
- | } | + | |
- | + | ||
- | + | ||
- | check() | + | |
- | { | + | |
- | $ADMIN -h $1 ping > /dev/null 2>& | + | |
- | return $? | + | |
- | } | + | |
- | + | ||
- | check_current_master() | + | |
- | { | + | |
- | failcnt=0 | + | |
- | check `get_master_host` | + | |
- | [ $? -ne 0 ] && let " | + | |
- | return $failcnt | + | |
- | } | + | |
- | + | ||
- | + | ||
- | #### Logic ###################################### | + | |
- | + | ||
- | check localhost | + | |
- | if [ $? -ne 0 ] | + | |
- | then | + | |
- | echo local is dead, no point in continuing | + | |
- | exit 1 | + | |
- | fi | + | |
- | + | ||
- | if [ " | + | |
- | 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:`" = " | + | |
- | echo $REMAINING is still there | + | |
- | change_master_to $REMAINING | + | |
- | fi | + | |
- | else | + | |
- | change_master_to $MYMASTER | + | |
- | fi | + | |
- | fi | + | |
- | </ | + |