Setting up master-master replication in MySQL

Introduction

Hey there, data aficionados! Ever found yourself in a situation where you need two MySQL servers to keep each other’s back? Master-master replication is here to save the day! In this article, we’ll walk you through the process of setting up master-master replication in MySQL, ensuring that your data stays safe and sound on two separate servers. Ready? Let’s dive in!

What is Master-Master Replication?

First things first, let’s make sure we’re all on the same page. Master-master replication, also known as multi-master replication, is a MySQL setup where two (or more) servers act as masters, meaning they can both read and write data. In this scenario, each server replicates the other’s data, providing a safety net in case one server goes down. It’s like having a buddy system for your databases!

The Perks of Master-Master Replication

  1. High availability: With two masters, your data is always available, even if one server goes down.
  2. Load balancing: Spread the load between two servers, speeding up response times and improving performance.
  3. Fault tolerance: If one server fails, the other can pick up the slack.
  4. Simplified backups: Back up one server while the other handles the load.

The Plan of Action

Ready for some hands-on action? Here’s the game plan for setting up master-master replication in MySQL:

  1. Configure each server as a master.
  2. Configure each server as a slave.
  3. Test the replication setup.

Sounds easy, right? Let’s go!

Step 1: Configure Each Server as a Master

First up, we need to make sure both servers are ready to play ball. Here’s how to configure each server as a master:

  1. Edit the MySQL configuration file (my.cnf) on both servers. Add the following lines:
server_id = 1 # or 2 for the second server
log_bin = /var/log/mysql/mysql-bin.log
relay_log = /var/log/mysql/mysql-relay-bin.log
binlog_do_db = myDatabase

Replace “myDatabase” with the name of the database you want to replicate. Save the file and restart MySQL on both servers:

sudo systemctl restart mysql

Step 2: Configure Each Server as a Slave

Now that both servers are masters, let’s turn them into slaves! Here’s how:

  1. On Server 1, create a replication user:
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
  1. Note the file and position of the binary log:
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+----------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------+
| mysql-bin.000001 |      107 | myDatabase   |                  |                |
+------------------+----------+--------------+------------------+----------------+
  1. On Server 2, configure it as a slave of Server 1:
rustCopy codemysql> CHANGE MASTER TO
    -> MASTER_HOST='server1_ip',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='repl_password',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=107;
  1. Repeat the process on Server 1, but this time, create a replication user on Server 2 and configure Server 1 as its slave.

Step3: Test the Replication Setup

With both servers configured as masters and slaves, it’s time to make sure everything’s working as it should. Let’s test the replication setup:

  1. On Server 1, create a new table and insert some data:
mysql> USE myDatabase;
mysql> CREATE TABLE test_table (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));
mysql> INSERT INTO test_table (name) VALUES ('John Doe');
  1. On Server 2, check if the data has been replicated:
mysql> USE myDatabase;
mysql> SELECT * FROM test_table;
+----+---------+
| id | name    |
+----+---------+
|  1 | John Doe|
+----+---------+
  1. On Server 2, insert some data and check if it’s replicated on Server 1:
mysql> INSERT INTO test_table (name) VALUES ('Jane Smith');
mysql> SELECT * FROM test_table;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | John Doe  |
|  2 | Jane Smith|
+----+-----------+
  1. On Server 1, confirm the replication:
mysql> SELECT * FROM test_table;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | John Doe  |
|  2 | Jane Smith|
+----+-----------+

Voila! If you see the same data on both servers, you’ve successfully set up master-master replication in MySQL. Give yourself a pat on the back!

Troubleshooting and Tips

As with any technical setup, things might not always go as planned. Here are some tips and troubleshooting steps to help you iron out any kinks in your master-master replication:

  1. Check the MySQL error log: If you run into issues, the error log is your best friend. It’ll give you clues about what’s going wrong.
  2. Verify the MySQL configuration: Make sure you’ve correctly configured both servers as masters and slaves, and that you’ve granted the necessary privileges to the replication user.
  3. Monitor replication status: Use the SHOW SLAVE STATUS command to monitor the replication status on both servers.
  4. Sync data before replication: If you’re working with existing data, ensure both servers have the same data before you start the replication process.

Conclusion

And there you have it! You’ve now set up master-master replication in MySQL, giving your data the double protection it deserves. With this setup, you can enjoy high availability, load balancing, fault tolerance, and simplified backups. So, go forth and conquer the world of data with your new buddy system!

Related Articles