Replication - High Performance MySQL - page 458

No matter what technique you choose, get comfortable with it, and document or script
it. You will probably be doing it more than once, and you need to be able to do it in a
pinch if something goes wrong.
Recommended Replication Configuration
There are many replication parameters, and most of them have at least some effect on
data safety and performance. We explain later which rules to break and when. In this
section, we show a recommended, “safe” replication configuration that minimizes the
opportunities for problems.
The most important setting for binary logging on the master is sync_binlog :
sync_binlog=1
This makes MySQL synchronize the binary log's contents to disk each time it commits
a transaction, so you don't lose log events if there's a crash. If you disable this option,
the server will do a little less work, but binary log entries could be corrupted or missing
after a server crash. On a replica that doesn't need to act as a master, this option creates
unnecessary overhead. It applies only to the binary log, not to the relay log.
We also recommend using InnoDB if you can't tolerate corrupt tables after a crash.
MyISAM is fine if table corruption isn't a big deal, but MyISAM tables are likely to be
in an inconsistent state after a replica server crashes. Chances are good that a statement
will have been incompletely applied to one or more tables, and the data will be incon-
sistent even after you've repaired the tables.
If you use InnoDB, we strongly recommend setting the following options on the master:
innodb_flush_logs_at_trx_commit=1 # Flush every log write
innodb_support_xa=1 # MySQL 5.0 and newer only
innodb_safe_binlog # MySQL 4.1 only, roughly equivalent to
# innodb_support_xa
These are the default settings in MySQL 5.0 and newer. We also recommend specifying
a binary log base name explicitly, to create uniform binary log names on all servers and
prevent changes in binary log names if the server's hostname changes. You might not
think that it's a problem to have binary logs named after the server's hostname auto-
matically, but our experience is that it causes a lot of trouble when moving data between
servers, cloning new replicas, and restoring backups, and in lots of other ways you
wouldn't expect. To avoid this, specify an argument to the log_bin option, optionally
with an absolute path, but certainly with the base name (as shown earlier in this
chapter):
log_bin=/var/lib/mysql/mysql-bin # Good; specifies a path and base name
#log_bin # Bad; base name will be server's hostname