MySQL Replication: Separating Read and Write Workloads
Learn the primary-replica model and its implications for read consistency.
You are reading a translated version.
Why use replication?
When reads greatly outnumber writes, a single database server can become overloaded. Replication copies data from a primary server to one or more replicas that can handle read queries.
How MySQL replication works
The primary records data changes in its binary log. A replica reads that log and applies the changes to its own data.
# Di server primary, aktifkan binary logging (my.cnf)
[mysqld]
server-id = 1
log_bin = mysql-bin
# Di server replica
[mysqld]
server-id = 2
Route queries to the right server
Applications commonly send writes (INSERT, UPDATE, DELETE) to the primary and route reads (SELECT) to a replica.
// Contoh koneksi Laravel dengan konfigurasi read/write terpisah
'mysql' => [
'read' => ['host' => ['replica1.internal']],
'write' => ['host' => ['primary.internal']],
'sticky' => true,
],
The sticky option makes a request that has just written data read from the primary for the remainder of that request, reducing the appearance that saved data has disappeared.
Replication lag
With asynchronous replication, changes reach the replica after a delay. Reading a recent write from a replica that has not caught up can temporarily return older data.
Exercise
Investigate Seconds_Behind_Source (or Seconds_Behind_Master on older versions) in SHOW REPLICA STATUS. Discuss how replication delay affects read-routing decisions.