Replication - High Performance MySQL - page 459

On the replica, we also recommend enabling the following configuration options. We
also recommend using an absolute path for the relay log location:
relay_log=/path/to/logs/relay-bin
skip_slave_start
read_only
The relay_log option prevents hostname-based relay log file names, which avoids the
same problems we mentioned earlier that can happen on the master, and giving the
absolute path to the logs avoids bugs in various versions of MySQL that can cause
the relay logs to be created in an unexpected location. The skip_slave_start option
will prevent the replica from starting automatically after a crash, which can give you a
chance to repair a server if it has problems. If the replica starts automatically after a
crash and is in an inconsistent state, it might cause so much additional corruption that
you'll have to throw away its data and start fresh.
The read_only option prevents most users from changing non-temporary tables. The
exceptions are the replication SQL thread and threads with the SUPER privilege. This is
one of the many reasons you should try to avoid giving your normal accounts the
SUPER privilege.
Even if you've enabled all the options we've suggested, a replica can easily break after
a crash, because the relay logs and master.info file aren't crash-safe. They're not even
flushed to disk by default, and there's no configuration option to control that behavior
until MySQL 5.5. You should enable those options if you're using MySQL 5.5 and if
you don't mind the performance overhead of the extra fsync() calls:
sync_master_info = 1
sync_relay_log = 1
sync_relay_log_info = 1
If a replica is very far behind its master, the slave I/O thread can write many relay logs.
The replication SQL thread will remove them as soon as it finishes replaying them (you
can change this with the relay_log_purge option), but if it is running far behind, the
I/O thread could actually fill up the disk. The solution to this problem is the
relay_log_space_limit configuration variable. If the total size of all the relay logs grows
larger than this variable's size, the I/O thread will stop and wait for the SQL thread to
free up some more disk space.
Although this sounds nice, it can actually be a hidden problem. If the replica hasn't
fetched all the relay logs from the master, those logs might be lost forever if the master
crashes. And this option has had some bugs in the past, and seems to be uncommonly
used, so the risk of bugs is higher when you use it. Unless you're worried about disk
space, it's probably a good idea to let the replica use as much space as it needs for relay
logs. That's why we haven't included the relay_log_space_limit setting in our recom-
mended configuration.