MySQL Master-Slave Replication for Data Redundancy • Krishan Chawla

Introduction#

In production environments, data redundancy and high availability are critical requirements. MySQL master-slave replication is a proven solution that ensures your data is safely replicated across multiple servers.

This guide will walk you through setting up MySQL master-slave replication step by step.

What is MySQL Replication?#

MySQL replication enables data from one MySQL database server (the master) to be copied to one or more MySQL database servers (the slaves). Replication is asynchronous by default; slaves do not need to be connected permanently to receive updates from the master.

Benefits of Replication#

Data Redundancy: Keep multiple copies of your data
Load Distribution: Distribute read queries across multiple slaves
Backup Solution: Use slaves for backups without locking the master
Disaster Recovery: Quick failover to slave in case of master failure

Prerequisites#

Before we begin, make sure you have:

MySQL 8.0 or higher installed on both servers
Root access to both servers
Network connectivity between servers
Basic understanding of MySQL

Step 1: Configure Master Server#

First, edit the MySQL configuration file on the master server:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

bash

Add the following lines:

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = production_db

ini

Important Configuration Parameters:

server-id: Unique identifier for the server (must be unique across all servers)
log_bin: Enable binary logging
binlog_do_db: Specify which database to replicate

Restart MySQL after making changes:

sudo systemctl restart mysql

bash

Step 2: Create Replication User#

On the master server, create a dedicated replication user:

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

sql

Step 3: Get Master Status#

Lock the database and note the master status:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

sql

You’ll see output like:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 154      | production_db|                  |
+------------------+----------+--------------+------------------+

plaintext

Important: Note the File and Position values - you’ll need them for slave configuration.

Step 4: Export Data from Master#

If you have existing data, export it:

mysqldump -u root -p --databases production_db > master_backup.sql

bash

After the export completes, unlock the tables:

UNLOCK TABLES;

sql

Step 5: Configure Slave Server#

Edit the MySQL configuration on the slave server:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

bash

Add these lines:

[mysqld]
server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = production_db

ini

Restart MySQL:

sudo systemctl restart mysql

bash

Step 6: Import Data to Slave#

If you exported data from master:

mysql -u root -p < master_backup.sql

bash

Step 7: Start Replication#

On the slave server, configure the master connection:

CHANGE MASTER TO
  MASTER_HOST='master_ip_address',
  MASTER_USER='replica',
  MASTER_PASSWORD='strong_password_here',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;

sql

Start the slave:

START SLAVE;

sql

Step 8: Verify Replication#

Check the slave status:

SHOW SLAVE STATUS\G

sql

Look for these indicators of successful replication:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0 (or a small number)

Testing Replication#

Create a test table on the master:

USE production_db;
CREATE TABLE test_replication (
    id INT PRIMARY KEY,
    message VARCHAR(100)
);

INSERT INTO test_replication VALUES (1, 'Replication is working!');

sql

Check the slave:

USE production_db;
SELECT * FROM test_replication;

sql

If you see the data, congratulations! Replication is working.

Monitoring and Maintenance#

Key Metrics to Monitor#

Replication Lag: Seconds_Behind_Master
Slave Status: Both IO and SQL threads should be running
Binary Log Size: Monitor disk space usage
Connection Status: Ensure slave can reach master

Common Issues and Solutions#

Issue: Slave_IO_Running: No

Check network connectivity
Verify replication user credentials
Check firewall rules

Issue: Replication Lag

Check slave server resources (CPU, memory, disk I/O)
Consider using semi-synchronous replication
Review long-running queries

Best Practices#

Use Dedicated Replication User: Don’t use root for replication
Monitor Regularly: Set up alerts for replication issues
Test Failover: Regularly test your failover procedures
Secure Connections: Use SSL for replication traffic
Backup Binary Logs: Keep backups of binary logs
Document Configuration: Maintain documentation of your setup

Conclusion#

You now have a working MySQL master-slave replication setup! This configuration provides data redundancy and can help with read load distribution.

Next Steps#

Set up monitoring using tools like MySQL Workbench or Prometheus
Implement automatic failover with MySQL Router or ProxySQL
Consider setting up semi-synchronous replication for better durability
Explore multi-source replication for complex scenarios

Additional Resources#

Happy replicating! 🚀