Adding a MySQL 5.1 Read Slave to Your PHP App

Most PHP sites hit the same wall eventually. Web servers are easy to multiply, but they all talk to one MySQL server, and that server spends its day answering SELECTs while writes are a small slice of the traffic. That shape is exactly what MySQL replication is good at, and with 5.1 now a production release (5.1.30 went GA in November and 5.1.32 is the current build), it’s a good time to add a read slave.

This post walks through the setup using the replication chapter of the MySQL 5.1 Reference Manual, then covers the parts the setup steps don’t warn you about: lag, what it does to your PHP code, and why the slave is not your backup.

How replication works in one paragraph

The master writes every change to its binary log. The slave connects over TCP/IP, and its I/O thread copies those events into a local relay log. A second thread, the SQL thread, reads the relay log and applies the changes. It’s asynchronous, which means the master never waits for the slave. That’s great for performance and it’s the root of every surprise later on. The manual’s scale-out section describes the model plainly: all writes go to the master, reads can go to the master or any slave.

Configure the master

Replication runs off the binary log, so the master needs it turned on, and every server in the group needs a unique server-id. Both go in the [mysqld] section of my.cnf and need a restart:

[mysqld]
log-bin = mysql-bin
server-id = 1
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

The last two lines are the manual’s recommendation for the best durability and consistency if you use InnoDB with transactions. Also check that skip-networking isn’t set, because a slave can’t replicate over a Unix socket.

Then create an account for the slave to log in with. It only needs one privilege:

GRANT REPLICATION SLAVE ON *.*
  TO 'repl'@'10.0.0.%' IDENTIFIED BY 'a-long-random-password';

Keep this account separate from your app’s account. The slave stores the replication user name and password in plain text in a file called master.info, so you want that password to be good for replication and nothing else.

Configure the slave

The slave’s my.cnf needs its own server-id. If you leave it unset it defaults to 0, and with 0 the slave refuses to connect to a master at all. I’d also turn on read_only here, more on that below:

[mysqld]
server-id = 2
read_only = 1

The slave doesn’t need binary logging for basic replication. You’d only want it if you plan to chain another slave off this one or use its logs for recovery.

Take a snapshot that matches a log position

This is the step people rush. The slave needs a copy of the data plus the exact spot in the master’s binary log that the copy corresponds to. If the two don’t line up, the slave either misses changes or applies some twice, and you end up with quiet inconsistencies.

The manual’s approach is to lock the master, read the position, and dump. In one mysql client session:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Write down the File and Position values, and leave that client open. The lock goes away when the session ends. Note that on InnoDB this read lock also blocks COMMIT, so your site’s writes stall until you unlock. From a second shell, dump the data, then go back to the first session and run UNLOCK TABLES.

mysqldump can do the bookkeeping for you. The --master-data option records the master’s binary log coordinates in the dump as a CHANGE MASTER TO statement, and it locks all tables while it runs. If your tables are all InnoDB, adding --single-transaction changes that: mysqldump takes the global read lock only briefly at the start, then dumps a consistent view inside a transaction without blocking the app. MyISAM tables don’t get that consistency, so don’t lean on it for them. While that kind of dump runs, avoid ALTER TABLE, DROP TABLE, RENAME TABLE and TRUNCATE TABLE on the master.

mysqldump --all-databases --master-data=2 --single-transaction > snapshot.sql

With --master-data=2 the CHANGE MASTER TO line is written as a comment, so it’s in the file for reference but doesn’t run on import.

Point the slave at the master

Load the dump on the slave, then tell it where to connect and where in the log to start:

CHANGE MASTER TO
  MASTER_HOST = '10.0.0.10',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'a-long-random-password',
  MASTER_LOG_FILE = 'mysql-bin.000012',
  MASTER_LOG_POS = 98765;
START SLAVE;

The file name and position are whatever SHOW MASTER STATUS or the dump comment gave you. Don’t trust START SLAVE returning without error, though. The manual points out that it only tells you the threads started, not that they connected and stayed up. Check for real:

SHOW SLAVE STATUS\G

You want Slave_IO_Running: Yes, Slave_SQL_Running: Yes and an empty Last_Error. The other field to watch is Seconds_Behind_Master, which I’ll come back to.

Statement, row or mixed

This is the part that’s genuinely new in 5.1. Before 5.1, MySQL replicated statements: the master logged the SQL text and the slave ran the same SQL. That’s still available, but 5.1 adds row-based logging, where the master logs the actual row changes, and a mixed mode that uses statements by default and switches to rows when a statement isn’t safe to replay. You choose with binlog_format, set to STATEMENT, ROW or MIXED, either in my.cnf or at runtime with SET GLOBAL (which needs the SUPER privilege).

The default has moved around during 5.1 development. It was mixed from 5.1.12 through 5.1.28, and from 5.1.29 on it’s back to statement. So if you’re on a current 5.1 and never set it, you’re replicating statements.

Statement-based replication has been around since 3.23 and logs very little. The trouble is statements that don’t give the same answer twice. The manual calls out things like UUID(), USER(), FOUND_ROWS(), and an UPDATE or DELETE with LIMIT but no ORDER BY, which could hit different rows on the slave. MySQL logs a warning when it sees an unsafe statement, and your error log is worth reading after you turn replication on. Row-based replication replicates everything correctly, at the cost of bigger binary logs when a statement changes lots of rows.

The 5.1 manual’s own advice is that mixed gives the best combination of data integrity and performance for most users, and for a typical PHP app I agree. You keep small logs for ordinary queries and get row logging for the risky ones.

Replication lag and your PHP code

Here’s the gotcha. Because replication is asynchronous, the slave is always at least a little behind, and sometimes a lot. A big ALTER TABLE or a slow batch UPDATE that took two minutes on the master will take about that long to replay on the slave, and changes queued behind it wait.

Seconds_Behind_Master gives you a rough number, but read it with care. It measures how far the SQL thread is behind the slave’s I/O thread, so on a slow network it can say 0 while the I/O thread is actually behind. It shows NULL when either thread isn’t running. A NULL is an alert, not a zero.

Now picture a user who posts a comment. The INSERT goes to the master, you redirect back to the thread, and the page reads from the slave, which doesn’t have the comment yet. The user thinks it failed and posts again. The fix is simple once you see it: reads that follow a write from the same user should go to the master for a little while.

A small wrapper handles it. This uses mysqli and remembers the time of the last write in the session:

<?php
function db_master() {
    static $link = null;
    if ($link === null) {
        $link = mysqli_connect('10.0.0.10', 'app', 'secret', 'shop');
    }
    return $link;
}

function db_slave() {
    static $link = null;
    if (isset($_SESSION['last_write']) && time() - $_SESSION['last_write'] < 5) {
        return db_master();
    }
    if ($link === null) {
        $link = mysqli_connect('10.0.0.11', 'app_read', 'secret', 'shop');
        if (!$link) {
            return db_master();
        }
    }
    return $link;
}

function db_write($sql) {
    $_SESSION['last_write'] = time();
    return mysqli_query(db_master(), $sql);
}

Five seconds is a guess you should tune against what Seconds_Behind_Master looks like on your servers. Anything that needs to be exactly right, like checking a balance before a purchase or reading a row you just inserted in the same request, should simply use db_master(). The manual recommends this kind of wrapper too: once every query in the app goes through one read function and one write function, adding a second slave later is a config change.

read_only on the slave

A slave that accepts writes from clients will drift from the master, and nothing will tell you. Setting read_only makes the server reject updates except from the slave’s own replication thread and from users with the SUPER privilege. It doesn’t cover TEMPORARY tables. The SUPER exception is the catch: if your app’s MySQL account has SUPER, read_only won’t stop it. Give the app a plain account on the slave with SELECT and not much more.

A slave is not a backup

Replication copies mistakes as faithfully as it copies good data. A DROP TABLE or a DELETE with a bad WHERE clause runs on the master and shows up on the slave a moment later. The slave does make a great place to take backups from, since you can stop the SQL thread, dump, and start it again without touching the master, and the 5.1 manual describes exactly that use. But you still need the dumps, and they need to live somewhere other than these two servers.

If your MySQL box is spending its time on reads, a slave is one of the cheapest scaling steps you can take. Get the snapshot position right, pick mixed format, watch the slave status, and route reads that follow a write back to the master.