Re: Failover/Switchover steps for Single Source Single Replica configuration

[Bob Stoneman]

"How to promote MySQL replica to primary during failover/switchover"
====================================================================

1. disabled the failed primary server on all load balancers

2. waited for the primary server to complete all its transactions:
"Source has sent all binlog to replica; waiting for more updates"

show master status\G

3. set the primary server as super R/O

set global read_only=1;
set global super_read_only=1;
show master status\G
shutdown;
vi /etc/mysql/mysql.conf.d/mysqld.cnf
read_only=1;
super_read_only=1;

--4. dumped all InnoDB databases from the failed primary server as a precaution --mysqldump --all-databases ???

5. stopped the IO_THREAD of the replica

STOP REPLICA IO_THREAD;
show replica status\G

6. waited for the replica to complete all its transactions:
"Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates"

7. stopped the SQL_THREAD of the replica

STOP REPLICA SQL_THREAD;

8. Reset the replica with 'RESET REPLICA ALL;'

RESET REPLICA ALL;
show replica status\G
show master status;


Any thoughts on this plan ?