77
What is database replication in MySQL and what are its types?
Level: Expert — A key scaling and high-availability topic for large-scale systems.
Replication is the process of copying data from one MySQL server (the source/master) to one or more other servers (replicas/slaves), keeping them synchronized. It's a foundation for scaling reads, high availability, and backups.
How it works: The source records all data changes in its binary log (binlog). Each replica reads this log and re-applies the same changes, staying in sync with the source.
Benefits of Replication
- Read scaling: Distribute read queries across replicas.
- High availability: A replica can take over if the source fails (failover).
- Backups: Run backups on a replica without loading the source.
- Analytics: Run heavy reports on a replica, isolating the production server.
Types of Replication
- Asynchronous (default): Source doesn't wait for replicas to confirm. Fast, but replicas can lag slightly behind.
- Semi-Synchronous: Source waits for at least one replica to acknowledge before committing. Safer, slightly slower.
- Synchronous (Group Replication): All nodes must confirm. Strong consistency, used in InnoDB Cluster.
Replication Formats (binlog)
| Format |
Logs |
Best For |
| Statement-Based (SBR) |
The actual SQL statements |
Compact logs |
| Row-Based (RBR) |
The changed row data |
Accuracy & safety (default) |
| Mixed |
Switches between SBR/RBR |
Balance of both |
Common Topologies
- Source–Replica: One source, one or many replicas (most common).
- Source–Source (Multi-Master): Two sources replicate to each other.
- Group Replication: Multiple nodes with automatic conflict handling (InnoDB Cluster).
Quick Example — Basic Replica Setup
-- ON THE SOURCE: create a replication user
CREATE USER 'repl'@'%' IDENTIFIED BY 'strong_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
-- Check the source's binlog position
SHOW MASTER STATUS; -- note File and Position
-- ON THE REPLICA: point it at the source
CHANGE REPLICATION SOURCE TO
SOURCE_HOST = '192.168.1.10',
SOURCE_USER = 'repl',
SOURCE_PASSWORD = 'strong_password',
SOURCE_LOG_FILE = 'mysql-bin.000001',
SOURCE_LOG_POS = 154;
-- Start replication and check status
START REPLICA;
SHOW REPLICA STATUS\G -- look for Replica_IO_Running: Yes
Interviewer tip: The one-liner they want — "Replication copies data from a source server to replicas using the binary log. It scales reads and provides high availability, and comes in asynchronous (default), semi-synchronous, and synchronous (group) types."