0

This is a production setup and I have to perform a failover from Master A to new potential master B.

MySQL Version is 5.7 and CentOS 7 is the OS.

I have a simple master slave setup with A as Master and B,C,D,E,F as async slaves. ProxySQL Cluster is also configured. Master A (writer in ProxySQL) and Slaves B and C (readers in ProxySQL) are added to ProxySQL.

On all the slaves log_slave_updates is ON and log_bin is also ON.

On Master A read_only=0 and Slaves B,C,D,E,F have read_only=1.

This is a GTID based replication with master_auto_position = 0.

A --> B,C,D,E,F this is the current replication topology

Now, I know for failover I have to choose either slave B or slave C because they are on proxy. I choose B as my new potential master. So, I have to move all the slaves from current Master A to new Master C. So the replication topology will be like this

B --> A,C,D,E,F -- proposed new topology

My question is how do I move the slaves from Master A to new potential Master B? How should I use the coordinates from new Master B? What procedure should I follow?

Once I move the slaves I guess failover will be easy, I just tweak the read_only flag on old master and new master and proxy will take care of the connections.

I would really appreciate if a detailed procedure with explanation is provided.

I have gone through following links but couldn't understand the process https://stackoverflow.com/questions/43960577/mysql-failover-how-to-choose-slave-as-new-master

https://serverfault.com/questions/303669/how-to-actually-use-mysql-slave-as-soon-the-master-is-failover-or-got-burnt

https://dev.mysql.com/doc/refman/8.0/en/replication-solutions-switch.html

1 Answer 1

0

I figured it out,

A --> B,C,D,E,F -- is my current topology B is chosen candidate master, for failover or switchover, I use following set of commands

On A

set persist read_only=0;
Kill the write connections 
show master status\G
show master status\G --> the coordinates should not be moving

On B

flush logs;
show master status\G
show master status\G --> the coordinates should not be moving

show slave status\G -- should be in sync seconds_behind_master =0, or you can compare show master status on A and show slave status\G on B
stop slave;
set persist read_only=0;
show processlist;

Monitor the proxy, the new master should be in the correct hostgroup.

Once the failover/switchover is done, using master coordinates on B (show master status\G above taken) issue the following on all the slaves inclding A

CHANGE MASTER TO master_host='B' , MASTER_USER='REPL', MASTER_PASSWORD='pwd', MASTER_LOG_FILE='binlog', MASTER_LOG_POS=pos;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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