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
Last activity: Jun 28, 2025, 11:05 PM