MySQL Replication

40 min

MySQL replication enables you to maintain multiple copies of your database for high availability, disaster recovery, and read scaling. Replication works by copying data changes from a master server to one or more slave servers. Replication ensures that if the master fails, a slave can take over, providing automatic failover and high availability. Understanding replication is essential for production MySQL deployments requiring reliability and scalability.

Master-slave replication (also called source-replica in MySQL 8.0+) is the most common replication setup. The master server receives all write operations and records changes in binary logs. Slave servers read from the master's binary logs and apply changes to their own data sets. This ensures all servers maintain identical data. Understanding master-slave replication enables basic replication setups.

Replication uses binary logs to record all data changes on the master. Binary logs contain all INSERT, UPDATE, DELETE, and DDL statements. Slaves read binary log events and apply them in the same order, ensuring data consistency. Binary log format (STATEMENT, ROW, or MIXED) affects replication behavior and performance. Understanding binary logs enables effective replication management.

Replication can be used for read scaling by distributing read queries across multiple slave servers. This improves performance for read-heavy workloads. However, slaves may have slight replication lag, so they may return slightly stale data. Applications must account for this when reading from slaves. Understanding read scaling enables performance optimization.

Replication provides disaster recovery by maintaining copies of data on separate servers. If the master fails, a slave can be promoted to master, enabling quick recovery. Replication also enables geographic distribution, placing slaves in different locations for disaster recovery. Understanding replication for disaster recovery enables robust data protection.

Additional replication features include multi-source replication (slave replicating from multiple masters), group replication (MySQL Group Replication for high availability), and semi-synchronous replication (ensuring data is replicated before commit). These advanced features enable sophisticated replication topologies. Understanding these features enables complex replication architectures.

Key Concepts

Replication creates copies of databases for availability and scaling.
Master-slave replication copies data from master to slaves.
Binary logs record all data changes for replication.
Replication enables read scaling and disaster recovery.
Replication lag may cause slightly stale data on slaves.

Learning Objectives

Master

Setting up master-slave replication
Configuring binary logging
Managing replication processes
Using replication for read scaling

Develop

Understanding high availability architectures
Designing reliable MySQL deployments
Implementing read scaling strategies

Tips

Enable binary logging: log-bin = mysql-bin in my.cnf on master.
Create replication user: CREATE USER 'repl'@'%' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
Configure slave: CHANGE MASTER TO MASTER_HOST='...', MASTER_USER='repl', ...;
Check replication status: SHOW SLAVE STATUS\G to see replication state.

Common Pitfalls

Not monitoring replication lag, serving stale data from slaves.
Not configuring binary logging, unable to set up replication.
Not testing failover procedures, unable to recover during actual failures.
Not understanding replication lag, causing consistency issues.

Summary

Replication provides high availability and read scaling.
Master-slave replication copies data from master to slaves.
Binary logs enable data replication.
Understanding replication enables reliable, scalable MySQL deployments.

Exercise

Set up basic MySQL replication between master and slave servers.

-- On Master Server (my.cnf)
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW

-- On Master, create replication user
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';

-- Get master status
SHOW MASTER STATUS;

-- On Slave Server (my.cnf)
[mysqld]
server-id = 2
relay-log = mysql-relay-bin
log-bin = mysql-bin
binlog_format = ROW

-- On Slave, configure replication
CHANGE MASTER TO
    MASTER_HOST = 'master_ip',
    MASTER_USER = 'repl_user',
    MASTER_PASSWORD = 'repl_password',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS = 154;

-- Start replication
START SLAVE;

-- Check replication status
SHOW SLAVE STATUSG

-- Stop replication
STOP SLAVE;

-- Reset replication
RESET SLAVE;

-- Show slave hosts on master
SHOW SLAVE HOSTS;

Exercise Tips

Use unique server-id: each server in replication must have unique server-id.
Monitor replication lag: check Seconds_Behind_Master in SHOW SLAVE STATUS.
Test failover: practice promoting slave to master for disaster recovery.
Use ROW format for binary logs: binlog_format = ROW for better replication.

MySQL Replication