How To Set Up MySQL Master-Master Replication: A Step-by-Step Guide

In today’s data-driven world, ensuring high availability and scalability for your database is crucial for any business. One effective strategy to achieve these goals is by implementing MySQL Master-Master replication. This setup allows two MySQL servers to act as both masters and slaves, meaning that each server can write data while also replicating changes to the other. In this blog post, we’ll walk you through the steps to set up MySQL Master-Master replication effectively.

What You Need

Before we dive into the setup, ensure you have the following requirements:

1. Two MySQL servers: Both should be networked and configured with the same version of MySQL.
2. Root access: You need administrative privileges on both servers to perform the installation and configuration.
3. IP addresses: Make sure you have the servers’ IP addresses handy, as you’ll need them in the configuration files.

Prerequisites

Make sure both servers have MySQL installed and running, and also verify that they can communicate with each other over the network. You can check this by pinging one server from the other.

Step 1: Configure MySQL on the First Server

1. Edit the MySQL configuration file: On your first server (Server A), open the MySQL configuration file, typically located at /etc/my.cnf or /etc/mysql/my.cnf.

   sudo nano /etc/my.cnf

2. Add or modify the following lines:

   [mysqld]
   server-id=1
   log_bin=mysql-bin
   binlog_do_db=your_database_name

Here, server-id uniquely identifies the server in the replication setup. You can set binlog_do_db to specify which databases you want to replicate.

3. Restart MySQL:

   sudo service mysql restart

4. Create a replication user: Log into MySQL as root and create a user for replication.

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

5. Obtain the binary log position:

   FLUSH TABLES WITH READ LOCK;
   SHOW MASTER STATUS;

Make a note of the log file and position, as you’ll need it for the second server.

Step 2: Configure MySQL on the Second Server

1. Edit the MySQL configuration file on your second server (Server B):

   sudo nano /etc/my.cnf

2. Add or modify the following lines:

   [mysqld]
   server-id=2
   log_bin=mysql-bin
   binlog_do_db=your_database_name

3. Restart MySQL:

   sudo service mysql restart

4. Create a replication user on Server B just like on Server A:

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

5. Obtain the binary log position from Server A: You can replicate the same steps as before to get the log file and position from Server A.

Step 3: Configure Replication

1. On Server B, run the following command in MySQL to set up replication from Server A:

   CHANGE MASTER TO
   MASTER_HOST='Server_A_IP',
   MASTER_USER='replicator',
   MASTER_PASSWORD='your_password',
   MASTER_LOG_FILE='your_log_file',
   MASTER_LOG_POS=your_log_position;

2. Start the replication process:

   START SLAVE;

3. Check replication status:

   SHOW SLAVE STATUS\G

Look for the values Slave_IO_Running and Slave_SQL_Running. Both should show Yes, indicating successful replication.

Step 4: Configure Replication in the Opposite Direction

Now, you need to configure Server A to replicate from Server B using the same procedure exact above, but swap Server A and Server B details.

1. On Server A, run:

   CHANGE MASTER TO
   MASTER_HOST='Server_B_IP',
   MASTER_USER='replicator',
   MASTER_PASSWORD='your_password',
   MASTER_LOG_FILE='your_log_file_from_B',
   MASTER_LOG_POS=your_log_position_from_B;

2. Start the slave:

   START SLAVE;

3. Check replication status:

   SHOW SLAVE STATUS\G

Step 5: Testing Your Setup

To ensure that the replication is working in both directions:

1. Create a test table on Server A:

   CREATE TABLE test_table (id INT AUTO_INCREMENT PRIMARY KEY, data VARCHAR(255));
   INSERT INTO test_table (data) VALUES ('Hello from A');

2. Check Server B to see if the data was replicated:

   SELECT * FROM test_table;

3. Repeat the process by creating a test entry on Server B and verify its presence on Server A.

Conclusion

Congratulations! You have successfully set up MySQL Master-Master replication. This setup not only provides high availability but also allows for read and write operations on both servers, enhancing your database’s performance and reliability.

However, keep in mind that with great power comes great responsibility. Regular backups and monitoring of replication lag are essential to maintain a healthy replication environment.

Should you experience any issues or have questions, don’t hesitate to reach out to our community at Greenhost.cloud or consult MySQL’s official documentation for further insights.

Happy coding, and may your databases be ever scalable!

This guide serves as a summary of the master-master replication setup. For advanced configurations or optimizations, consider consulting with a database administrator or a professional service.