Sample Header Ad - 728x90

Promoting MySQL replica to primary during failover

0 votes
1 answer
570 views
The MySQL primary fails and I'm trying to promote the replica to primary. Following the official documentation , I've already: 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 3. set the primary server as super R/O 4. dumped all InnoDB databases from the failed primary server as a precaution 5. stopped the IO_THREAD of the replica 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 8. reset the GTID execution history and the binary log of the replica However, there are discrepancies between the failed primary and the new primary servers: - **failed primary server status**:
+---------------+----------+--------------+------------------+---------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                           |
+---------------+----------+--------------+------------------+---------------------------------------------+
| binlog.000073 |      197 |              |                  | c219365c-bd04-11ed-b1e2-525400003001:1-4620 |
+---------------+----------+--------------+------------------+---------------------------------------------+
- **new primary server status**:
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |      157 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
We can see that the positions are different and that the new primary has no 'Executed_Gtid_Set'. Either I missed something, or the doc is incomplete. Any suggestion?
Asked by jean-christophe manciot (121 rep)
Apr 26, 2023, 02:10 PM
Last activity: Jun 28, 2025, 11:05 PM