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




Your Answer

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