MySQL Master-Slave Replication Setup

By Khimananda Oli | Last reviewed: August 2026

A properly executed MySQL Master-Slave Replication Setup provides read scalability and disaster recovery, but misconfigured instances frequently cause silent data drift or catastrophic sync failures in production. Many teams still rely on legacy position-based replication despite GTID being the standard for over a decade, leading to fragile topologies that break during failover. This guide covers the modern, audit-ready approach to configuring asynchronous replication with GTIDs, hardened security, and observable lag metrics.

What is MySQL Master-Slave Replication Setup and why use GTID?

The core mechanism of any MySQL Master-Slave Replication Setup is the binary log (binlog). The master records every data-modifying transaction to its binlog, and replicas connect via a dedicated I/O thread to stream these events into their relay logs. A separate SQL thread on the replica then replays those events to maintain an identical dataset. In 2026, using Global Transaction Identifiers (GTID) is non-negotiable for any serious deployment.

GTID assigns a unique identifier to every transaction across the entire replication topology. Unlike traditional file-and-position methods, GTID allows replicas to automatically determine which transactions they have already applied. This makes failover significantly safer because a promoted replica knows exactly where it stands relative to other nodes without manual binlog coordinate calculation. For teams managing infrastructure as code or automating deployments via tools discussed in my Terraform IaC guide, GTID is essential for idempotent provisioning scripts.

Key prerequisites before starting

• Identical MySQL versions (major and minor) across all nodes to avoid subtle behavioral differences.
• Unique server-id on every instance (1 for master, 2+ for replicas).
• Binlog format set to ROW for deterministic replication; statement-based replication is unsafe for most workloads.
• Network connectivity on port 3306 between master and replicas, restricted by firewall rules.
• Consistent time synchronization via NTP; clock skew breaks GTID ordering assumptions.

How do you configure the master server for replication?

The master configuration focuses on durability and GTID consistency. Edit your my.cnf or mysqld.cnf under the [mysqld] section. These settings require a service restart.

[mysqld]
server-id               = 1
log-bin                 = mysql-bin
binlog-format           = ROW
gtid-mode               = ON
enforce-gtid-consistency = ON
sync-binlog             = 1
innodb-flush-log-at-trx-commit = 1
binlog-expire-logs-seconds = 604800
max-binlog-size         = 1073741824

Setting sync-binlog=1 and innodb-flush-log-at-trx-commit=1 ensures every committed transaction is durably written to disk before acknowledgment. This is critical for zero-data-loss replication. On high-write masters, this impacts throughput; if you accept minimal risk for performance, sync-binlog=100 batches flushes but risks losing up to 100 transactions on crash. For compliance-heavy environments like fintech or healthcare systems common in Nepal's growing digital sector, always keep both values at 1.

Create a dedicated replication user

Never reuse application credentials for replication. Create a minimal-privilege account specifically for the replica I/O thread:

CREATE USER 'repl_user'@'10.0.%.%' IDENTIFIED BY 'Str0ng!R3pl#2026';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'10.0.%.%';
FLUSH PRIVILEGES;

Restrict the host pattern to your actual replica subnet. Avoid wildcards like '%' in production. Store this password in a secrets manager such as HashiCorp Vault or AWS Secrets Manager rather than plaintext config files; see my notes on secrets management patterns for implementation details.

How do you initialize and start replica synchronization?

Modern MySQL uses CHANGE REPLICATION SOURCE TO (the older CHANGE MASTER TO is deprecated). With GTID enabled, you skip manual binlog coordinates entirely.

1. On each replica, configure the source connection:

   CHANGE REPLICATION SOURCE TO
     SOURCE_HOST='master.db.internal',
     SOURCE_USER='repl_user',
     SOURCE_PASSWORD='Str0ng!R3pl#2026',
     SOURCE_AUTO_POSITION=1,
     GET_SOURCE_PUBLIC_KEY=1;

2. Start replication: START REPLICA;
3. Verify status: SHOW REPLICA STATUS\G

Check that both Replica_IO_Running and Replica_SQL_Running show Yes. If IO shows "Connecting," verify network, credentials, and SSL/TLS settings. If SQL shows "No," inspect Last_SQL_Error immediately. Common causes include duplicate key violations from pre-existing data or non-deterministic statements. Never blindly skip errors with SET GLOBAL sql_replica_skip_counter; this creates silent divergence that surfaces weeks later during audits or failovers.

Handling initial data seeding

For existing datasets, take a consistent snapshot from the master using mysqldump --single-transaction --set-gtid-purged=ON or Percona XtraBackup for large databases. Import this into the replica before starting replication. With GTID auto-positioning, the replica automatically skips already-applied transactions and resumes from the correct point. For greenfield setups with empty databases, simply start replication directly after configuration.

How do you monitor replication lag and prevent data drift?

Replication lag is the single most important metric for any MySQL Master-Slave Replication Setup. Relying solely on Seconds_Behind_Source is insufficient because it can report 0 even when the replica is stuck on a long-running transaction while newer events queue up. Implement layered monitoring:

Create a heartbeat table on the master that updates via a scheduled event every second. Query this table on replicas to measure true application-visible lag. Tools like Prometheus with mysqld_exporter automate this collection. Integrate alerts with your observability stack; if you're building monitoring pipelines, the patterns in my Prometheus setup guide apply directly here.

Preventing common failure modes

• Non-deterministic functions: Ban NOW(), UUID(), and RAND() in DML unless wrapped in row-based events. Use binlog-format=ROW strictly.
• Schema drift: Enforce schema changes through CI/CD pipelines with tools like gh-ost or pt-online-schema-change. Never ALTER TABLE directly on master during peak hours.
• Disk exhaustion: Monitor binlog growth. Set binlog-expire-logs-seconds appropriately and ensure adequate disk headroom. Binlogs can consume hundreds of GB daily on busy systems.
• Network partitions: Use semi-synchronous replication (rpl_semi_sync_source_wait_for_replica_count=1) if you need stronger durability guarantees, accepting write latency trade-offs.

When should you choose async vs semi-sync replication?

Understanding this trade-off prevents costly architectural mistakes. Asynchronous replication (default) offers maximum write throughput but risks data loss on master failure. Semi-synchronous waits for at least one replica ACK before committing, reducing loss windows to near-zero at the cost of added latency.

For most web applications serving Nepali or global audiences, asynchronous replication with proper monitoring suffices. Reserve semi-sync for payment processing, banking cores, or regulated datasets where RPO=0 matters more than P99 write latency. Test semi-sync impact thoroughly; cross-region replicas can add 50–200ms per commit.

Securing Your MySQL Master-Slave Replication Setup for Production

Security isn't optional—it's foundational. Encrypt replication traffic using TLS. Generate certificates signed by your internal CA and configure:

[mysqld]
ssl-ca                  = /etc/mysql/ssl/ca.pem
ssl-cert                = /etc/mysql/ssl/server-cert.pem
ssl-key                 = /etc/mysql/ssl/server-key.pem
require-secure-transport = ON

On replicas, add SOURCE_SSL=1 to the CHANGE REPLICATION SOURCE TO command. Restrict OS-level file permissions on certificate files to mysql:mysql 600. Audit replication user activity regularly; unexpected connections from unknown IPs indicate compromise. For compliance frameworks like ISO 27001 or SOC 2, document encryption-in-transit controls and retain binlogs for forensic analysis periods defined in your security policy.

Next Steps for Reliable Database High Availability

A correctly implemented MySQL Master-Slave Replication Setup gives you read scaling and a foundation for disaster recovery, but it's only one layer. Pair it with automated backups validated via regular restore tests, proactive lag alerting, and documented runbooks for failover procedures. Consider orchestrators like Orchestrator or Vitess for automated failover if manual promotion isn't acceptable for your SLOs.

If you're designing database infrastructure for production workloads and need architecture review or hands-on implementation support, reach out to discuss your specific requirements. Whether you're scaling a Laravel application, preparing for compliance audits, or optimizing costs on self-managed MySQL versus managed services, getting the replication foundation right prevents expensive rework downstream.