MySQL Replication: A Practical Guide

Q: Can I replicate only certain databases?

Yes. Use binlog_do_db and binlog_ignore_db on the primary to control which databases are replicated. Or use replicate_do_db on the replica.

MySQL replication copies data from a primary server to one or more replica servers. The primary handles writes, replicas handle reads. This scales read performance and provides failover capability. The most common setup is asynchronous replication, where the primary does not wait for replicas to confirm writes.

Replication is the foundation of MySQL high availability. Most production deployments use replication in some form. It scales reads, enables backups without downtime, and provides failover capability.

How Replication Works

MySQL replication uses three threads:

Binary log dump thread - Runs on the primary, sends binary log events to replicas
I/O thread - Runs on the replica, receives binary log events and writes them to the relay log
SQL thread - Runs on the replica, reads the relay log and applies the changes

-- Primary server: Binary log is enabled
SHOW VARIABLES LIKE 'log_bin';
-- log_bin: ON

-- Replica server: Shows replication status
SHOW REPLICA STATUS\G
-- Replica_IO_Running: Yes
-- Replica_SQL_Running: Yes
-- Seconds_Behind_Source: 0

Setting Up Primary-Replica Replication

Step 1: Configure the Primary

# my.cnf on primary
[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800  # 7 days
max_binlog_size = 100M

Step 2: Create a Replication User

-- On the primary
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;

Step 3: Get Primary Status

-- On the primary
SHOW BINARY LOG STATUS;
-- File: mysql-bin.000003
-- Position: 12345

Step 4: Configure the Replica

# my.cnf on replica
[mysqld]
server-id = 2
relay_log = relay-bin
read_only = ON
super_read_only = ON

Step 5: Start Replication

-- On the replica
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = 'primary_host',
  SOURCE_USER = 'repl_user',
  SOURCE_PASSWORD = 'repl_password',
  SOURCE_LOG_FILE = 'mysql-bin.000003',
  SOURCE_LOG_POS = 12345;

START REPLICA;

Monitoring Replication

Monitor these metrics to catch problems early:

-- Check replication status
SHOW REPLICA STATUS\G

-- Key fields to watch:
-- Replica_IO_Running: Yes (should always be Yes)
-- Replica_SQL_Running: Yes (should always be Yes)
-- Seconds_Behind_Source: 0 (should be close to 0)
-- Last_IO_Error: (should be empty)
-- Last_SQL_Error: (should be empty)

If Seconds_Behind_Source is increasing, the replica cannot keep up with the primary. This usually means the replica is underpowered or the primary is doing too many writes.

A common mistake is setting up replication and never checking it again. You should monitor replication lag daily, not just during incidents. Configure alerts in your monitoring system (Prometheus, Datadog, or whatever you use) to fire when lag exceeds 30 seconds. Many outages could have been prevented if someone had been watching this single metric.

Replication lag is not always consistent. It can spike during heavy write periods on the primary, then catch up during quiet periods. Understand your normal baseline. If lag is usually under 1 second and suddenly jumps to 10 seconds during peak hours, that is something to investigate before it becomes a real problem.

Common Replication Problems

Replication Lag

The replica falls behind the primary. This is the most common problem.

Replication lag is not just a numbers game. A lag of 5 seconds might be fine for a reporting workload, but unacceptable for a real-time dashboard. The acceptable threshold depends on your application's tolerance for stale reads. Some teams tolerate minutes of lag for analytics queries, while sub-second lag is required for user-facing applications where data freshness matters.

-- Causes of replication lag:
-- 1. Replica hardware is slower than primary
-- 2. Primary has heavy write load
-- 3. Long-running transactions on the replica
-- 4. Replication lag (replica_parallel_workers defaults to 4 in MySQL 8.4; parallel replication is on by default. Setting it to 0 disables it and is deprecated)

-- Verify parallel replication settings
SET GLOBAL replica_parallel_workers = 4;
SET GLOBAL replica_parallel_type = 'LOGICAL_CLOCK';

Replication Errors

Errors stop replication. You need to fix them before replication can continue.

-- Check for errors
SHOW REPLICA STATUS\G
-- Look for Last_IO_Error and Last_SQL_Error

-- Skip an error using GTID (MySQL 8.4)
-- First, find the GTID of the problematic transaction
SHOW REPLICA STATUS\G
-- Look for Executed_Gtid_Set

-- Inject an empty transaction to skip the GTID
STOP REPLICA;
SET GTID_NEXT='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:123';
BEGIN; COMMIT;
SET GTID_NEXT='AUTOMATIC';
START REPLICA;

-- Or skip to a specific position
STOP REPLICA;
CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='mysql-bin.000003', SOURCE_LOG_POS=56789;
START REPLICA;

Data Drift

The replica has different data than the primary. This happens when someone writes directly to the replica or when replication errors cause partial apply.

Data drift is insidious because it can go unnoticed for weeks or months. You might not discover it until a failover puts the wrong data in front of users. Regular checksum comparisons between primary and replicas are the only way to catch this early. Schedule pt-table-checksum to run weekly, or set up automated comparison queries that run daily on critical tables.

-- Check for data drift
-- Compare row counts
SELECT 'primary' as source, COUNT(*) FROM users
UNION ALL
SELECT 'replica' as source, COUNT(*) FROM users;

-- Use pt-table-checksum for thorough comparison
pt-table-checksum --replicate=percona.checksums h=primary_host

Read Scaling with Replicas

The most common use of replicas is read scaling. Send read queries to replicas, write queries to the primary.

When splitting reads and writes, you need to think about connection management in your application code. Some ORMs and connection libraries have built-in support for read/write splitting, while others require manual routing. The key is to keep the logic simple and in one place so you can change it later if your architecture evolves. A common pattern is to use a connection pool wrapper that routes based on the query type.

-- Application code
const readDb = mysql.createConnection({
  host: 'replica_host',
  database: 'mydb'
});

const writeDb = mysql.createConnection({
  host: 'primary_host',
  database: 'mydb'
});

// Reads go to replica
const users = await readDb.query('SELECT * FROM users WHERE id = ?', [userId]);

// Writes go to primary
await writeDb.query('UPDATE users SET name = ? WHERE id = ?', [name, userId]);

Failover

When the primary fails, promote a replica to primary. This is the high availability aspect of replication.

The hardest part of failover is not the technical steps. It is deciding when to failover. If you failover too early during a temporary blip, you might cause more disruption than the original problem. If you wait too long, you lose data or availability. Most teams set a threshold, for example: if the primary is unreachable for 60 seconds, start the failover process. Having a clear decision criteria before the incident saves valuable time.

-- Manual failover steps:
-- 1. Stop writes to primary
-- 2. Wait for replica to catch up
-- 3. Stop replication on replica
-- 4. Promote replica to primary
STOP REPLICA;
RESET REPLICA ALL;
SET GLOBAL read_only = OFF;

-- 5. Update application to point to new primary
-- 6. Set up old primary as new replica

For automated failover, use tools like Orchestrator, MHA (Master High Availability), or ProxySQL.

GTID Replication

Global Transaction Identifiers (GTIDs) make replication easier to manage. Each transaction gets a unique ID across all servers.

# my.cnf: Enable GTID
[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON

-- Simplified replication setup
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = 'primary_host',
  SOURCE_USER = 'repl_user',
  SOURCE_AUTO_POSITION = 1;

-- Easier failover with GTID
-- No need to track log file and position

Best Practices

Use GTID replication - Easier management and failover
Monitor replication lag - Set up alerts for lag > 30 seconds
Use ROW-based replication - More reliable than statement-based
Enable parallel replication - Reduces lag on replicas
Test failover regularly - Do not wait for a real failure to discover problems
Backup from replicas - Reduce load on the primary
Use read_only on replicas - Prevent accidental writes

Common Mistakes

Using statement-based replication - Statement-based replication can cause inconsistencies with non-deterministic functions. Always use ROW-based replication for reliability
Not monitoring replication lag - Unchecked lag can grow to hours before you notice. Set up alerts for lag exceeding 30 seconds
Writing directly to replicas - This causes data drift. Always set read_only and super_read_only on replicas to prevent accidental writes

Real-World Example: Read Scaling for a Content Site

A news website serves 100,000 page views per minute. Most requests are reads (fetching articles, comments, user profiles). Writes happen only when users post comments or editors publish articles. Without replicas, a single MySQL instance handles all traffic.

With replication, the architecture changes. The primary handles all writes (new articles, comments, user registrations). Two replicas handle read traffic (article pages, search results, user profiles). Load balancers distribute read requests across replicas. If one replica falls behind, the load balancer routes traffic to the other.

-- Application routing logic (pseudocode)
-- Write queries go to primary
-- Read queries are distributed across replicas

-- Primary handles: INSERT, UPDATE, DELETE
-- Replicas handle: SELECT (read-only)

-- Round-robin load balancing across replicas
-- If a replica falls behind, route traffic to the other

This setup significantly reduces primary load since most traffic is read-heavy. The primary only handles the small fraction of write requests, giving it headroom to handle traffic spikes during breaking news events.

Key Takeaways

MySQL replication uses three threads: binary log dump, I/O thread, and SQL thread
GTID replication simplifies management and failover compared to traditional log-based replication
Monitor Seconds_Behind_Source to detect replication lag before it impacts your application
Use parallel replication (replica_parallel_workers) to reduce lag on high-write-load primaries
Test failover procedures regularly; do not wait for a real failure to discover configuration issues

FAQ

How many replicas can I have?

Technically, unlimited. Practically, network bandwidth and the primary's ability to serve binlog dumps become bottlenecks. For most deployments, 3-5 replicas is optimal. For more read capacity, use ProxySQL to manage connection pooling.

Can I replicate only certain databases?

Yes. Use binlog_do_db and binlog_ignore_db on the primary to control which databases are replicated. Or use replicate_do_db on the replica.

What happens if the replica crashes?

Nothing happens to the primary. When the replica restarts, it picks up from where it left off using the relay log. The replication I/O thread and SQL thread automatically resume.

How do I resync a replica that has drifted?

The safest approach is to take a fresh backup from the primary, restore it on the replica, and reconfigure replication from the backup's GTID position. Do not try to manually fix data differences.

Can I use replication for version upgrades?

Yes. You can upgrade replicas first, test your application against them, then promote a replica as the new primary. This allows zero-downtime upgrades. During the upgrade process, replicas should be the same version or newer than the primary.