Learning Focus

Use this lesson to understand Replication basics with practical syntax and examples.

Backup and Recovery: Replication Basics

Concept Overview

Definition:

Replication in MySQL is the process of copying data from one database server (the source, formerly known as master) to one or more other database servers (the replicas, formerly known as slaves). This enables backup, load balancing, high availability, and disaster recovery.

Why It's Important:

Replication is a critical part of a robust backup and recovery strategy. It allows:

Real-time data redundancy
Minimized downtime during failures
Parallel reads across replicas for performance
Easier system upgrades and migrations

Where It Fits in the Broader Context:

In MySQL's backup and recovery ecosystem, replication supports:

Live standby systems (disaster recovery)
Non-blocking backups (take backups from replicas)
Distributed reads (scalability)

It works alongside tools like mysqldump, binary logging, and point-in-time recovery.

Basic Syntax & Rules

Basic Syntax:

Enable binary logging on the source server:

# my.cnf on source
server-id=1
log-bin=mysql-bin

Configure the replica:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='source_host',
  SOURCE_USER='replica_user',
  SOURCE_PASSWORD='password',
  SOURCE_LOG_FILE='mysql-bin.000001',
  SOURCE_LOG_POS=120;

Start replication:

START REPLICA;

Check status:

SHOW REPLICA STATUS\G

Key Parameters:

SOURCE_HOST: IP/hostname of the source server
SOURCE_USER: User with REPLICATION SLAVE privilege
SOURCE_LOG_FILE and SOURCE_LOG_POS: Indicate where replication should start
server-id: Unique for each server
log-bin: Enables binary logging (required on source)

Limitations & Considerations:

Replication is asynchronous by default
Requires binary logging enabled on source
Network issues can cause lag between source and replica
DDL statements (e.g., ALTER TABLE) must be carefully managed

Step-by-Step Examples

Example 1: Simple One-Way Replication

Scenario: You want to replicate the employees database.

Step 1: On Source Server

# my.cnf
server-id=1
log-bin=mysql-bin

Step 2: Create replication user

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

Step 3: Check binary log position

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Output:

File: mysql-bin.000001
Position: 245

Step 4: On Replica Server

# my.cnf
server-id=2

Step 5: Configure replication

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='192.168.1.100',
  SOURCE_USER='replica_user',
  SOURCE_PASSWORD='password',
  SOURCE_LOG_FILE='mysql-bin.000001',
  SOURCE_LOG_POS=245;

START REPLICA;

Step 6: Check status

SHOW REPLICA STATUS\G

Example 2: Replicating With a Filter (Only Specific Tables)

-- In replica's config
replicate-do-db=employees
replicate-do-table=employees.departments

This ensures only the departments table from employees database is replicated.

Practical Use Cases

Disaster Recovery

If your source server fails, promote a replica to become the new source.
Load Balancing Reads

Direct heavy SELECT traffic to replicas to reduce load on the source.
Non-Disruptive Backups

Use replicas to take backups without locking production databases.
Testing Production Data

Use a replica as a safe environment to run queries or tests on near-live data.
Geographic Redundancy

Deploy replicas in different data centers for regional high availability.

Common Mistakes & Troubleshooting

Mistake	Cause	Solution
Replication stops silently	Network issue or crashed SQL thread	Check `SHOW REPLICA STATUS\G` and restart threads
Wrong log file/position	Incorrect snapshot during setup	Double-check `SHOW MASTER STATUS` output
Duplicate server-id	Both servers have same ID	Ensure unique `server-id` in config
Replication lag	Heavy writes or slow replicas	Monitor `Seconds_Behind_Source`, optimize schema/indexes

Tips:

Use pt-heartbeat from Percona Toolkit to monitor lag
Always LOCK TABLES and get consistent snapshots when starting replication

Best Practices

Use GTID (Global Transaction Identifiers) for simpler and safer replication setup:
```
gtid_mode=ON
enforce-gtid-consistency=ON
```
Monitor replication health via tools like:
- MySQL Enterprise Monitor
- SHOW REPLICA STATUS
- Performance Schema
Secure replication connections with SSL
Avoid DDL on high-traffic systems or use pt-online-schema-change
Don't chain too many replicas (prefer fan-out structure)

Hands-On Practice

Exercise 1: Basic Replication Setup

Task: Set up one replica for a sample employees database

Expected Output: Replica replicates new inserts in real time

Exercise 2: Introduce Replica Lag

Task: Insert millions of rows on source, observe Seconds_Behind_Source

Goal: Understand how lag occurs and how to monitor it

Exercise 3: Apply Replication Filter

Task: Set up a replica that only replicates the customers table

Expected Outcome: Only that table is present and synced on replica

Sample Data Setup:

CREATE DATABASE shop;
USE shop;

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob');

Connection to Other Concepts

Related Concept	Description
Binary Logs	Core mechanism behind replication
GTID	Simplifies replication and failover
Backups	Replicas can be backup sources
Failover	Replicas can be promoted when source fails
Clustering	Replication is a stepping stone to high availability setups

Prerequisites:

Understanding of binary logs
Basic MySQL admin (install, configure)

What to Learn Next:

GTID replication
Multi-source replication
MySQL Group Replication
Tools like MySQL Shell, MySQL Router

Visual Learning Diagram

graph TD
    A[MySQL Basics] --> B[Binary Logging]
    B --> C[Backup & Recovery]
    C --> D[Replication Basics]
    D --> E[GTID Replication]
    E --> F[Group Replication]
    F --> G[High Availability]
    D --> H[Load Balancing]
    D --> I[Disaster Recovery]
    D --> J[Failover Automation]

    %% Styling
    classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
    classDef currentTopic stroke-width:4px

    class A,B,C,E,F,G,H,I,J allNodes
    class D allNodes,currentTopic

    class A,B,C,E,F,G,H,I,J,D allNodes
    class A,B,C,E,F,G,H,I,J rect
    class D rect

Common Pitfalls

Pitfall	Consequence	Prevention
Running destructive commands without context	Databases or tables are dropped unexpectedly	Confirm target with `SELECT DATABASE();` and run in a controlled environment first
Skipping backups before schema/data changes	Recovery window becomes long or impossible	Create a backup snapshot and test restore before production changes
Not validating privileges and locks	Operations fail midway or block traffic	Check grants, active sessions, and maintenance window constraints

Quick Reference

SHOW REPLICA STATUS\G
CHANGE REPLICATION SOURCE TO SOURCE_HOST="db-primary", SOURCE_USER="repl";
START REPLICA;

What's Next

Previous: point-in-time recovery - Review the previous lesson to reinforce context.
Module Overview - Return to this module index and choose another related lesson.

Concept Overview​

Basic Syntax & Rules​

Basic Syntax:​

Key Parameters:​

Limitations & Considerations:​

Step-by-Step Examples​

Example 1: Simple One-Way Replication​

Example 2: Replicating With a Filter (Only Specific Tables)​

Practical Use Cases​

Common Mistakes & Troubleshooting​

Best Practices​

Hands-On Practice​

Exercise 1: Basic Replication Setup​

Exercise 2: Introduce Replica Lag​

Exercise 3: Apply Replication Filter​

Connection to Other Concepts​

Visual Learning Diagram​

Common Pitfalls​

Quick Reference​

What's Next​