• 05 May, 2026
• 0 Comments
• 2 Mins Read

MySQL Replication

MySQL Replication: Complete Guide with Setup, Configuration & Real-Time Challenges

Introduction

MySQL Replication is one of the most important features for achieving high availability, scalability, and data redundancy in production environments. It allows data from one MySQL server (Primary) to be copied automatically to another server (Replica).

In this blog, we will cover:

• MySQL Replication concepts
• Step-by-step setup (MySQL 8.4)
• Port access, binary logs, server IDs
• Replication user configuration
• Primary-to-Primary replication
• MySQL vs SQL Server replication
• Real-world issues like duplicate key conflicts

What is MySQL Replication?

MySQL Replication is a process where:

• Primary (Source) → Writes data
• Replica (Slave) → Reads and applies changes

Replication is asynchronous by default, meaning:

• Primary does not wait for Replica confirmation
• Replica may lag behind

Types of MySQL Replication

1. Asynchronous Replication (Default)
2. Semi-Synchronous Replication
3. Group Replication (Advanced)

Prerequisites for Replication Setup

Before configuring replication, ensure:

Network Connectivity

• Primary and Replica should communicate over MySQL port:

  3306

• Open port using firewall:

  firewall-cmd --add-port=3306/tcp --permanent
  firewall-cmd --reload

Unique Server IDs

Each MySQL server must have a unique server-id.

Example:

Primary:

server-id=1

Replica:

server-id=2

Enable Binary Logs on Primary

Binary logs are mandatory for replication.

log_bin = mysql-bin
binlog_format = ROW

Restart MySQL after changes.

Step-by-Step MySQL Replication Setup (MySQL 8.4)

Step 1: Create Replication User on Primary

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

Step 2: Lock and Take Backup

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Note:

• File (e.g., mysql-bin.000001)
• Position (e.g., 157)

Take backup using:

mysqldump -u root -p --all-databases > dump.sql

Step 3: Restore Backup on Replica

mysql -u root -p < dump.sql

Step 4: Configure Replica

Run the new MySQL 8.4 command:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='primary_ip',
  SOURCE_USER='repl_user',
  SOURCE_PASSWORD='password',
  SOURCE_LOG_FILE='mysql-bin.000001',
  SOURCE_LOG_POS=157;

Step 5: Start Replication

START REPLICA;

Step 6: Verify Replication

SHOW REPLICA STATUS\G

Check:

• Replica_IO_Running: Yes
• Replica_SQL_Running: Yes

Important Configuration Considerations

Auto-Increment Handling (Multi-Write Setup)

When writing on both servers:

auto-increment-increment = 2
auto-increment-offset = 1   # Primary
auto-increment-offset = 2   # Replica

This avoids ID conflicts.

MySQL Primary-to-Primary Replication (Master-Master)

In this setup:

• Both servers act as Primary and Replica
• Each server replicates to the other

Configuration:

Run CHANGE REPLICATION SOURCE on both servers.

Major Risk: Writing on Both Servers

Although possible, it is dangerous in asynchronous replication.

Why?

Because:

• Replication lag may occur
• Both servers may generate same primary key

Real-Time Conflict Scenario (Very Important)

Example:

Server A inserts:

ID = 9, Value = I

Server B inserts:

ID = 9, Value = J

Now:

• Both try to replicate
• Duplicate key error occurs

Result:

• Replication breaks
• Data inconsistency

How to Handle Conflicts

1. Stop replication:

STOP REPLICA;

2. Identify conflict:

SHOW REPLICA STATUS\G

3. Fix manually:

• Delete conflicting row OR
• Update correct value

4. Restart:

START REPLICA;

Best Practice

• Avoid writes on both servers
• Use:
  • Primary → Writes
  • Replica → Reads

MySQL vs SQL Server Replication

Key Learnings from Real-Time Demonstration

• Always create a dedicated replication user
• Binary logs are mandatory
• Use CHANGE REPLICATION SOURCE (MySQL 8+)
• Avoid dual writes in async replication
• Proper coordination with application team is critical

Conclusion

MySQL Replication is powerful but must be used carefully.

Use it for:

• High availability
• Read scaling
• Backup strategy

Avoid:

• Writing on both servers without conflict control