Replication — copying data from a primary to replica databases (read scaling)
Concept
Replication — copying data from a primary (write) database to one or more replica (read) databases in near-real-time. Used for read scaling, high availability, and backup.
Primary (Master): Receives all writes (INSERT, UPDATE, DELETE). The source of truth. Replica (Slave/Read Replica): Receives a copy of all writes from the primary. Serves read queries. Typically slightly behind the primary (replication lag).
How replication works (MySQL):
- Primary writes to the binary log (binlog) — every change recorded.
- Replica's I/O thread connects to primary and pulls the binlog events.
- Replica's SQL thread replays those events on its own data.
- Lag = how far behind the replica is from the primary.
Read/write splitting: Application sends writes to primary, reads to replicas. Balances load. Most web app queries are reads (90%+), so replicas take most traffic.
Replication lag: Replicas are slightly behind (milliseconds to seconds). Causes stale reads: you write a record, then immediately read it from a replica — it might not be there yet. Solution: read from primary immediately after writes.
Laravel config for read/write splitting:
'read' => ['host' => ['replica1', 'replica2']],
'write' => ['host' => 'primary'],
'sticky' => true, // reads from primary during same request if a write occurredUse cases:
- Reporting queries: Heavy reads on replica so they don't slow down production.
- Backup: Replica can be snapshotted without locking the primary.
- Geographic distribution: Replica in another region serves local reads faster.
- High availability (HA): Failover to replica if primary fails.
Code Example
<?php
// LARAVEL READ/WRITE SPLITTING — config/database.php
return [
'connections' => [
'mysql' => [
'driver' => 'mysql',
// Read from replicas (load balanced)
'read' => [
'host' => [
env('DB_HOST_REPLICA_1', 'replica1.internal'),
env('DB_HOST_REPLICA_2', 'replica2.internal'),
],
],
// Write only to primary
'write' => [
'host' => env('DB_HOST_PRIMARY', 'primary.internal'),
],
'sticky' => true, // reads from primary for the rest of this request after any write
// 'sticky' prevents reading stale data immediately after writing
'database' => env('DB_DATABASE'),
'username' => env('DB_USERNAME'),
'password' => env('DB_PASSWORD'),
],
],
];
// Usage — Laravel routes reads/writes automatically
User::create(['name' => 'Alice', 'email' => 'alice@example.com']); // → PRIMARY
User::find(1); // → REPLICA (or PRIMARY if sticky)
\DB::statement('INSERT INTO logs ...'); // → PRIMARY (write)
\DB::select('SELECT * FROM products ...'); // → REPLICA
// Forcing a read from primary (bypass replica lag)
$user = User::onWriteConnection()->find($id); // reads from PRIMARY
// Use when you just wrote and need to read the fresh data
// Checking which connection is being used
\DB::getReadPdo(); // the replica PDO instance
\DB::getPdo(); // the write (primary) PDO instance
// REPLICATION LAG detection (MySQL)
$lag = \DB::selectOne("SHOW SLAVE STATUS"); // Seconds_Behind_Master field
// If lag > 5 seconds, redirect critical reads to primary:
if ($lag && $lag->Seconds_Behind_Master > 5) {
$user = User::onWriteConnection()->find($id);
} else {
$user = User::find($id); // from replica is fine
}
// GLOBAL ID trick: after writing, check if replica has caught up
function waitForReplica(string $transactionId): bool
{
// MySQL: SELECT WAIT_FOR_EXECUTED_GTID_SET('transaction_id', 5)
// Returns 0 if replica caught up within 5 seconds
$result = \DB::selectOne(
"SELECT WAIT_FOR_EXECUTED_GTID_SET(?, 5) as waited",
[$transactionId]
);
return $result->waited === 0; // 0 = success, 1 = timeout
}