I have 3 MySQL servers which is running with circular replication.

  • Server A version - 5.5
  • Server B version - 5.6.21
  • Server C version - 5.6.21

Master Server FOR Slave Server

C                   FOR            A
A                   FOR            B
B                   FOR            C

Above structure is working well with circular replication.

Now, if Server C is down then automatically Server B becomes a master of Server A and Server A become master of Server B.

I want this kind of auto switching utility which reduce the manual coding.

I did Google but I did and after that I am really confused what to do and how any utility implement in my structure that monitor the Master server is alive or not.

  • You may need to do scripting to check for status periodically and then do what you want accordingly. – Nawaz Sohail Nov 19 '14 at 10:38
  • Thanks for reply, can please provide me any link or demo of that scrip so can able to use that. I already write one procedure to check the health of master is alive or not but some how it is not working properly. – sam Nov 19 '14 at 10:42
  • Circular replication is pure evil, you have 3 separate single points of failure, and unless you are using GTID replication, doing failover is complex and unreliable. I recomend against it. If you need 3 syncronized servers with write-anywhere, I would recommend having a look at Galera galeracluster.com – jynus Nov 19 '14 at 15:40
  • Sorry but still i am not able to write scrip to resolve my issue. Can you please help me. – sam Nov 20 '14 at 4:51

Following is the script just to give you a rough idea of how to proceed. Change as per your need.

Check for slave status using following command

if [ mysql -uroot -ppass -e "show global status like 'Slave_running'" -eq "OFF" ]
mysql -uuser -ppass -hhost -e "show master status" >file

tweak required things in variables from "file" and use in following change master command accordingly

mysql -uroot -ppass -hhost -e "Change master to master_host"

if [ mysql -uuser -ppas -Nbe "show global status like 'Slave_running'" |awk '{print $2}' = "OFF" ]; then
echo "Slave stopped"
echo "Slave running"

Hope it helps

  • Sorry but i am not able to write a proper script. Please help me for this. – sam Nov 20 '14 at 5:13
  • how do i change the master host in script. if [ mysql -uroot -padmin -e "show global status like 'Slave_running'" -eq "OFF" ] mysql -uroot -padmin -h192.192.4.222 -e "Change master to master_host" fi here what i have to write to change master host. – sam Nov 20 '14 at 6:42
  • if [ mysql -uroot -padmin -e "show global status like 'Slave_running'" -eq "OFF" ] then echo "Slave is not running"; else #echo "Slave is running"; fi – sam Nov 20 '14 at 7:30
  • Please let me know if i am wrong in following command. slave is stop for this server still following if condition is not satisfied. if [ mysql -uroot -padmin -e "show global status like 'Slave_running'" -eq "OFF" ] then echo "Slave is not running"; else #echo "Slave is running"; fi – sam Nov 20 '14 at 8:09
  • use following if [ mysql -uuser -ppas -Nbe "show global status like 'Slave_running'" |awk '{print $2}' ];then echo "Slave stopped" else echo "Slave running" fi – Nawaz Sohail Nov 20 '14 at 8:27

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.