Replication basics in MySQL - Deep Dive

Overview - Replication basics

What is it?

Replication in MySQL is a process where data from one database server (called the master) is copied automatically to another server (called the slave). This helps keep the data synchronized between servers. It is used to improve data availability, backup, and load balancing. Replication happens continuously and automatically once set up.

Why it matters

Without replication, if the main database server fails, data could be lost or unavailable, causing downtime and lost business. Replication ensures there is a backup copy of data that can take over quickly. It also allows spreading read requests across servers, improving performance and user experience.

Where it fits

Before learning replication, you should understand basic MySQL database concepts like tables, queries, and server setup. After mastering replication basics, you can learn advanced topics like replication filtering, multi-source replication, and failover strategies.

Mental Model

Core Idea

Replication is like having a trusted assistant who copies every change you make in your notebook to their own notebook in real time.

Think of it like...

Imagine you write notes in your diary every day. Your friend sits next to you and writes down exactly what you write, so they have the same diary. If you lose your diary, your friend’s copy keeps your notes safe.

Master Server
┌───────────────┐
│  Writes data  │
└──────┬────────┘
       │  sends changes
       ▼
Slave Server
┌───────────────┐
│  Receives and │
│  applies data │
└───────────────┘

Build-Up - 7 Steps

1

Foundation

What is MySQL Replication?

Concept: Introduction to the basic idea of copying data from one server to another automatically.

MySQL replication means one server (master) sends all changes it makes to another server (slave). The slave keeps a copy of the master's data. This happens continuously without manual copying.

Result

You get two servers with the same data, where one is the main source and the other is a backup copy.

Understanding replication as automatic copying helps you see how data stays safe and available.

2

Foundation

Roles: Master and Slave Servers

Concept: Learn the two main roles in replication and their responsibilities.

The master server is where all data changes happen first. It records these changes in a special log called the binary log. The slave server reads this log and applies the same changes to its own data, keeping in sync.

Result

Clear roles help organize how data flows and keeps servers synchronized.

Knowing the roles clarifies why replication is one-way and how data consistency is maintained.

3

Intermediate

How Data Moves: Binary Log and Relay Log

🤔Before reading on: do you think the slave copies data directly from the master’s database or from a special log? Commit to your answer.

Concept: Understanding the logs that track and transfer changes between servers.

The master writes all changes to the binary log. The slave connects to the master and copies this log into its own relay log. Then the slave reads the relay log and applies changes to its database. This two-step process ensures reliability and order.

Result

Data changes flow smoothly and in order from master to slave, even if the connection is temporarily lost.

Knowing the log system explains how replication can recover from interruptions without losing data.

4

Intermediate

Setting Up Replication Step-by-Step

🤔Before reading on: do you think setting up replication requires changing server settings or just running a simple command? Commit to your answer.

Concept: Learn the practical steps to configure replication between two MySQL servers.

You enable binary logging on the master and create a replication user. On the slave, you configure it to connect to the master using this user and specify where to start copying from (log file and position). Then you start the slave process to begin replication.

Result

Replication starts running, and the slave begins copying data changes from the master.

Understanding setup steps helps you control replication and troubleshoot issues.

5

Intermediate

Read-Only Slave and Load Balancing

🤔Before reading on: do you think slaves can accept data changes from users or only read queries? Commit to your answer.

Concept: Learn how slaves are used to spread read queries and reduce load on the master.

Slaves usually are set to read-only mode to prevent accidental changes. Applications can send read queries to slaves and write queries to the master. This balances the load and improves performance.

Result

Faster response times and less stress on the master server.

Knowing slaves serve read queries explains why replication improves scalability.

6

Advanced

Handling Replication Delays and Conflicts

🤔Before reading on: do you think slaves always have exactly the same data as the master instantly? Commit to your answer.

Concept: Understand replication lag and how conflicts can happen in complex setups.

Replication is asynchronous by default, so slaves may lag behind the master by seconds or more. In multi-master or circular replication setups, conflicts can occur if the same data is changed in different places. Tools and careful design help detect and resolve these issues.

Result

You learn to monitor replication health and design systems to avoid data conflicts.

Understanding lag and conflicts prepares you for real-world replication challenges.

7

Expert

Semi-Synchronous Replication and Failover Strategies

🤔Before reading on: do you think replication always waits for slaves to confirm before committing changes? Commit to your answer.

Concept: Explore advanced replication modes that improve data safety and how to handle master failures.

Semi-synchronous replication waits for at least one slave to confirm receiving changes before the master commits them, reducing data loss risk. Failover strategies use monitoring and automation to switch to a slave if the master fails, ensuring high availability.

Result

Replication becomes safer and systems stay online even during failures.

Knowing advanced modes and failover helps build robust, production-ready database systems.

Under the Hood

MySQL replication works by the master recording all data changes in a binary log file. The slave connects to the master and requests this log. The slave copies the binary log into its relay log and then applies each change to its own data. This process is asynchronous by default, meaning the master does not wait for the slave to finish applying changes before continuing. The replication protocol uses a client-server connection over TCP/IP, with the slave acting as a client reading the master's binary log.

Why designed this way?

Replication was designed to be asynchronous to maximize performance and reduce master load. Synchronous replication would slow down writes because the master would wait for slaves to confirm. Using logs decouples data changes from replication, allowing recovery and replay. The binary log format is compact and efficient, and the relay log on the slave ensures ordered application of changes even if the connection drops temporarily.

Master Server
┌───────────────┐
│ Binary Log    │
│ (records all  │
│ changes)      │
└──────┬────────┘
       │
       │ TCP/IP connection
       ▼
Slave Server
┌───────────────┐
│ Relay Log     │
│ (copies from  │
│ binary log)   │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Applies data  │
│ changes to DB │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does replication guarantee zero delay between master and slave data? Commit to yes or no.

Common Belief: Replication means the slave always has exactly the same data as the master instantly.

Reality: Replication is asynchronous by default, so slaves can lag behind the master by seconds or more.

Why it matters: Assuming zero delay can cause applications to read stale data from slaves, leading to inconsistent user experiences.

Quick: Can slaves accept write queries from users? Commit to yes or no.

Common Belief: Slaves can be used just like masters and accept data changes from users.

Reality: Slaves are usually set to read-only mode and should not accept writes to avoid conflicts and data inconsistency.

Why it matters: Writing to slaves can cause data conflicts and break replication, risking data loss.

Quick: Does replication automatically protect against all data loss? Commit to yes or no.

Common Belief: Replication always guarantees no data loss even if the master crashes.

Reality: Asynchronous replication can lose recent changes if the master crashes before slaves receive them. Semi-synchronous replication reduces but does not eliminate this risk.

Why it matters: Relying solely on replication for backup can cause unexpected data loss without additional safety measures.

Quick: Is replication setup the same for all MySQL versions? Commit to yes or no.

Common Belief: Replication setup and behavior are identical across all MySQL versions.

Reality: Replication features and setup commands have evolved; newer versions support advanced modes like GTIDs and semi-synchronous replication.

Why it matters: Using outdated setup instructions can cause replication failures or missed features.

Expert Zone

1

Replication lag can be caused not only by network delay but also by heavy slave query load or slow disk I/O, which many overlook.

2

GTID-based replication simplifies failover and recovery but requires careful planning to avoid conflicts and ensure consistent server states.

3

Semi-synchronous replication improves durability but can reduce write throughput, so balancing safety and performance is a key expert decision.

When NOT to use

Replication is not suitable when you need immediate consistency across servers; in such cases, synchronous distributed databases or clustering solutions like Galera Cluster are better. Also, for very high write workloads, replication can become a bottleneck and alternative sharding or partitioning strategies might be preferred.

Production Patterns

In production, replication is often combined with monitoring tools to detect lag and failures automatically. Multi-slave setups distribute read load, and delayed slaves are used for point-in-time recovery. Experts use GTIDs for easier failover and semi-synchronous replication to reduce data loss risk. Backup strategies complement replication to ensure data safety.

Connections

Event Sourcing (Software Architecture)

Replication logs changes as events, similar to event sourcing recording state changes.

Understanding replication logs as event streams helps grasp how systems can rebuild state from change history.

Backup and Disaster Recovery

Replication provides a live copy of data, complementing traditional backups for faster recovery.

Knowing replication’s role alongside backups clarifies how to design resilient data systems.

Supply Chain Management

Replication’s master-slave data flow resembles supply chains where goods move from supplier to retailer.

Seeing replication as a flow of goods helps understand the importance of order, timing, and reliability in data synchronization.

Common Pitfalls

#1 Not enabling binary logging on the master server.

Wrong approach:

On master: skip setting 'log_bin=ON' in my.cnf and restart server.

Correct approach:

On master: add 'log_bin=ON' in my.cnf and restart server to enable binary logging.

Root cause:

Replication depends on the binary log; without it, slaves have no data to copy.

#2 Starting slave replication without specifying the correct log file and position.

Wrong approach:

On slave: RUN 'CHANGE MASTER TO MASTER_HOST='master', MASTER_USER='replica', MASTER_PASSWORD='pass'; START SLAVE;' without log file and position.

Correct approach:

On slave: RUN 'CHANGE MASTER TO MASTER_HOST='master', MASTER_USER='replica', MASTER_PASSWORD='pass', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154; START SLAVE;' with correct log file and position.

Root cause:

Slave needs to know where to start reading the master's binary log to avoid data inconsistency.

#3 Allowing writes on the slave server.

Wrong approach:

On slave: leave 'read_only=OFF' and run INSERT or UPDATE queries directly.

Correct approach:

On slave: set 'read_only=ON' in my.cnf to prevent accidental writes.

Root cause:

Writes on slaves cause conflicts and break replication consistency.

Key Takeaways

MySQL replication copies data changes from a master server to one or more slave servers automatically using binary and relay logs.

Replication roles are clearly defined: the master writes data and logs changes, while slaves read and apply those changes to stay synchronized.

Replication is asynchronous by default, so slaves may lag behind the master, which affects data freshness for read queries.

Proper setup requires enabling binary logging on the master, configuring slaves with correct log positions, and usually setting slaves to read-only mode.

Advanced replication modes and failover strategies improve data safety and availability but require careful planning and monitoring.