Replication configuration checklist

May 18, 2010

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.

tags: ,
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

13 Comments to "Replication configuration checklist"

  1. Sheeri Cabral wrote:

    Hi Shlomi,

    I would call "expire-logs-days" on the master "Essential/Optional" -- they're not required to make replication work, but they're a good idea.

    I would also say the same for expire-logs-days on the slave, too.

  2. shlomi wrote:

    Hi Sheeri,
    Thanks. Perhaps you are right. I've actually seen a couple installations where nearly all disk space was consumed by unpurged binary logs; and purging them manually can be intimidating to non-dbas. Having them removed after, say, 7 days, is both safe-enough and self-solving.

    You are right - this should be on the slave as well.

  3. Harrison wrote:

    I would also add sync-binlog=1 to the list. It is important if you want replication to recover after a server crash on the master. Of course, there is overhead to it, so you need to decide if it is worth it or not for your application.

  4. shlomi wrote:

    @Harrison
    Thank you

  5. Ryan wrote:

    Hey Shlomi,

    What about read_only on the slave(s)? This should help to ensure that slaves don't receive accidental writes (assuming application user grants don't include SUPER).

  6. shlomi wrote:

    Hi Ryan,

    Oh dear! Yes, indeed! Thank you.

  7. James Day wrote:

    Those options can definitely be interesting but do you always want to halve the replication throughput of all of your slaves?

    In one case I was handling recently, turning off the binary log, the query cache and adding innodb_support_xa = 0 and innodb_locks_unsafe_for_binlog = 1 increased a lagged slave's throughput from one to two binary logs per hour to two to four per hour.

    Sometimes a mixture of high reliability and high speed slave configurations might be the best solution.

    On the slave it's best to stick to the table-based replication rules if you can. Also all of one type, say a mixture of do and wild do. It's easier to understand and not get caught out that way. You also get the benefit of not relying solely on the USEd database to determine whether a table can be updated or not, potentially missing updates if the update was carried out from a different database than the table itself.

    On the master, do remember that the primary purpose of the binary log is disaster recovery. If you don't write things to the binary log you're not going to be able to get them back from it when you're replaying the binary log after hitting say a serious disk damage based corruption problem. The slaves might save you but it's worth considering writing more to the binary log.

  8. jay bharat wrote:

    Sync-binlog=1 to the list. It is important if you want replication to recover after a server crash on the master. Of course, there is overhead to it, so you need to decide if it is worth it or not for your application.

  9. shlomi wrote:

    @jay,
    already on the list :P

  10. Shantanu Oak wrote:

    skip-slave-start
    # do not start slave thread when the server is restarted
    slave-skip-errors=all
    # use the error number or 'all' to skip all errors
    relay-log=/var/log/mysql/
    # good to have the path for relay log
    max_allowed_packet=500M
    # yes. you need it for replication too!

    The "expert" will also need
    replicate-wild-ignore-table=test.%
    # to ignore all the tables from test database

  11. shlomi wrote:

    @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!

  12. Shantanu Oak wrote:

    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.

  13. MySQL复制的概述、安装、故障、技巧、工具 | 火丁笔记 wrote:

    [...] 注:常用的MySQL复制配置选项可以参考Replication configuration checklist。 [...]

Leave Your Comment

 

 
Powered by Wordpress and MySQL. Theme by openark.org