MySQL Replication Interview Questions

Master MySQL Replication with interview-focused questions covering Binary Logs, Source-Replica Architecture, GTID, Row-Based Replication, Statement-Based Replication, Semi-Synchronous Replication, Replication Lag, Failover, Read Scaling, and production best practices.

Introduction

Replication is one of the most important features of MySQL for building highly available, scalable, and fault-tolerant systems.

Instead of relying on a single database server, replication copies data from one server to one or more replica servers.

Replication provides

High Availability
Read Scaling
Disaster Recovery
Backup Servers
Reporting Servers
Fault Tolerance

Almost every enterprise production MySQL deployment uses replication.

MySQL Replication Architecture

flowchart LR

Application --> LoadBalancer

LoadBalancer --> Primary

LoadBalancer --> Replica1

LoadBalancer --> Replica2

Primary --> BinaryLog

BinaryLog --> Replica1

BinaryLog --> Replica2

1. What is MySQL Replication?

Answer

Replication is the process of copying data from one MySQL server to one or more replica servers.

It ensures

Data Redundancy
High Availability
Read Scalability

2. Why is Replication required?

Without replication

Database Crash

↓

Application Down

With replication

Primary Failure

↓

Replica Promotion

↓

Application Continues

3. What are the components of MySQL Replication?

Main components

Primary Server
Replica Server
Binary Log
Relay Log
Replication Threads

Replication Components

flowchart LR

Primary --> BinaryLog

BinaryLog --> Replica

Replica --> RelayLog

RelayLog --> Database

4. What is a Primary Server?

The Primary Server

Accepts Reads
Accepts Writes
Generates Binary Logs
Sends changes to replicas

5. What is a Replica Server?

Replica Server

Receives changes
Replays transactions
Supports read queries
Improves scalability

6. What is Binary Log (binlog)?

Binary Log stores

INSERT
UPDATE
DELETE
DDL

operations executed on the Primary.

It is the source for replication.

Binary Log Flow

flowchart LR

Transaction --> BinaryLog --> Replica

7. What is Relay Log?

Replica receives Binary Log events.

They are stored in

Relay Log

before being executed.

8. What are Replication Threads?

Replica uses

IO Thread
SQL Thread

Modern MySQL versions also support multiple applier workers for parallel replication.

9. What does the IO Thread do?

IO Thread

Connects to Primary
Reads Binary Log
Writes Relay Log

10. What does the SQL Thread do?

SQL Thread

Reads Relay Log

↓

Executes SQL

↓

Synchronizes Replica.

Replication Flow

flowchart LR

Primary --> BinaryLog --> IOThread --> RelayLog --> SQLThread --> Replica

11. What is GTID?

GTID

Global Transaction Identifier

Every transaction gets

a globally unique ID.

Example

UUID:100

12. Why is GTID useful?

Benefits

Simplifies Failover
Easier Recovery
Prevents Duplicate Transactions
Automatic Position Tracking

13. What is File Position Replication?

Older replication mechanism.

Replica tracks

Binary Log File

+

Position

14. Difference between GTID and File Position?

GTID	File Position
Transaction Based	File Offset Based
Easier Failover	Manual Tracking
Recommended	Legacy

15. What are Replication Formats?

Three formats

Statement Based
Row Based
Mixed

16. What is Statement-Based Replication (SBR)?

Replicates SQL statements.

Example

UPDATE Employee

SET salary=10000

WHERE id=10;

17. Advantages of Statement-Based Replication

Smaller Binary Logs
Less Network Traffic

18. Disadvantages of Statement-Based Replication

Problems with

Non-deterministic Functions
NOW()
RAND()
UUID()

May produce inconsistent results.

19. What is Row-Based Replication (RBR)?

Replicates

actual row changes

instead of SQL statements.

Row-Based Replication

flowchart LR

UpdateRow --> BinaryLog --> ReplicaRowUpdate

20. Advantages of Row-Based Replication

More Reliable
Accurate
Deterministic
Recommended

21. Disadvantages of Row-Based Replication

Larger Binary Logs
Higher Storage Usage

22. What is Mixed Replication?

Automatically chooses

Statement

Row

depending on the query.

23. What is Asynchronous Replication?

Primary

commits immediately

without waiting

for replicas.

Default replication mode.

24. What is Semi-Synchronous Replication?

Primary waits

until

at least one replica

acknowledges receiving the transaction before confirming the commit.

Provides better durability than asynchronous replication.

Replication Modes

flowchart LR

Asynchronous --> Fast

SemiSync --> Safer

25. What is Replication Lag?

Delay between

Primary

and

Replica.

26. What causes Replication Lag?

Slow Disk
Heavy Writes
Large Transactions
Network Latency
Slow SQL Thread

27. How do you monitor Replication?

Useful commands

SHOW REPLICA STATUS\G;

(Older versions use SHOW SLAVE STATUS\G.)

Important fields

Seconds_Behind_Source
Replica_IO_Running
Replica_SQL_Running

28. What is Read Scaling?

Applications send

Writes

↓

Primary

Reads

↓

Replicas

Read Scaling

flowchart LR

Application --> Primary

Application --> Replica1

Application --> Replica2

29. What is Failover?

Primary fails

↓

Replica promoted

↓

Application reconnects

↓

Business continues.

30. What is Automatic Failover?

Tools like

MySQL InnoDB Cluster
MySQL Router
Orchestrator
MHA

can automatically promote replicas.

Failover Architecture

flowchart LR

Primary --> Crash

Crash --> ReplicaPromotion

ReplicaPromotion --> NewPrimary

31. Banking Example

Money Transfer

↓

Primary

↓

Replicated

↓

Standby Server

32. E-Commerce Example

Orders

↓

Primary

Product Search

↓

Replica

33. Reporting Example

Reports

↓

Replica

Production Writes

↓

Primary

No performance impact.

34. Disaster Recovery Example

Primary Data Center

↓

Failure

↓

Replica promoted

↓

Minimal downtime.

35. Common Replication Problems

Replication Lag
Network Failures
Binary Log Corruption
Large Transactions
Replica Drift
Disk Bottlenecks

36. Enterprise Best Practices

Enable GTID.
Prefer Row-Based Replication.
Monitor replication lag.
Use SSD storage.
Keep transactions short.
Enable Binary Logs.
Regularly test failover.
Use automatic failover tools.
Monitor replica health.
Backup replicas independently.

Replication Workflow

flowchart LR

Application --> Primary --> BinaryLog --> Replica --> ReadQueries

Quick Revision

Topic	Key Point
Primary	Handles Writes
Replica	Read Scaling
Binary Log	Source of Replication
Relay Log	Temporary Replica Log
IO Thread	Copies Binary Log
SQL Thread	Applies Changes
GTID	Transaction Identifier
RBR	Row Changes
SBR	SQL Statements
Mixed	Automatic Choice
Replication Lag	Delay Between Servers
Failover	Replica Promotion

Interview Tips

Interviewers frequently ask

What is MySQL Replication?
Explain Binary Log.
What is Relay Log?
GTID vs File Position Replication.
Row-Based vs Statement-Based Replication.
What is Replication Lag?
How do you monitor replication?
Explain Read Scaling.
Explain Failover.
What replication mode is recommended?

Always explain that modern production systems generally use GTID-based, Row-Based Replication because it provides reliable replication, simpler failover, and easier disaster recovery. Also mention that replication improves availability and scalability but does not replace regular backups, since accidental deletes or corrupted data can also replicate to replicas.

Summary

MySQL Replication enables high availability, disaster recovery, and read scalability by copying data from a Primary server to one or more Replica servers. Using Binary Logs, Relay Logs, GTIDs, and Row-Based Replication, MySQL ensures reliable synchronization across servers.

Understanding replication architecture, GTIDs, Binary Logs, replication formats, failover mechanisms, monitoring, and production best practices is essential for Backend Developers, Database Engineers, DevOps Engineers, and Solution Architects working with enterprise MySQL deployments.