How to Scale MySQL with Read Replicas When Your App Slows Down (2015)

Short answer: Enable binary logging on master, create a replication user, run CHANGE MASTER TO on replica, then route SELECT queries to replica and INSERT/UPDATE/DELETE to master in your application layer. Read replicas typically reduce master load by 60-80% for read-heavy apps.

---

When You Need This

Signs your single MySQL server is the bottleneck:

• SHOW PROCESSLIST shows dozens of SELECT queries waiting
• Threads_running is consistently above 20
• Writes (checkout, order creation) are timing out even though the SELECT traffic looks fine

Our inflection point: 50k daily active users, 200k page views/day. SELECT queries from product listings were blocking order inserts.

---

Step 1: Configure Master (my.cnf)

# /etc/mysql/my.cnf on master server
[mysqld]
server-id           = 1
log_bin             = /var/log/mysql/mysql-bin.log
binlog_format       = ROW           # More reliable than STATEMENT for complex queries
expire_logs_days    = 7             # Keep 7 days of binary logs
max_binlog_size     = 100M

# Ensure data integrity on master
innodb_flush_log_at_trx_commit = 1  # Flush to disk on every commit
sync_binlog                    = 1  # Sync binary log to disk on every commit
# These two settings together = no data loss on crash (at cost of ~20% write speed)

sudo service mysql restart

---

Step 2: Create Replication User on Master

-- On master MySQL server
CREATE USER 'replicator'@'%' IDENTIFIED BY 'strong_password_here';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;

-- Take a snapshot of master position
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
-- Output: File = mysql-bin.000023, Position = 4892134
-- Note these values - needed for CHANGE MASTER TO

-- In another connection, dump the database
-- mysqldump -u root -p --single-transaction --master-data=2 mydb > dump.sql
UNLOCK TABLES;

---

Step 3: Configure Replica (my.cnf)

# /etc/mysql/my.cnf on replica server
[mysqld]
server-id           = 2             # Must differ from master
relay_log           = /var/log/mysql/mysql-relay-bin.log
log_slave_updates   = 1             # Allow chaining replicas
read_only           = 1             # Prevent accidental writes to replica

# Replica doesn't need binary logging (unless it's also a master)
# skip_log_bin = 1

---

Step 4: Import Dump and Start Replication

# On replica server
mysql -u root -p mydb < dump.sql

mysql -u root -p

-- On replica
CHANGE MASTER TO
  MASTER_HOST     = '10.0.0.1',          -- Master private IP
  MASTER_USER     = 'replicator',
  MASTER_PASSWORD = 'strong_password_here',
  MASTER_LOG_FILE = 'mysql-bin.000023',  -- From SHOW MASTER STATUS
  MASTER_LOG_POS  = 4892134;

START SLAVE;
SHOW SLAVE STATUS\G
-- Check: Slave_IO_Running: Yes, Slave_SQL_Running: Yes
-- Seconds_Behind_Master: 0 (or close to 0)

---

Step 5: Route Queries in PHP Application

// db/Connection.php
class Connection {
    private PDO $master;
    private PDO $replica;
    private bool $forcemaster = false;

    public function __construct() {
        $this->master = new PDO(
            'mysql:host=10.0.0.1;dbname=mydb;charset=utf8',
            DB_USER, DB_PASS,
            [PDO::ATTR_PERSISTENT => true]
        );

        $this->replica = new PDO(
            'mysql:host=10.0.0.2;dbname=mydb;charset=utf8',
            DB_USER, DB_PASS,
            [PDO::ATTR_PERSISTENT => true]
        );
    }

    // After a write, force reads to master for this request
    // Prevents "read your own writes" inconsistency (replication lag)
    public function afterWrite(): void {
        $this->forcemaster = true;
    }

    public function read(): PDO {
        return $this->forcemaster ? $this->master : $this->replica;
    }

    public function write(): PDO {
        return $this->master;
    }
}

// Usage
$db = new Connection();

// Read from replica
$stmt = $db->read()->prepare('SELECT * FROM products WHERE id = ?');
$stmt->execute([$id]);
$product = $stmt->fetch();

// Write to master
$db->write()->prepare('UPDATE products SET stock = stock - 1 WHERE id = ?')
   ->execute([$id]);
$db->afterWrite();  // Subsequent reads in this request go to master

// Read after write - goes to master (consistent)
$stmt = $db->read()->prepare('SELECT stock FROM products WHERE id = ?');
$stmt->execute([$id]);

---

Monitoring Replication Lag

-- On replica: check how far behind master
SHOW SLAVE STATUS\G
-- Seconds_Behind_Master: this is the critical metric
-- 0-2s: normal
-- 2-10s: acceptable under write spikes
-- 10s+: investigate

-- On master: check replica is connected
SHOW SLAVE HOSTS;

# Cron job to alert on lag > 10 seconds
# check_replication.sh
LAG=$(mysql -u monitor -p... -e "SHOW SLAVE STATUS\G" | grep Seconds_Behind_Master | awk '{print $2}')
if [ "$LAG" -gt 10 ]; then
  echo "Replication lag: ${LAG}s" | mail -s "ALERT: MySQL replication lag" ops@company.com
fi

---

Results After Deploying Read Replica

On our 2-server setup (both: 4 CPU, 8 GB RAM, SSD):

Metric	Before	After
Master CPU (peak)	92%	38%
Avg query time (SELECT)	480ms	45ms
Order creation timeouts	12/hour	0/hour
Checkout conversion	71%	84%

The checkout conversion improvement came from eliminating the 3-5 second stalls users were experiencing when write queries competed with heavy SELECT traffic.

---

Common Problem: Replication Stops After Master Crash

If master crashes and restarts, replica may stop with Got fatal error 1236:

-- On replica
STOP SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000024', MASTER_LOG_POS=4;
-- The new log file starts at position 4 after restart
START SLAVE;
SHOW SLAVE STATUS\G

Prevention: use sync_binlog=1 and innodb_flush_log_at_trx_commit=1 on master (already in config above). These prevent position mismatches on crash.

---

Aunimeda builds backend systems with optimized database architectures - PostgreSQL, Redis, ClickHouse, and more.

Contact us for backend and database engineering. See also: Custom Software Development