Master-Slave vs. Master-Master Replication in MySQL: A Complete Guide

Introduction

Database replication is an essential feature for high availability, load balancing, and disaster recovery in MySQL. This guide will provide a step-by-step process to set up Master-Slave and Master-Master replication in MySQL, helping you ensure seamless data synchronization across servers.

Master-Slave Replication Setup

Step 1: Configure the Master Server

Edit the MySQL configuration file on the master server:

vim /etc/my.cnf

Add the following lines under [mysqld]:

•     [mysqld]
      server-id = 1  
      binlog-format = mixed
      log-bin = mysql-bin
      innodb_flush_log_at_trx_commit = 1
      sync_binlog = 1
      # log-slave-updates = 1 (Uncomment for Master-Master replication)
  

  Save and exit (:wq!).

  Restart MySQL:

•     systemctl restart mysql
  

  Step 2: Create a Replication User

  Login to MySQL and create a replication user:

•     mysql -uroot -p
      GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY 'YOUR_PASSWORD';
      FLUSH PRIVILEGES;
  

  Step 3: Create a MySQL Dump

  To ensure data consistency, take a full backup of the master database:

•     sudo mysqldump -uroot -pYOUR_PASSWORD  --skip-lock-tables --single-transaction --flush-logs --master-data=2 -A | gzip -9 > /srv/mysqldump.sql.gz &
  

  Step 4: Transfer the Backup to the Slave Server

  Copy the dump file to the slave server:

scp /srv/mysqldump.sql.gz root@192.168.X.X:/tmp/

Slave Server Configuration

Step 1: Restore the MySQL Dump

Extract and import the backup:

cd /tmp/
gunzip mysqldump.sql.gz
mysql -uroot -pYOUR_PASSWORD < mysqldump.sql &

Step 2: Configure MySQL on the Slave Server

Edit the MySQL configuration file:

vim /etc/my.cnf

Add the following lines under [mysqld]:

[mysqld]
server-id = 2
binlog-format = mixed
log_bin = mysql-bin
relay-log = mysql-relay-bin
# log-slave-updates = 1 (Uncomment for Master-Master replication)

Save and exit (:wq!).

Restart MySQL:

systemctl restart mysql

Step 3: Get Master Log Position

To find the master log file and position, run:

head -n 80 mysqldump.sql | grep "MASTER_LOG_POS"

This will return values like:

MASTER_LOG_FILE='mysql-bin.000020', MASTER_LOG_POS=154;

Step 4: Configure the Slave to Replicate from the Master

Login to MySQL on the slave server and execute:

CHANGE MASTER TO MASTER_HOST='192.168.x.x', MASTER_USER='replication', MASTER_PASSWORD='YOUR_PASSWORD', MASTER_LOG_FILE='mysql-bin.000020', MASTER_LOG_POS=154;
START SLAVE;
SHOW SLAVE STATUS\G;

If the SHOW SLAVE STATUS\G; command displays Slave_IO_Running: Yes and Slave_SQL_Running: Yes, the replication is successfully configured.

Master-Master Replication Setup

For Master-Master replication, repeat the Master-Slave setup on another server and execute the following on the second master:

CHANGE MASTER TO MASTER_HOST='192.168.X.X', MASTER_USER='replication', MASTER_PASSWORD='YOUR_PASSWORD', MASTER_LOG_FILE='mysql-bin.000020', MASTER_LOG_POS=154;
START SLAVE;
SHOW SLAVE STATUS\G;

NOTE = Ensure that log-slave-updates = 1 is enabled on both servers.

Conclusion

• Master-Slave Replication is ideal for backup, read scalability, and disaster recovery.

• Master-Master Replication is best for high availability and load balancing.

By following this guide, you can set up MySQL replication efficiently to enhance database performance and reliability.