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://dev.mysql.com/doc/refman/8.0/en/replication-solutions-switch.html
