Essential MySQL Replication Best Practices to Keep Your Replicas Stable

1. Use GTID

GTID‑based replication eliminates many failure‑mode scenarios. Enable it in my.cnf:

gtid_mode=ON
enforce_gtid_consistency=ON
log_replica_updates=ON

Never mix GTID with legacy replication modes.

2. Switch to Row‑Based Replication (RBR)

Statement‑based replication is fragile; it fails with functions like NOW(), floating‑point differences, ordering mismatches, or trigger behavior. Enable RBR by setting: binlog_format=ROW RBR provides far higher predictability.

3. Ensure Every Table Has a Primary Key

Row‑based replication needs a way to locate changed rows. Without a primary key (or a unique index) the server must scan all columns, which is slow and error‑prone. Common symptoms of missing keys include replication lag, full‑table scans on updates, row‑apply failures, and errors such as Error 1032: Can't find record in table. Adding a primary key eliminates these issues.

4. Keep Schemas Consistent Across All Nodes

All replicas must run the same schema; otherwise silent drift can cause data divergence. Practical ways to verify schema consistency:

Method A – mysqldump

mysqldump --no-data mydb > schema.sql

Then compare schemas from source and replica:

diff source-schema.sql replica-schema.sql

Method B – Information Schema Metadata

SELECT table_name, column_name, column_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'mydb'
ORDER BY table_name, ordinal_position;

Run the query on each server and compare the results.

Method C – pt‑table‑checksum (data‑only)

Detect data drift without comparing schemas: pt-table-checksum --replicate=percona.checksums Fix drift with:

pt-table-sync --execute --replicate=percona.checksums

Combining schema checks and data checks yields safe replication.

5. Harden Binary Log Settings

The binary log is the foundation of replication. Recommended settings:

sync_binlog=1
binlog_row_image=FULL
binlog_expire_logs_seconds=604800  # 7 days

sync_binlog=1

is critical; without it a crash can corrupt the binlog or GTID position.

6. Use super_read_only to Protect Replicas

Add to my.cnf to prevent accidental writes:

read_only=ON
super_read_only=ON

super_read_only

blocks even SUPER users from writing to a replica.

7. Create a Dedicated Replication User with Minimal Privileges

CREATE USER 'repl'@'%' IDENTIFIED BY 'strong_password';
GRANT REPLICATION REPLICA ON *.* TO 'repl'@'%';

The user should be used only for replication, never for application workloads.

8. Monitor Replication Lag Closely

The Seconds_Behind_Source metric is unreliable on its own. Prefer more accurate sources such as:

Performance Schema replication status per worker

Percona Monitoring and Management (PMM)

Custom heartbeat tables using pt‑heartbeat Lag is a common cause of service disruption; early detection is essential.

9. Use Parallel Replication Wisely

When the primary has multiple writers, enable parallel workers in my.cnf:

replica_parallel_type=LOGICAL_CLOCK
replica_parallel_workers=4

Four to eight workers is optimal for most systems; adding more only increases memory usage without performance gains. Properly tuned, parallel replication can cut latency by 80‑90%.

10. Encrypt Replication Traffic Outside Trusted Networks

Never expose replication traffic. Enable SSL on the source side:

source_ssl=1
source_ssl_ca=/path/ca.pem

Older versions used master_ssl_* variables, but the principle remains the same: encrypt connections that leave trusted networks.

Final Checklist

Enable GTID

Use row‑based replication

Ensure every table has a primary key

Keep schemas identical across servers

Check for data drift regularly

Strengthen binlog configuration

Prevent accidental writes to replicas

Monitor replication lag continuously

Use parallel workers when appropriate

Encrypt replication traffic over untrusted networks

Following these steps will keep your replica databases healthy, stable, and effectively invisible to users.