Master-Slave Replication Configuration in MySQL: Complete Setup Guide

Introduction

MySQL master-slave replication is a fundamental database architecture pattern that enables data from one MySQL database server (the master) to be automatically copied to one or more MySQL database servers (the slaves). This powerful feature provides numerous benefits including improved read performance through load distribution, enhanced data redundancy for disaster recovery, and the ability to perform backups without impacting the primary database server.

Replication works by recording all data changes on the master server in binary logs, which are then transmitted to slave servers and replayed to keep the data synchronized. This asynchronous process allows the master to continue operating independently while slaves catch up with changes, making it an ideal solution for read-heavy applications where the majority of database operations are SELECT queries.

Understanding and properly implementing MySQL replication is crucial for building scalable, high-availability database infrastructures. This comprehensive guide will walk you through every step of configuring master-slave replication, from initial server preparation to monitoring and troubleshooting, ensuring you can confidently deploy and manage replicated MySQL environments.

Prerequisites

Before beginning the replication configuration, ensure you have the following:

Server Requirements

• Two or more Linux servers (Ubuntu 20.04+, CentOS 7+, or Debian 10+)
• One server designated as the master
• One or more servers designated as slaves
• Network connectivity between all servers
• Sufficient bandwidth for replication traffic

MySQL Installation

• MySQL 5.7+ or MySQL 8.0+ installed on all servers
• MariaDB 10.3+ is also compatible
• Root or administrative access to MySQL on all servers
• MySQL service running and accessible

System Requirements

• At least 2GB RAM per server (4GB+ recommended)
• Adequate disk space for database storage and binary logs
• Static IP addresses or reliable DNS names for all servers
• Synchronized system clocks (NTP recommended)

Firewall Configuration

• Port 3306 open between master and slave servers
• SSH access (port 22) for server management

Knowledge Requirements

• Basic MySQL administration skills
• Understanding of SQL syntax
• Linux command line proficiency
• Basic networking concepts

Understanding MySQL Replication

Replication Architecture

MySQL replication uses a binary log (binlog) based approach:

1. The master records all data changes in binary logs
2. Slaves connect to the master and request binary log events
3. The master sends binary log events to connected slaves
4. Slaves receive events and store them in relay logs
5. A SQL thread on each slave reads and executes relay log events

Replication Formats

MySQL supports three binary log formats:

Statement-Based Replication (SBR): Logs SQL statements. More compact but can cause inconsistencies with non-deterministic functions.

Row-Based Replication (RBR): Logs individual row changes. More reliable and consistent but generates larger logs.

Mixed Replication: Automatically switches between statement-based and row-based formats depending on the operation.

Replication Topology

Common replication topologies include:

• Single Master, Multiple Slaves: One master handles writes, multiple slaves handle reads
• Chain Replication: Master replicates to intermediate slaves, which replicate to additional slaves
• Master-Master: Two servers acting as both master and slave (advanced configuration)

Step-by-Step Configuration

Step 1: Configure the Master Server

First, connect to your master server and modify the MySQL configuration file.

For Ubuntu/Debian:

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

For CentOS/RHEL:

sudo nano /etc/my.cnf

Add or modify the following configuration under the [mysqld] section:

[mysqld]
# Server identification
server-id = 1

# Enable binary logging
log_bin = /var/log/mysql/mysql-bin.log

# Binary log format (ROW is recommended)
binlog_format = ROW

# Databases to replicate (optional - omit to replicate all)
# binlog_do_db = database1
# binlog_do_db = database2

# Databases to ignore (optional)
# binlog_ignore_db = mysql
# binlog_ignore_db = information_schema

# Binary log retention period (days)
expire_logs_days = 10

# Maximum binary log size before rotation
max_binlog_size = 100M

# Enable GTID for easier failover (MySQL 5.6+)
gtid_mode = ON
enforce_gtid_consistency = ON

# Ensure binary logs are sync'd to disk
sync_binlog = 1

# InnoDB settings for durability
innodb_flush_log_at_trx_commit = 1

Save the file and restart MySQL:

sudo systemctl restart mysql

Verify MySQL is running:

sudo systemctl status mysql

Step 2: Create Replication User on Master

Connect to MySQL on the master server:

mysql -u root -p

Create a dedicated replication user with appropriate privileges:

-- Create replication user
CREATE USER 'replication_user'@'%' IDENTIFIED WITH mysql_native_password BY 'StrongPassword123!';

-- Grant replication privileges
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';

-- Apply privilege changes
FLUSH PRIVILEGES;

For enhanced security, restrict the replication user to specific slave IP addresses:

-- Create replication user for specific slave
CREATE USER 'replication_user'@'192.168.1.20' IDENTIFIED WITH mysql_native_password BY 'StrongPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'192.168.1.20';
FLUSH PRIVILEGES;

Step 3: Record Master Binary Log Position

While still connected to the master MySQL server, lock the tables and get the binary log position:

-- Lock tables to prevent writes
FLUSH TABLES WITH READ LOCK;

-- Get current binary log file and position
SHOW MASTER STATUS;

You'll see output similar to:

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

Important: Write down the File name and Position number. You'll need these values when configuring the slave. Keep the MySQL session open with tables locked while you create the backup.

Step 4: Create Master Database Backup

Open a new terminal session to the master server and create a backup:

# Create backup directory
sudo mkdir -p /var/backups/mysql

# Create full database dump
sudo mysqldump -u root -p \
    --all-databases \
    --master-data=2 \
    --single-transaction \
    --flush-logs \
    --triggers \
    --routines \
    --events \
    > /var/backups/mysql/master_backup.sql

For large databases, add compression:

sudo mysqldump -u root -p \
    --all-databases \
    --master-data=2 \
    --single-transaction \
    --flush-logs \
    --triggers \
    --routines \
    --events | gzip > /var/backups/mysql/master_backup.sql.gz

After the backup completes, return to your MySQL session and unlock the tables:

UNLOCK TABLES;

Step 5: Transfer Backup to Slave Server

Transfer the backup file to your slave server:

# Using scp
scp /var/backups/mysql/master_backup.sql root@slave_server_ip:/tmp/

# Or with compression
scp /var/backups/mysql/master_backup.sql.gz root@slave_server_ip:/tmp/

Alternatively, use rsync for large files:

rsync -avz --progress /var/backups/mysql/master_backup.sql root@slave_server_ip:/tmp/

Step 6: Configure the Slave Server

Connect to your slave server and edit the MySQL configuration file:

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

Add the following configuration under [mysqld]:

[mysqld]
# Server identification (must be unique)
server-id = 2

# Enable relay log
relay-log = /var/log/mysql/mysql-relay-bin

# Binary logging on slave (optional, for cascading replication)
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW

# Make slave read-only to prevent accidental writes
read_only = 1

# Relay log settings
relay_log_recovery = 1
relay_log_purge = 1

# Enable GTID (must match master)
gtid_mode = ON
enforce_gtid_consistency = ON

# Skip errors (use cautiously)
# slave_skip_errors = 1062,1032

# Replication filters (optional)
# replicate_do_db = database1
# replicate_ignore_db = mysql

Save the configuration and restart MySQL:

sudo systemctl restart mysql

Step 7: Restore Master Backup on Slave

Connect to the slave server and import the master backup:

# If backup is compressed
gunzip /tmp/master_backup.sql.gz

# Import the backup
mysql -u root -p < /tmp/master_backup.sql

This process may take several minutes to hours depending on database size.

Step 8: Configure Replication on Slave

Connect to MySQL on the slave server:

mysql -u root -p

Configure the slave to connect to the master:

-- Stop slave if running
STOP SLAVE;

-- Configure master connection
CHANGE MASTER TO
    MASTER_HOST='master_server_ip',
    MASTER_USER='replication_user',
    MASTER_PASSWORD='StrongPassword123!',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;

-- Start slave replication
START SLAVE;

-- Check slave status
SHOW SLAVE STATUS\G

If using GTID-based replication (recommended for MySQL 5.6+):

STOP SLAVE;

CHANGE MASTER TO
    MASTER_HOST='master_server_ip',
    MASTER_USER='replication_user',
    MASTER_PASSWORD='StrongPassword123!',
    MASTER_AUTO_POSITION=1;

START SLAVE;

SHOW SLAVE STATUS\G

Step 9: Verify Replication Status

Check the slave status output for key indicators:

SHOW SLAVE STATUS\G

Look for these critical values:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Last_IO_Error:
Last_SQL_Error:

Both Slave_IO_Running and Slave_SQL_Running must be "Yes" for successful replication.

Step 10: Test Replication

On the master server, create a test database and table:

-- Connect to master
CREATE DATABASE replication_test;
USE replication_test;

CREATE TABLE test_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    test_data VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO test_table (test_data) VALUES ('Test replication data');

On the slave server, verify the data has been replicated:

-- Connect to slave
USE replication_test;
SELECT * FROM test_table;

You should see the data that was inserted on the master.

Advanced Configuration

Setting Up Multiple Slaves

To add additional slaves, repeat steps 6-10 for each new slave server, ensuring each has a unique server-id.

For the second slave, use:

server-id = 3

For the third slave:

server-id = 4

Implementing Semi-Synchronous Replication

Semi-synchronous replication provides better data safety by ensuring at least one slave receives the binary log before the master commits:

On the master:

-- Install plugin
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

-- Enable semi-sync
SET GLOBAL rpl_semi_sync_master_enabled = 1;

-- Set timeout (milliseconds)
SET GLOBAL rpl_semi_sync_master_timeout = 1000;

-- Verify status
SHOW VARIABLES LIKE 'rpl_semi_sync%';

On each slave:

-- Install plugin
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

-- Enable semi-sync
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

-- Restart slave threads
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

-- Verify status
SHOW VARIABLES LIKE 'rpl_semi_sync%';

Delayed Replication

Configure delayed replication to protect against accidental data deletion:

-- On slave server
STOP SLAVE;

-- Set 1-hour delay
CHANGE MASTER TO MASTER_DELAY = 3600;

START SLAVE;

-- Verify delay
SHOW SLAVE STATUS\G

Parallel Replication

Enable parallel replication for better performance on multi-core systems (MySQL 5.7+):

On slave:

STOP SLAVE SQL_THREAD;

SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 4;

START SLAVE SQL_THREAD;

Monitoring and Maintenance

Monitor Replication Lag

Create a monitoring script:

sudo nano /usr/local/bin/check-replication-lag.sh

#!/bin/bash

SLAVE_STATUS=$(mysql -e "SHOW SLAVE STATUS\G")
LAG=$(echo "$SLAVE_STATUS" | grep "Seconds_Behind_Master" | awk '{print $2}')

if [ "$LAG" = "NULL" ]; then
    echo "ERROR: Replication is not running!"
    exit 2
elif [ "$LAG" -gt 300 ]; then
    echo "WARNING: Replication lag is ${LAG} seconds"
    exit 1
else
    echo "OK: Replication lag is ${LAG} seconds"
    exit 0
fi

Make it executable:

sudo chmod +x /usr/local/bin/check-replication-lag.sh

Monitor Binary Log Size

On the master, monitor binary log disk usage:

sudo du -sh /var/log/mysql/

Configure automatic binary log purging:

-- Keep logs for 7 days
SET GLOBAL expire_logs_days = 7;

-- Or specify in days and hours (MySQL 8.0+)
SET GLOBAL binlog_expire_logs_seconds = 604800;

Create Monitoring Dashboard

Monitor key replication metrics:

-- Check replication status
SHOW SLAVE STATUS\G

-- View relay log position
SHOW RELAYLOG EVENTS;

-- Check binary log position on master
SHOW MASTER STATUS;

-- View replication errors
SELECT * FROM performance_schema.replication_applier_status_by_worker;

Verification and Testing

Verify Slave Configuration

-- Check slave is read-only
SHOW VARIABLES LIKE 'read_only';

-- Verify GTID status
SHOW VARIABLES LIKE 'gtid%';

-- Check relay log configuration
SHOW VARIABLES LIKE 'relay%';

Test Failover Readiness

Simulate master failure:

# On master
sudo systemctl stop mysql

On slave, promote to master:

-- Stop replication
STOP SLAVE;

-- Reset slave status
RESET SLAVE ALL;

-- Disable read-only mode
SET GLOBAL read_only = 0;

-- Verify promotion
SHOW MASTER STATUS;

Verify Data Consistency

Use pt-table-checksum from Percona Toolkit:

# Install Percona Toolkit
sudo apt-get install percona-toolkit

# Run checksum
pt-table-checksum --host=master_server --databases=your_database

Troubleshooting

Replication Not Starting

Issue: Slave_IO_Running or Slave_SQL_Running shows "No"

Solution: Check error logs and slave status:

SHOW SLAVE STATUS\G

Look at Last_IO_Error and Last_SQL_Error fields. Common causes include:

# Check network connectivity
ping master_server_ip
telnet master_server_ip 3306

# Verify credentials
mysql -h master_server_ip -u replication_user -p

# Check firewall
sudo ufw status
sudo firewall-cmd --list-all

Duplicate Key Errors

Issue: Error 1062: Duplicate entry

Solution: Skip the duplicate error:

-- Stop slave
STOP SLAVE;

-- Skip one error
SET GLOBAL sql_slave_skip_counter = 1;

-- Restart slave
START SLAVE;

-- Verify status
SHOW SLAVE STATUS\G

For GTID-based replication:

STOP SLAVE;
SET GTID_NEXT='problematic_gtid';
BEGIN;
COMMIT;
SET GTID_NEXT='AUTOMATIC';
START SLAVE;

Replication Lag Issues

Issue: High Seconds_Behind_Master value

Solution: Investigate and optimize:

-- Check slow queries on slave
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;

-- Enable parallel replication
STOP SLAVE SQL_THREAD;
SET GLOBAL slave_parallel_workers = 4;
START SLAVE SQL_THREAD;

-- Check for table locks
SHOW PROCESSLIST;

Binary Log File Not Found

Issue: Error: "Could not find first log file"

Solution: Reset slave and reconfigure:

STOP SLAVE;
RESET SLAVE;

-- Reconfigure with current master position
CHANGE MASTER TO
    MASTER_LOG_FILE='current_bin_file',
    MASTER_LOG_POS=current_position;

START SLAVE;

Connection Timeouts

Issue: Lost connection to MySQL server during query

Solution: Adjust timeout settings:

-- On slave
SET GLOBAL slave_net_timeout = 60;

-- In my.cnf
[mysqld]
slave_net_timeout = 60

Best Practices

1. Use GTID-Based Replication

Global Transaction Identifiers (GTIDs) simplify replication management and make failover easier. Always enable GTID for new setups.

2. Implement Monitoring and Alerting

Set up automated monitoring for:

• Replication lag (Seconds_Behind_Master)
• Slave thread status (IO and SQL threads)
• Binary log disk usage
• Network connectivity
• Error logs

3. Regular Backup Testing

Test slave backups regularly to ensure they're consistent and restorable. Perform test restores on a separate server monthly.

4. Document Your Replication Topology

Maintain clear documentation of:

• Server roles and IP addresses
• Replication configuration
• Failover procedures
• Contact information for database administrators

5. Secure Replication Traffic

Use SSL/TLS for replication traffic in production:

CHANGE MASTER TO
    MASTER_HOST='master_server_ip',
    MASTER_USER='replication_user',
    MASTER_PASSWORD='password',
    MASTER_SSL=1,
    MASTER_SSL_CA='/path/to/ca.pem',
    MASTER_SSL_CERT='/path/to/client-cert.pem',
    MASTER_SSL_KEY='/path/to/client-key.pem';

6. Plan for Capacity

Monitor disk space for binary and relay logs. Configure automatic log rotation and purging to prevent disk space issues.

7. Use Read-Only Mode on Slaves

Always configure slaves as read-only to prevent accidental writes:

SET GLOBAL read_only = 1;
SET GLOBAL super_read_only = 1;  -- MySQL 5.7.8+

8. Implement Connection Pooling

Use connection pooling in your application layer to efficiently distribute read queries across multiple slaves.

9. Monitor Performance Impact

Replication can impact master performance. Monitor:

• Binary log generation rate
• Network bandwidth usage
• Disk I/O for binary logs

10. Have a Failover Plan

Document and regularly test your failover procedures:

• Promoting a slave to master
• Redirecting application traffic
• Reconfiguring remaining slaves
• Bringing old master back online

Conclusion

MySQL master-slave replication is a powerful feature that enables scalable, highly available database architectures. By following this comprehensive guide, you've learned how to configure, maintain, and troubleshoot MySQL replication from basic setup through advanced configurations.

Key accomplishments include setting up binary logging on the master, creating dedicated replication users with appropriate privileges, configuring one or more slave servers, implementing monitoring and alerting systems, and understanding how to troubleshoot common replication issues. You've also learned best practices for maintaining a healthy replication environment and ensuring data consistency across your database infrastructure.

Replication is not a replacement for backups, but rather a complementary technology that improves availability and read scalability. Continue to maintain regular backups of both master and slave servers, monitor replication lag and status regularly, test failover procedures periodically, and stay updated with MySQL security patches and updates.

As your infrastructure grows, consider more advanced topologies such as multi-source replication, group replication for automatic failover, or hybrid solutions combining replication with clustering technologies. The foundation you've built with this guide will serve you well as you scale and evolve your database architecture to meet increasing demands.