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 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, 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.
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