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.
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.
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.
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.
@Harrison
Thank you
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).
Hi Ryan,
Oh dear! Yes, indeed! Thank you.
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,… Read more »
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.
@jay,
already on the list 😛
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
@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!
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.
[…] 注：常用的MySQL复制配置选项可以参考Replication configuration checklist。 […]