Replication configuration checklist

This post lists the essential and optional settings for a replication environment.

It does not explain how to create replicating slaves. See How To Setup Replication for that. However, not all configuration options are well understood, and their roles in varying architectures can change.

Here are the settings for a basic Master/Slave(s) replication architecturee.

Essential

• log-bin: enable binary logs on the master. Replication is based on the master logging all modifying queries (INSERT/CREATE/ALTER/GRANT etc.), and the slaves being able to replicate them.
• server-id: each machine must have a unique server-id. A slave will not replay queries originating from a server with the same server-id as its own.
• GRANT: grant a user with REPLICATION SLAVE. The host list must include all replication slave hosts.
• expire-logs-days: automatically clean up master’s binary logs older than given value. By default, binary logs are never removed.

When working with Master/Slaves replication, one should be prepared to master failure and slave promotion to master. It may be desirable to identify a particular slave as primary candidate for promotion.

Just setting up the log-bin will yield with warnings in the MySQL’s error log. The binary logs are named, by default, after the host’s name. If that should change – MySQL will not be able to find the binary logs anymore (expecting a name which does previous logs did not use). It is therefore recommended to use:

log-bin=mychachine-bin

or

log-bin=mysql-bin

Essential/Optional

• log-bin: enable on a slave, so that in case it is promoted to master, the rest of the slaves can replicate using its binary logs. Enabling binary logging cannot be done on a live server: this parameter requires MySQL restart.
• GRANT: include the master’s host, so that when a slave promotes to master, the master can become a slave and continue replicating.
• log-slave-updates: together with log-bin, enable on slave so that master’s binary logs are propagated and logged by the slave. This is required if the slave takes the role of a master in a chained replication setup.
• expire-logs-days: set this flag on slave as well [tnx Sheeri].
• read-only: set on slave(s). Refuses any modifying query (INSERT, DELETE, ALTER, DROP etc.) for non-SUPER privileged users [tnx Ryan].
• sync-binlog: flush binary log to disk per transaction commit. Use this on master for safer replication; however note that increased I/O is expected [tnx Harrison].

Extra

• report-host, report-port: the host and port identifying the slave when looking at SHOW SLAVE HOSTS on master. Set this up on all hosts. See further discussion here.
• max-binlog-size: the maximum size for a binary log / relay log file, after which it is rotated.

Expert

• binlog-do-db, binlog-do-table, replicate-do-db, …: filter queries by either not writing them to binary log, or not reading them from the logs.

The reason I list the above as “Expert” is not because one must have a super-brain to set them up. That part is easy enough. But they lead to some dangerous situations, sometimes seemingly harmless. It takes great care to control the application and developers from creating those situations. See documentation here. See also discussion here and here.