Introduction

In MySQL replication, the terms "Master" and "Slave" (more recently referred to as "Primary" and "Replica") are used to describe the relationship between databases in a replication setup. The Master database acts as the primary source of data, while the Slave is a copy that receives updates from the Master asynchronously. In some scenarios, you may need to promote a Slave to a Master, or the other way around, due to hardware failures, load balancing, maintenance, or planned upgrades.

This article explores the technical aspects of switching roles between Master and Slave databases in a MySQL replication environment.

Concepts of Switching Roles

Master Role

The Master server handles active data transactions and writes. It logs all changes to its binary logs, which can then be applied to the Slave databases. The Master server is responsible for:

• Handling all writes (INSERT, UPDATE, DELETE).
• Managing the binary log which tracks changes.
• Providing the binary log information to Slave servers.

Slave Role

The Slave server acts as a backup and a repository for distributed read operations. It synchronizes its data with the Master:

• Pulls changes from the Master's binary log.
• Applies these changes locally to maintain data consistency.
• Can be used for read-only queries to offload the Master.

Switching Roles Scenarios

Scenario 1: Promoting a Slave to a Master

When a Master server fails or needs to be replaced, promoting a Slave to a Master is necessary. This involves:

1. Stopping Replication: Cease any ongoing replication processes on the Slave.

   STOP SLAVE;

1. Ensuring Consistency: Verify that the Slave is fully up to date with the Master. The SHOW SLAVE STATUS \G; command can confirm this.
2. Promoting the Slave:
   • Disable read-only mode on the Slave (if enabled).
   • Restart the server as a master instance.

   SET GLOBAL read_only = OFF;

1. Reconfiguring Other Slaves: Adjust the Slave configurations of any other servers that were connected to the old Master to now connect to the new Master.

Scenario 2: Demoting a Master to a Slave

If a Slave is more powerful, you might want to demote a Master to a Slave. Positions need to be synchronized, and binary logs reassessed:

1. Sync the Slave up to the Master: Make sure the fallback Slave has all the logs from the current Master.
2. Stop Writes to Master: Temporarily halt operations on the Master to stabilize data states.
3. Change Master to New Master: Use the following to start replication from the new Master:

1   CHANGE MASTER TO MASTER_HOST='new_master_host', 
2   MASTER_USER='replication_user', 
3   MASTER_PASSWORD='password', 
4   MASTER_LOG_FILE='recorded log file', 
5   MASTER_LOG_POS=recorded log position;

1. Start Slave on Old Master:

   START SLAVE;

Technical Example

Below is a practical example of switching a Slave to become a Master.

1-- On the Slave that will be promoted
2STOP SLAVE;
3RESET MASTER;
4SET GLOBAL read_only = OFF;
5
6-- Update application connection strings to point to the new Master.
7
8-- On the other Slaves
9CHANGE MASTER TO MASTER_HOST='New Master IP',
10MASTER_USER='repl',
11MASTER_PASSWORD='password',
12MASTER_LOG_FILE='mysql-bin.000001',
13MASTER_LOG_POS=position;
14
15START SLAVE;

Table Summary

Additional Considerations

• Consistency: Always verify transaction consistency before promoting or demoting roles. It ensures there’s no data loss.
• Network Latencies: Switching roles can affect network load. Account for these changes in your load balancing strategy.
• Backup and Recovery: Regular backups are crucial before any role transition to prevent data loss.
• Session Configuration: After changing roles, ensure all session-based configurations are revisited for accuracy.

In conclusion, role reversal in a MySQL replication setup is a crucial process that allows database administrators to maintain availability and performance, especially during unexpected outages or planned upgrades. The adaptable nature of MySQL replication architectures makes it possible to achieve high performance and availability when role transition processes are well-understood and implemented.