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.

13 thoughts on “Replication configuration checklist

  1. @Shantanu:

    I strongly disagree on slave-skip-errors=all.
    I always prefer the slave to actually stop replicating upon error. There are good reasons for it to stop replicating. Simply ignoring the errors is, well, error-prone. This is one setting I never use.

    Good points for all the rest!

  2. I have 2 slaves and one is used for testing purpose. I have set slave-skip-errors on that slave. It is also possible to skip the specific errors if you know the error number for e.g.
    slave-skip-errors=1205,1422,1062
    1205 Error on master: ‘Lock wait timeout exceeded; try restarting transaction’ (1205), Error on slave: ‘no error’ (0).
    1422 Explicit or implicit commit is not allowed in stored function or trigger
    1062 Duplicate entry for key 1
    This is not recommended for obvious reasons. But comes handy at times.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.