Replication - High Performance MySQL - page 491

12. Issue a CHANGE MASTER TO command on each replica, pointing it to the new master.
Use the binary log coordinates you gathered from SHOW MASTER STATUS .
When you promote a replica to a master, be sure to remove from it any
replica-specific databases, tables, and privileges. You also need to
change any replica-specific configuration parameters, such as a relaxed
innodb_flush_log_at_trx_commit option. Likewise, if you demote a
master to a replica, be sure to reconfigure it as needed.
If you configure your masters and replicas identically, you won't need
to change anything.
Unplanned promotions
If the master crashes and you have to promote a replica to replace it, the process might
not be as easy. If there's only one replica, you just use the replica. But if there's more
than one, you'll have to do a few extra steps to promote a replica to be the new master.
There's also the added problem of potentially lost replication events. It's possible that
some updates that have happened on the master will not yet have been replicated to
any of its replicas. It's even possible that a statement was executed and then rolled back
on the master, but not rolled back on the replica—so the replica could actually be
ahead of the master's logical replication position. 17 If you can recover the master's data
at some point, you might be able to retrieve the lost statements and apply them
manually.
In all of the following steps, be sure to use the Master_Log_File and Read_Master
_Log_Pos values in your calculations. Here is the procedure to promote a replica in a
master-and-replicas topology:
1. Determine which replica has the most up-to-date data. Check the output of SHOW
SLAVE STATUS on each replica and choose the one whose Master_Log_File/Read_
Master_Log_Pos coordinates are newest.
2. Let all replicas finish executing the relay logs they fetched from the old master
before it crashed. If you change a replica's master before it's done executing the
relay log, it will throw away the remaining log events and you won't know where
it stopped.
3. Perform steps 5-7 from the list in the preceding section.
4. Compare every replica's Master_Log_File/Read_Master_Log_Pos coordinates to
those of the new master.
5. Perform steps 10-12 from the list in the preceding section.
17. This really is possible, even though MySQL doesn't log any events until the transaction commits. See
“Mixing Transactional and Nontransactional Tables” on page 498 for the details. Another scenario
where this can happen is when the master crashes and recovers, but it didn't have innodb
_flush_log_at_trx_commit set to 1, so it loses some changes.