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

Avinash Pawar
  • 216
  • 2
  • 11

1 Answers1

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;
Avinash Pawar
  • 216
  • 2
  • 11