Promoting MySQL replica to primary during failover

Question

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?

Answer 1

As it turns out, the issue lays within the official documentation:

On the replica Replica 1 being promoted to become the source, issue STOP REPLICA and RESET MASTER.

should be instead:

On the replica Replica 1 being promoted to become the source, issue STOP REPLICA and RESET REPLICA ALL.

I recreated both MySQL servers from scratch and applied the 7 steps from the first post and replaced the 8th one by:

8. Reset the replica with RESET REPLICA ALL;

On the primary before the switchover:

+---------------+----------+--------------+------------------+---------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                           |
+---------------+----------+--------------+------------------+---------------------------------------------+
| binlog.000010 |  1380513 |              |                  | a4228b68-e5b8-11ed-ac40-525400003001:1-1615 |
+---------------+----------+--------------+------------------+---------------------------------------------+

On the new primary (former replica) after the switchover:

+---------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                       |
+---------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+
| binlog.000010 |     6841 |              |                  | 61ab9e32-e5b9-11ed-b4aa-525400003002:1-511,
a4228b68-e5b8-11ed-ac40-525400003001:1-1615 |
+---------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+

It looks fine, except for the position which may or may not be a problem.