MySQL Master-Slave Replication Setup

By Kokil Thapa | Last reviewed: August 2026

When your Laravel or PHP application starts hitting read bottlenecks, a properly configured MySQL Master-Slave Replication Setup is often the most cost-effective scaling strategy before sharding or migrating to managed cloud databases. I have implemented this architecture for high-traffic legal-tech portals and eCommerce platforms where write operations must remain consistent while read queries scale horizontally across replicas. This guide covers the exact configuration steps, security hardening, and monitoring practices required for a reliable database-driven website development in Nepal and global production environments.

How do you configure the MySQL Master server for replication?

The master (primary) server must be configured to generate binary logs that capture every data-modifying statement. In MySQL 8.0 and 8.4 LTS, GTID-based replication is the standard approach because it simplifies failover and eliminates fragile file-position tracking. Edit your /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/my.cnf.d/server.cnf file on the master node with these essential parameters:

[mysqld]
server-id               = 1
log_bin                 = /var/log/mysql/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

Each directive serves a specific purpose in production. The server-id must be a unique positive integer across your entire replication topology; I typically use sequential integers starting from 1 for the master. Setting binlog_format = ROW ensures deterministic replication by logging actual row changes rather than SQL statements, which prevents issues when functions like NOW() or triggers produce different results on replicas. The sync_binlog = 1 and innodb_flush_log_at_trx_commit = 1 combination guarantees zero data loss on crash at the cost of slightly higher I/O — never relax these in production unless you accept potential data inconsistency.

Creating the replication user securely

Create a dedicated MySQL user solely for replication traffic. Never reuse application credentials or root accounts. On MySQL 8.0+, execute:

CREATE USER 'repl_user'@'%' IDENTIFIED WITH caching_sha2_password BY 'StrongP@ssw0rd!2026';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;

In practice, restrict the host wildcard to your replica's private IP or subnet (e.g., 'repl_user'@'10.0.1.%') rather than allowing connections from any host. For cross-datacenter replication over public networks, wrap the connection in an SSH tunnel or use MySQL's built-in SSL/TLS by adding REQUIRE SSL to the GRANT statement and configuring certificates on both nodes. I have seen too many setups where open replication users became attack vectors during security audits on server hardening projects.

How do you initialize a MySQL replica and start replication?

On each replica server, configure a unique server-id and enable relay logging in mysqld.cnf:

[mysqld]
server-id               = 2
relay_log               = /var/log/mysql/relay-bin
read_only               = ON
super_read_only         = ON
gtid_mode               = ON
enforce_gtid_consistency = ON
replica_parallel_workers = 4

Setting super_read_only = ON prevents even SUPER-privileged users from accidentally writing to the replica — a safeguard I always enable after being burned by a well-meaning DBA who ran a manual UPDATE on what they thought was the master. The replica_parallel_workers = 4 enables multi-threaded applier threads, significantly reducing lag on write-heavy workloads. Adjust this value based on CPU cores; 4–8 workers suit most mid-range servers.

Pointing the replica to the master

After restarting MySQL with the new configuration, connect to the replica and run:

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = '10.0.1.10',
    SOURCE_USER = 'repl_user',
    SOURCE_PASSWORD = 'StrongP@ssw0rd!2026',
    SOURCE_AUTO_POSITION = 1,
    SOURCE_SSL = 1;

START REPLICA;

The SOURCE_AUTO_POSITION = 1 parameter tells MySQL to use GTID auto-positioning instead of manual binlog file and position coordinates. This is non-negotiable for new deployments in 2026. If you are migrating from legacy file-based replication, complete the current transaction set first, then switch to GTID mode following the official MySQL upgrade path. Verify replication health immediately:

SHOW REPLICA STATUS\G

Confirm that Replica_IO_Running and Replica_SQL_Running both show Yes, and Seconds_Behind_Source is 0 or a low stable number. Any other state requires investigation before serving traffic.

What are common MySQL replication failures and how do you fix them?

Replication breaks in predictable ways. After maintaining replicated topologies for Laravel applications and WooCommerce stores since 2010, these are the failures I encounter most frequently:

• Non-deterministic statements: Queries using UUID(), RAND(), or user-defined variables without ROW format cause silent divergence. Always use binlog_format = ROW and audit slow query logs for unsafe patterns.
• Schema drift: Running ALTER TABLE directly on a replica breaks replication. All DDL must originate on the master. Use tools like pt-online-schema-change or gh-ost for zero-downtime schema migrations on large tables.
• Disk space exhaustion: Binary logs accumulate quickly under heavy write load. Set binlog_expire_logs_seconds = 604800 (7 days) and monitor disk usage with automated alerts. I have recovered multiple production systems where full disks halted replication silently.
• Network interruptions: Transient network failures between datacenters cause IO thread disconnections. Configure replica_net_timeout = 60 and replica_reconnect_interval = 10 for automatic reconnection. For unstable links, consider compressed replication with COMPRESSION_ALGORITHMS=zstd in MySQL 8.0.34+.
• GTID inconsistencies: Manual writes to replicas inject GTIDs that don't exist on the master, causing permanent breakage. Enforce super_read_only and audit access controls quarterly.

How should Laravel applications use MySQL read replicas?

Laravel natively supports read/write splitting through database configuration. In config/database.php, define separate connections and let the framework route queries automatically:

'mysql' => [
    'read' => [
        'host' => env('DB_READ_HOST', '10.0.1.20'),
    ],
    'write' => [
        'host' => env('DB_WRITE_HOST', '10.0.1.10'),
    ],
    'sticky' => true,
    // ... shared credentials, database name, charset
],

The sticky option is critical. When enabled, Laravel routes all reads to the master for the remainder of a request cycle after any write occurs. Without it, a user might create a record and immediately fail to see it because the replica hasn't caught up yet. I enable sticky by default on every Laravel project using replication, disabling it only for specific read-heavy endpoints where eventual consistency is acceptable (analytics dashboards, search results, cached content).

Handling replica lag in application code

Even with sticky connections, background jobs, API responses consumed by SPAs, and webhook callbacks can hit stale replicas. Implement explicit master reads when freshness is mandatory:

$order = DB::connection('mysql')
    ->table('orders')
    ->where('id', $orderId)
    ->useWriteConnection()
    ->first();

For queue workers processing payment confirmations or inventory updates, always configure the worker to use the write connection exclusively. Stale reads in financial workflows cause duplicate charges and overselling — problems I have debugged on live eCommerce systems handling NPR-denominated transactions where timing matters during peak Dashain sales periods.

How do you monitor MySQL replication health in production?

Replication monitoring cannot be an afterthought. Silent failures are the norm, not the exception. Implement these layers:

1. Heartbeat table: Create a small table on the master that updates every second via a scheduled event. Query this table on replicas to measure actual end-to-end lag more accurately than Seconds_Behind_Source, which can report 0 even when the replica is stuck replaying old events.
2. Alerting thresholds: Alert at 30 seconds lag for warning, 120 seconds for critical. For legal-tech portals handling court date notifications or document attestation deadlines, I set tighter thresholds (10s warning, 30s critical) because stale data has real-world consequences for clients.
3. GTID set comparison: Periodically compare Executed_Gtid_Set between master and replicas. Divergence indicates skipped transactions or manual interference that Seconds_Behind_Source won't catch.
4. Automated failover readiness: Test promotion procedures quarterly. Document the exact commands, DNS changes, and application config updates required. Unpracticed failovers fail during actual outages.

When should you choose replication over other scaling strategies?

MySQL Master-Slave Replication Setup solves read-scaling and disaster recovery, but it introduces operational complexity. Choose replication when your workload is read-heavy (80%+ reads), when you need geographic distribution for latency reduction, or when budget constraints rule out managed multi-AZ databases. Avoid replication as a sole strategy when your bottleneck is write throughput, when your team lacks database administration experience, or when strong consistency across all reads is non-negotiable.

For Nepal-based businesses running on limited infrastructure budgets, replication on two modest VPS instances (Rs 8,000–15,000/month each, ~USD 60–110) often delivers better ROI than jumping straight to AWS RDS Multi-AZ at USD 300+/month. However, factor in the engineering time for setup, monitoring, and incident response. If your team is small and database ops aren't a core competency, managed solutions may be cheaper overall despite higher monthly costs. I discuss these trade-offs in detail when consulting on website development cost planning for growing businesses.

Implementing MySQL Master-Slave Replication Setup reliably

A production-grade MySQL Master-Slave Replication Setup demands disciplined configuration, continuous monitoring, and application-aware query routing. Start with GTID mode, enforce read-only replicas, implement heartbeat-based lag detection, and configure Laravel's sticky connections to prevent consistency bugs. Test failover procedures before you need them. If you are planning a replication deployment for a Laravel application, eCommerce platform, or legal-tech portal and want to avoid the pitfalls that only surface under real production load, reach out to discuss your architecture. I help teams design, implement, and maintain replicated MySQL topologies that actually stay healthy at 3 AM.