How to Set Up MySQL Replication: Master-Replica Configuration

Introduction

MySQL replication is a powerful feature that allows you to copy data from one MySQL server (the master) to one or more servers (the slaves/replicas). This guide walks you through setting up replication from scratch, covering everything you need to know.

If you're new to MySQL, start with our complete guide to installing MySQL on Ubuntu first.

Table of Contents

1. Prerequisites
2. What is MySQL Replication
3. Step 1: Configure the Master Server
4. Step 2: Create a Replication User
5. Step 3: Get Master Status
6. Step 4: Configure the Replica Server
7. Step 5: Verify Replication is Working
8. Step 6: Test Replication
9. Troubleshooting Common Issues
10. Best Practices
11. Conclusion
12. Frequently Asked Questions

Prerequisites

Before you start, ensure you have:

• Two or more MySQL servers (versions 5.7 or later, or MariaDB 10.x+)
• Network connectivity between servers with port 3306 open (or your custom MySQL port)
• SSH access to both servers
• Basic MySQL knowledge
• Root or administrative privileges on both servers

What is MySQL Replication?

Replication is the process of copying data and changes from a master database server to one or more replica servers. The master records all data modifications in its binary log, and replicas read and execute these logs to stay in sync.

Why Use Replication?

• High Availability - If your master fails, you have replicas ready to take over
• Load Balancing - Distribute read queries across multiple replicas
• Backups - Use replicas for backups without impacting the master
• Analytics - Run heavy queries on replicas without affecting production
• Data Distribution - Replicate data to geographically distant locations

Types of Replication

MySQL supports two main replication methods:

Binary Log Replication - Uses binary log file positions to track which changes have been replicated. More traditional and widely supported. Learn more in the MySQL documentation.

GTID Replication - Uses Global Transaction IDs for more reliable replication, especially in complex topologies. Easier to handle failovers. Read the official GTID guide.

This guide covers both approaches, with emphasis on binary log replication for simplicity.

Step 1: Configure the Master Server

The master server is where all write operations happen. It records every change in its binary log.

Enable Binary Logging

Edit your MySQL configuration file (/etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf):

[mysqld]
server-id=1
log_bin=/var/log/mysql/mysql-bin
binlog_format=ROW

Explanation:

• server-id=1 - Unique identifier for this server (must be different on each server)
• log_bin - Location where binary logs are stored
• binlog_format=ROW - Log format (ROW, STATEMENT, or MIXED; ROW is safest)

For detailed configuration options, see the MySQL Server System Variables documentation.

Restart MySQL

sudo systemctl restart mysql

Verify Binary Logging is Enabled

SHOW VARIABLES LIKE 'log_bin';

You should see log_bin = ON.

Step 2: Create a Replication User

The replica needs a dedicated user account to connect to the master and read the binary logs.

On the master server, run:

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

This creates a user called repl_user that can connect from any host (%) with the password repl_password. The REPLICATION SLAVE privilege allows this user to read the binary logs.

Step 3: Get Master Status

Before starting replication, you need to know the current binary log file and position on the master.

On the master server, run:

SHOW MASTER STATUS;

You'll see output like:

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
|------------------|----------|--------------|------------------|-------------------|
| mysql-bin.000014 | 398685532|              |                  |                   |

Note down the File and Position - you'll need these in the next step.

Step 4: Configure the Replica Server

Now set up your replica to connect to the master.

Set a Unique Server ID

Edit the MySQL configuration on the replica:

[mysqld]
server-id=2
relay-log=/var/log/mysql/mysql-relay-bin

• server-id=2 - Must be different from the master (use 3, 4, etc. for additional replicas)
• relay-log - Location for relay logs (optional but recommended)

Restart MySQL

sudo systemctl restart mysql

Configure Master Connection

On the replica server, run:

STOP SLAVE;
RESET SLAVE ALL;

CHANGE MASTER TO
  MASTER_HOST='master_ip_address',
  MASTER_PORT=3306,
  MASTER_USER='repl_user',
  MASTER_PASSWORD='repl_password',
  MASTER_LOG_FILE='mysql-bin.000014',
  MASTER_LOG_POS=398685532;

START SLAVE;

Replace:

• master_ip_address with your master's IP
• mysql-bin.000014 with the file from SHOW MASTER STATUS
• 398685532 with the position from SHOW MASTER STATUS

Step 5: Verify Replication is Working

On the replica server, check the replication status:

SHOW SLAVE STATUS\G

Look for these key fields:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Last_Error: (should be empty)

If both are Yes and there are no errors, replication is working!

Step 6: Test Replication

Let's verify that data actually replicates from master to replica.

On the master, create a test table and insert data:

CREATE DATABASE test_replication;
USE test_replication;
CREATE TABLE test_table (id INT, name VARCHAR(50));
INSERT INTO test_table VALUES (1, 'Alice');
INSERT INTO test_table VALUES (2, 'Bob');

On the replica, check if the data appeared:

USE test_replication;
SELECT * FROM test_table;

You should see the same two rows. If you do, replication is working correctly!

Troubleshooting Common Issues

Issue 1: Slave_IO_Running = No

The replica can't connect to the master.

Check:

• Network connectivity: ping master_ip
• Firewall allows port 3306
• Credentials are correct
• Master is running

Fix:

SHOW SLAVE STATUS\G

Look at Last_IO_Error for details. Then reconnect:

STOP SLAVE;
CHANGE MASTER TO
  MASTER_HOST='correct_ip',
  MASTER_PORT=3306,
  MASTER_USER='repl_user',
  MASTER_PASSWORD='repl_password';
START SLAVE;

Issue 2: Slave_SQL_Running = No

The replica encountered an error executing replicated queries.

Check the error:

SHOW SLAVE STATUS\G

Look at Last_Error. Common causes:

• Duplicate key error
• Table doesn't exist on replica
• Permission issues

To skip the error:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

Issue 3: Duplicate Server IDs

If both master and replica have the same server_id, replication fails with "equal server ids" error.

Fix: Each server must have a unique ID. Edit /etc/mysql/my.cnf:

[mysqld]
server-id=1  # master
server-id=2  # replica 1
server-id=3  # replica 2

Restart MySQL and replication will work.

Issue 4: GTID Errors (Out-of-Order Sequence)

If using GTID replication, you might see:

An attempt was made to binlog GTID 1-1-7369 which would create an out-of-order sequence

Fix: Disable strict GTID mode temporarily:

SET GLOBAL gtid_strict_mode=OFF;
START SLAVE;

Or reset the replica's GTID position:

STOP SLAVE;
RESET SLAVE ALL;
RESET MASTER;
START SLAVE;

Best Practices

1. Monitor Replication

Regularly check replica status:

SHOW SLAVE STATUS\G

Watch for:

• Seconds_Behind_Master increasing (replication lag)
• Any errors in Last_Error
• Slave_IO_Running or Slave_SQL_Running becoming No

2. Use Unique Server IDs

Always assign unique server IDs to every server in your replication setup. This prevents conflicts and confusion.

3. Secure Replication User

Limit the replication user's permissions and hosts:

CREATE USER 'repl_user'@'replica_ip' IDENTIFIED BY 'strong_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'replica_ip';

4. Handle Replication Failures

If replication fails and you can't fix it easily, you might need to resync. Consider:

• Using a backup from the master
• Using a tool like mysqldump to sync data
• Setting up fresh replication from scratch

5. Test Your Replicas

Periodically verify that replicas have the correct data by running sample queries on both master and replicas.

6. Use Read-Only Mode on Replicas

Prevent accidental writes to replicas:

SET GLOBAL read_only=ON;

This ensures replicas only receive changes from the master.

Conclusion

MySQL replication is a powerful tool for building scalable, highly available database systems. While setup is straightforward, understanding how to troubleshoot issues is key to running reliable replication in production.

Start with a simple master-replica setup, verify it's working, and gradually add more replicas as your needs grow.

Next Steps:

• For more advanced setups like master-master replication, check the MySQL Replication Best Practices guide
• Ready for the next level? Learn how to use replication with a proxy like MaxScale for load balancing and automatic failover
• Review our guide to installing MySQL on Ubuntu if you haven't set up your servers yet

Frequently Asked Questions