Promote MySQL Replica to Master Database Safely

Promoting a MySQL replica to a master node is a critical database administration task required during planned maintenance, hardware upgrades, or disaster recovery. This article provides a step-by-step guide on how to safely transition a replica database to become the new primary master, ensuring data integrity, preventing split-brain scenarios, and minimizing application downtime.

Step 1: Prepare the Old Master (Planned Failover Only)

If you are performing a planned migration, you must prevent new writes to the current master to avoid data loss or divergence.

Set the old master to read-only mode:

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;
SET GLOBAL super_read_only = ON;
UNLOCK TABLES;

Record the master’s binary log coordinates to ensure the replica has processed all transactions.
```
SHOW MASTER STATUS;
```

Step 2: Ensure the Replica is Fully Caught Up

Before promotion, the target replica must apply all outstanding changes from the old master’s relay log.

Check replication status on the replica:
```
SHOW REPLICA STATUS\G
```
(Note: Use SHOW SLAVE STATUS\G if you are on MySQL versions older than 8.0.22).
Verify synchronization:
- Ensure Replica_IO_Running and Replica_SQL_Running are both Yes.
- Verify that Seconds_Behind_Master is 0.
- If using GTIDs (Global Transaction Identifiers), ensure Retrieved_Gtid_Set matches Executed_Gtid_Set.

Step 3: Stop Replication and Promote the Replica

Once the replica has fully caught up, you can safely disconnect it from the old master and elevate its status.

Stop the replication threads:
```
STOP REPLICA;
```
Reset the replication configuration to prevent the database from attempting to reconnect to the old master upon restart:
```
RESET REPLICA ALL;
```

Enable read-write mode on the newly promoted master:

SET GLOBAL super_read_only = OFF;
SET GLOBAL read_only = OFF;

Step 4: Redirect Application Traffic

With the new master now accepting writes, update your application configuration to point to the new node.

DNS/Load Balancer: If you use a proxy (like ProxySQL, HAProxy) or a DNS CNAME, update the routing rules to point to the new master’s IP address.
Direct Connections: If your applications connect via direct IP addresses, update the connection strings in your application configuration files and restart the application services to apply the changes.

Step 5: Reconfigure Remaining Replicas (If Applicable)

If you have other replica databases in your cluster, they must now be re-pointed to receive updates from the newly promoted master.

On each remaining replica, stop replication:
```
STOP REPLICA;
```

Point them to the new master (if using GTID replication):

CHANGE REPLICATION SOURCE TO 
  SOURCE_HOST='new_master_ip', 
  SOURCE_USER='replication_user', 
  SOURCE_PASSWORD='password', 
  SOURCE_AUTO_POSITION=1;

Start replication:
```
START REPLICA;
```