BLOG POSTS

MangoHost Blog / How to Set Up Replication in MySQL – Step-by-Step Guide

How to Set Up Replication in MySQL – Step-by-Step Guide

MySQL replication is a cornerstone technology that allows you to maintain identical copies of data across multiple database servers, providing high availability, load distribution, and backup solutions. Whether you’re scaling a growing application, implementing disaster recovery, or distributing read operations across multiple servers, understanding MySQL replication is crucial for any serious database deployment. This comprehensive guide will walk you through setting up MySQL master-slave replication from scratch, covering the technical internals, step-by-step configuration, troubleshooting common issues, and best practices that’ll save you headaches down the road.

How MySQL Replication Works

MySQL replication operates on a relatively straightforward principle: the master server logs all data-changing operations (INSERT, UPDATE, DELETE) to a binary log (binlog), and slave servers connect to read and replay these logged operations on their local datasets.

The process involves three main threads:

Master dump thread: Reads the binary log and sends events to slave servers
Slave I/O thread: Connects to the master, requests log events, and writes them to a relay log
Slave SQL thread: Reads the relay log and executes the events against the local database

This asynchronous process means there’s typically a small delay between when data changes on the master and when it appears on slaves. For applications requiring immediate consistency, you’ll want to consider MySQL’s semi-synchronous replication or MySQL Cluster instead.

Prerequisites and Environment Setup

Before diving into configuration, ensure you have:

Two or more MySQL server instances (version 5.7+ recommended)
Network connectivity between servers
Sufficient disk space for binary logs and relay logs
Root or administrative access to all MySQL instances

For this guide, I’ll use the following setup:

Master server: 192.168.1.100 (mysql-master)
Slave server: 192.168.1.101 (mysql-slave)

If you’re testing this setup, consider using VPS instances to simulate a real distributed environment.

Step-by-Step Master Server Configuration

Start by configuring the master server, which will serve as the source of truth for your replicated data.

Configure the Master MySQL Instance

Edit the MySQL configuration file (typically /etc/mysql/mysql.conf.d/mysqld.cnf on Ubuntu or /etc/my.cnf on CentOS):

[mysqld]
# Server identification
server-id = 1

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

# Binary log format (ROW is recommended for consistency)
binlog-format = ROW

# Database to replicate (optional - omit to replicate all databases)
binlog-do-db = production_db

# Databases to ignore (optional)
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema

# Binary log retention (days)
expire-logs-days = 7

# Maximum binary log file size
max-binlog-size = 100M

Restart the MySQL service to apply changes:

sudo systemctl restart mysql

Create a Replication User

Connect to the master MySQL instance and create a dedicated user for replication:

mysql -u root -p

CREATE USER 'replication_user'@'192.168.1.101' IDENTIFIED BY 'strong_replication_password';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'192.168.1.101';
FLUSH PRIVILEGES;

For broader network access, you can use wildcards, but be mindful of security implications:

CREATE USER 'replication_user'@'%' IDENTIFIED BY 'strong_replication_password';

Lock Tables and Get Master Status

Before setting up the slave, you need to obtain the master’s binary log position:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

You’ll see output similar to:

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

Note down the File and Position values – you’ll need them for slave configuration.

Step-by-Step Slave Server Configuration

Now configure the slave server to connect to and replicate from the master.

Configure the Slave MySQL Instance

Edit the slave’s MySQL configuration file:

[mysqld]
# Unique server identification (must be different from master)
server-id = 2

# Enable relay logging
relay-log = /var/log/mysql/mysql-relay-bin
relay-log-index = /var/log/mysql/mysql-relay-bin.index

# Read-only mode (recommended for slaves)
read-only = 1

# Databases to replicate (should match master settings)
replicate-do-db = production_db

# Skip errors (use cautiously)
# slave-skip-errors = 1062,1053,1146

Restart the MySQL service:

sudo systemctl restart mysql

Configure Slave Replication

Connect to the slave MySQL instance and configure the replication parameters:

mysql -u root -p

CHANGE MASTER TO
    MASTER_HOST='192.168.1.100',
    MASTER_USER='replication_user',
    MASTER_PASSWORD='strong_replication_password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;

Start the slave replication:

START SLAVE;

Verify Replication Status

Check the slave status to ensure replication is working correctly:

SHOW SLAVE STATUS\G

Look for these key indicators of successful replication:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_Error: (should be empty)
Seconds_Behind_Master: (should be 0 or a small number)

Unlocking Master Tables

Don’t forget to unlock the master tables once slave replication is confirmed working:

# On master server
UNLOCK TABLES;

Testing Your Replication Setup

Create a test database and verify replication:

# On master server
CREATE DATABASE replication_test;
USE replication_test;
CREATE TABLE test_table (id INT AUTO_INCREMENT PRIMARY KEY, data VARCHAR(255));
INSERT INTO test_table (data) VALUES ('Test replication data');

# On slave server
USE replication_test;
SELECT * FROM test_table;

If you see the inserted data on the slave, congratulations – your replication is working!

Common Issues and Troubleshooting

Even with careful setup, replication issues can occur. Here are the most common problems and solutions:

Slave Connection Issues

If Slave_IO_Running shows “No”:

Check network connectivity between servers
Verify firewall settings (MySQL typically uses port 3306)
Confirm replication user credentials and permissions
Check master server’s bind-address configuration

# Test connectivity from slave to master
telnet 192.168.1.100 3306

# Check MySQL bind address
grep bind-address /etc/mysql/mysql.conf.d/mysqld.cnf

Binary Log Position Errors

If the master log file or position is incorrect:

STOP SLAVE;
CHANGE MASTER TO MASTER_LOG_POS=new_position;
START SLAVE;

Duplicate Key Errors

Common when replication starts mid-stream:

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

For persistent issues, consider setting slave-skip-errors in the configuration, but use this cautiously as it can lead to data inconsistencies.

Large Seconds_Behind_Master Values

If slaves are lagging significantly:

Check slave server performance and resources
Consider using parallel replication (MySQL 5.7+)
Optimize slow queries identified in the relay log
Upgrade to dedicated servers with better I/O performance

MySQL Replication Types Comparison

Replication Type	Consistency	Performance Impact	Use Case	Setup Complexity
Asynchronous (Standard)	Eventual	Low	Read scaling, backups	Low
Semi-synchronous	Enhanced	Medium	Critical applications	Medium
Group Replication	Strong	High	High availability clusters	High
Master-Master	Eventual	Medium	Multi-region writes	High

Real-World Use Cases and Applications

MySQL replication shines in several scenarios:

Read Scaling for Web Applications

Configure your application to send SELECT queries to slave servers while directing INSERT/UPDATE/DELETE operations to the master. This pattern works particularly well for content-heavy websites where reads significantly outnumber writes.

# Example PHP configuration
$master_db = new PDO('mysql:host=192.168.1.100;dbname=app', $user, $pass);
$slave_db = new PDO('mysql:host=192.168.1.101;dbname=app', $user, $pass);

// Use slave for reads
$stmt = $slave_db->prepare("SELECT * FROM articles WHERE published = 1");

// Use master for writes
$stmt = $master_db->prepare("INSERT INTO user_actions (user_id, action) VALUES (?, ?)");

Disaster Recovery and Backups

Maintain geographically distributed slaves for disaster recovery. You can perform backups from slave servers without impacting master performance:

# Backup from slave without stopping replication
mysqldump --single-transaction --routines --triggers --all-databases > backup.sql

Analytics and Reporting

Run heavy analytical queries against slave databases to avoid impacting production performance. Many organizations maintain dedicated reporting slaves with additional indexes optimized for analytical workloads.

Performance Optimization and Best Practices

To maximize replication performance and reliability:

Binary Log Optimization

Use ROW-based replication for consistency
Configure appropriate sync_binlog values (1 for durability, higher values for performance)
Monitor binary log disk usage and configure log rotation

# Add to master configuration
sync_binlog = 1
binlog_cache_size = 1M
max_binlog_cache_size = 128M

Slave Performance Tuning

# Slave-specific optimizations
relay_log_recovery = ON
slave_parallel_workers = 4  # MySQL 5.7+
slave_parallel_type = LOGICAL_CLOCK  # MySQL 5.7+
slave_preserve_commit_order = ON

Monitoring and Alerting

Set up monitoring for key replication metrics:

# Script to check replication lag
#!/bin/bash
LAG=$(mysql -u monitor -p -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master" | awk '{print $2}')
if [ "$LAG" -gt 60 ]; then
    echo "CRITICAL: Replication lag is $LAG seconds"
    exit 2
fi

Security Considerations

Securing your replication setup is crucial:

Use strong passwords for replication users
Restrict replication user access to specific IP addresses
Enable SSL for replication connections in production
Regularly rotate replication user credentials

# Enable SSL replication
CHANGE MASTER TO
    MASTER_HOST='192.168.1.100',
    MASTER_USER='replication_user',
    MASTER_PASSWORD='strong_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';

Advanced Configuration Options

For production environments, consider these advanced settings:

Multi-Source Replication

MySQL 5.7+ supports replicating from multiple masters:

CHANGE MASTER TO
    MASTER_HOST='master1.example.com',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='password'
    FOR CHANNEL 'master1';

CHANGE MASTER TO
    MASTER_HOST='master2.example.com',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='password'
    FOR CHANNEL 'master2';

Delayed Replication

Create time-delayed slaves for protection against accidental data corruption:

CHANGE MASTER TO MASTER_DELAY = 3600;  # 1 hour delay

For comprehensive documentation on advanced replication features, refer to the official MySQL replication documentation.

MySQL replication is a powerful tool that, when properly configured and monitored, provides excellent scalability and reliability benefits. Start with basic master-slave replication to understand the concepts, then gradually incorporate advanced features as your infrastructure requirements grow. Remember that replication is just one piece of a comprehensive database strategy – combine it with proper backup procedures, monitoring, and disaster recovery planning for a robust production environment.

This article incorporates information and material from various online sources. We acknowledge and appreciate the work of all original authors, publishers, and websites. While every effort has been made to appropriately credit the source material, any unintentional oversight or omission does not constitute a copyright infringement. All trademarks, logos, and images mentioned are the property of their respective owners. If you believe that any content used in this article infringes upon your copyright, please contact us immediately for review and prompt action.

This article is intended for informational and educational purposes only and does not infringe on the rights of the copyright owners. If any copyrighted material has been used without proper credit or in violation of copyright laws, it is unintentional and we will rectify it promptly upon notification. Please note that the republishing, redistribution, or reproduction of part or all of the contents in any form is prohibited without express written permission from the author and website owner. For permissions or further inquiries, please contact us.