Switching slave from master-slave to chain replication in MySQL

Introduction

MySQL replication is a widely used technique for maintaining a live backup of your data, ensuring high availability, and spreading read queries across multiple servers. The traditional master-slave replication model has been a popular choice for many years. However, there are other replication techniques that may offer better performance and fault tolerance, such as chain replication.

In this blog post, we will explore the benefits of chain replication, discuss the key differences between master-slave and chain replication, and provide a step-by-step guide to switch your existing MySQL setup from master-slave replication to chain replication.

Understanding Chain Replication

Chain replication is a linear replication model where each server in the chain is responsible for forwarding write requests to the next server in the chain. The last server in the chain sends a confirmation back to the first server, which then acknowledges the write request to the client. This model can provide better fault tolerance and performance, especially for large-scale distributed systems.

1.1 Benefits of Chain Replication

  • Improved fault tolerance: Chain replication can continue to function even if one or more servers in the chain fail, as long as the first and last servers remain operational.
  • Load balancing: Chain replication allows you to distribute read requests across all servers in the chain, effectively balancing the load and increasing the overall throughput.
  • Reduced latency: Since write requests only need to traverse the chain once, chain replication can offer lower latency compared to master-slave replication, where writes must be propagated to all slaves.

Differences between Master-Slave and Chain Replication

Before diving into the steps required to switch from master-slave to chain replication, let’s quickly go over the key differences between these two replication models.

2.1 Master-Slave Replication

  • The master server is responsible for processing all write requests and propagating them to all slave servers.
  • Slave servers can process read requests only.
  • The replication process is parallel, with each slave receiving updates from the master independently.
  • In case of master failure, a new master must be promoted from the existing slaves.

2.2 Chain Replication

  • Write requests are processed by the first server in the chain and forwarded to the next server until they reach the last server.
  • All servers in the chain can process read requests.
  • The replication process is linear, with each server forwarding updates to the next server in the chain.
  • In case of a server failure, the chain can be reconfigured to exclude the failed server without promoting a new master.

Switching to Chain Replication: A Step-By-Step Guide

Now that we have a good understanding of chain replication and its benefits, let’s walk through the steps required to switch your existing MySQL master-slave replication setup to chain replication.

Step 1: Prepare your servers

Before making any changes to your replication setup, it’s important to ensure that all your servers are running the same version of MySQL and have sufficient resources to handle the new replication model.

Step 2: Update the MySQL configuration

Update the MySQL configuration on each server to enable chain replication. This can be done by modifying the ‘my.cnf’ file and adding the following lines:

[mysqld]
log-bin
binlog_format=ROW

Step 3: Configure the chain replication topology

On each server in the chain, configure the ‘master_host’ and ‘master_port’ settings to point to the next server in the chain. For example, on Server A:

[mysqld]
master_host=server_b_ip
master_port=3306

On the last server in the chain, configure the ‘master_host’ and ‘master_port’ settings to point to the first server in the chain to complete the loop. For example, on Server C:

[mysqld]
master_host=server_a_ip
master_port=3306

Step 4: Configure replication user and permissions

On each server in the chain, create a replication user and grant the necessary permissions. For example:

CREATE USER 'repl_user'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;

Step 5: Stop the existing master-slave replication

On each slave server, stop the existing replication process by executing the following command:

STOP SLAVE;

Step 6: Reset and synchronize the existing data

Before starting chain replication, it’s essential to ensure that all servers in the chain have the same data. This can be achieved by creating a backup of the master server’s data and restoring it on each server in the chain.

Step 7: Start chain replication

On each server in the chain, execute the following command to start the chain replication process:

CHANGE MASTER TO MASTER_HOST='next_server_ip', MASTER_PORT=3306, MASTER_USER='repl_user', MASTER_PASSWORD='repl_password', MASTER_AUTO_POSITION=1;
START SLAVE;

Make sure to replace ‘next_server_ip’, ‘repl_user’, and ‘repl_password’ with the appropriate values for your setup.

Step 8: Verify the chain replication status

To confirm that chain replication is working correctly, check the replication status on each server in the chain by executing the following command:

SHOW SLAVE STATUS\G;

Look for the ‘Slave_IO_Running’ and ‘Slave_SQL_Running’ fields, which should display ‘Yes’ if the replication is functioning correctly.

Conclusion

Switching from master-slave to chain replication in MySQL can provide significant benefits in terms of fault tolerance, load balancing, and reduced latency. By following the steps outlined in this guide, you can successfully transition your existing MySQL replication setup to the more efficient and resilient chain replication model.

Related Articles