MySQL Master-Slave Replication for Data Redundancy

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

Add the following lines:

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

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

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;

Step 3: Get Master Status#

Lock the database and note the master status:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

You’ll see output like:

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

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

After the export completes, unlock the tables:

UNLOCK TABLES;

Step 5: Configure Slave Server#

Edit the MySQL configuration on the slave server:

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

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

Restart MySQL:

sudo systemctl restart mysql

Step 6: Import Data to Slave#

If you exported data from master:

mysql -u root -p < master_backup.sql

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;

Start the slave:

START SLAVE;

Step 8: Verify Replication#

Check the slave status:

SHOW SLAVE STATUS\G

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!');

Check the slave:

USE production_db;
SELECT * FROM test_replication;

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

Monitoring and Maintenance#

Key Metrics to Monitor#

1. Replication Lag: Seconds_Behind_Master
2. Slave Status: Both IO and SQL threads should be running
3. Binary Log Size: Monitor disk space usage
4. 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#

1. Use Dedicated Replication User: Don’t use root for replication
2. Monitor Regularly: Set up alerts for replication issues
3. Test Failover: Regularly test your failover procedures
4. Secure Connections: Use SSL for replication traffic
5. Backup Binary Logs: Keep backups of binary logs
6. 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#

• MySQL Replication Documentation ↗
• MySQL High Availability Solutions ↗
• Percona XtraDB Cluster ↗

Happy replicating! 🚀