• 05 May, 2026
• 0 Comments
• 5 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

Multi-Channel Replication in MySQL

What is Multi-Channel Replication?

MySQL Multi-Channel Replication allows a single replica server to receive replication data from multiple primary servers simultaneously using separate replication channels.

Example Architecture

In this setup:

• Multi-3 receives replication from both Multi-1 and Multi-2
• Each replication stream uses a separate channel

Pre-Requisites for Multi-Channel Replication

Before configuring multi-channel replication, ensure:

Binary Logs Enabled

log_bin=mysql-bin

Unique Server IDs

server-id=1
server-id=2
server-id=3

Each server must have a unique ID.

Bind Address Configuration

bind-address=0.0.0.0

This allows remote replication connections.

MySQL Channel-Based Replication Setup

Why Use Channel-Based Replication?

Channel-based replication is useful for:

• Cost reduction
• Consolidated backup management
• Centralized analytics
• Reporting across multiple servers

Important Requirement

Database Names Must Be Unique

If multiple primary servers replicate to one replica, database names should not conflict.

Example:

✔ Good:

sales_db
hr_db

❌ Bad:

testdb on both servers

This can create replication conflicts.

Configuring Replication Channel

Example:

CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.1.10',
SOURCE_USER='repl_user',
SOURCE_PASSWORD='password'
FOR CHANNEL 'channel1';

For second server:

CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.1.20',
SOURCE_USER='repl_user',
SOURCE_PASSWORD='password'
FOR CHANNEL 'channel2';

Why Channel Names are Important

Without specifying channel names:

• Commands may affect all replication channels
• Replication management becomes risky

Example:

START REPLICA FOR CHANNEL 'channel1';

Using SysBench for MySQL Performance Testing

What is SysBench?

SysBench is a benchmarking and stress testing tool used to:

• Test MySQL performance
• Generate workload
• Simulate real-time traffic

SysBench Workflow

Prepare Phase

Creates tables and inserts initial data.

sysbench oltp_read_write prepare

Run Phase

Executes:

• SELECT
• UPDATE
• DELETE
• INSERT operations

sysbench oltp_read_write run

Monitoring MySQL Using General Logs

General logs help track:

• User activity
• Queries
• Replication-related operations

Enable general logs:

SET GLOBAL general_log = 'ON';

Check location:

SHOW VARIABLES LIKE 'general_log_file';

Replication Failure Scenario

Sri demonstrated a replication issue where:

• A row was deleted from the primary server
• Replica could not find the corresponding row
• Replication stopped with an error

Example error:

Could not execute Delete_rows event

Using SUPER READ ONLY Mode on Replica

To prevent accidental changes on replica:

SET GLOBAL super_read_only=ON;

Benefits:

✔ Prevents accidental DELETE/UPDATE
✔ Protects replication consistency
✔ Reduces human errors

MySQL Replication Troubleshooting

How to Identify Replication Errors

Run:

SHOW REPLICA STATUS\G

Check:

• Last_SQL_Error
• Last_IO_Error

Temporary Fix for Missing Rows

If replication fails due to missing rows:

Option 1: Insert Missing Row Manually

INSERT INTO table_name VALUES (...);

Then:

START REPLICA;

Why Skipping Errors is Dangerous

Although possible:

SET GLOBAL sql_slave_skip_counter = 1;

Sri recommended avoiding this because:

• Data inconsistency may occur
• Hidden corruption risks increase

Best Practice: Use Checksums

Instead of skipping errors:

• Run checksum validation
• Compare source and replica data consistency

This ensures:
✔ Accurate replication
✔ Data integrity
✔ Reliable troubleshooting

Key Learning from the Session

✔ Always use channel names in multi-channel replication
✔ Use super_read_only on replicas
✔ Avoid skipping replication errors blindly
✔ Validate data consistency using checksums
✔ Monitor replication regularly using logs and status commands