MySQL master-slave handoff
MySQL master-slave handoff
Based on traditional master-slave switching:
When the master goes down,
Method 1:
1. All slave IO threads will be interrupted because of the master downtime. At this point, stop SLAVE IO_THREAD and wait for the SQL thread to finish executing the events in relay log.
2. Select the biggest slave of Read_Master_Log_Pos and Exec_Master_Log_Pos to upgrade to the new master
3. Check the last position of the binary on each slave (check the event time to find it faster), for example, for CPOS, intercept the binary log of the new master from the post-CPOS log and import it into slave for data consistency
4. SHOW MASTER STATUS records the logfile and logpos of the new master on the new master
5. Change master points to the new master on all slave.
Method 2 (recommended):
1. All slave IO threads will be interrupted because of the master downtime. At this point, stop SLAVE IO_THREAD and wait for the SQL thread to finish executing the events in relay log.
2. Select the biggest slave of Read_Master_Log_Pos and Exec_Master_Log_Pos to upgrade to the new master
3. Check the last position of the binary on each slave (check the event time to find it faster), for example, for CPOS, find out the binary log of the new master since the log of CPOS.
4. Change master to directly points to the location of the log to start replication.
Method 3 (recommended):
The details are as follows:
Replicate master-slave switching based on GTID:
Problems with replication:
