By default, MySQL is configured only to listen on the localhost
address (127.0.0.1). The
should be checked to ensure that it is either set to a valid
value, or commented to allow listening on all available network
#bind-address = 127.0.0.1
Specify the server id
Each server must have a unique server id:
server-id = 1
Ensure that the maximum number of open files matches the
configuration of the database user. This was configured earlier at
open_files_limit = 65535
Enable binary logs
Tungsten Replicator operates by reading the binary logs on each
machine, so logging must be enabled:
log-bin = mysql-bin
parameter to 1 (one).
In MySQL 5.7, the default value is 1.
parameter sets the frequency at which the binary log is flushed to
disk. A value of zero indicates that the binary log should not be
synchronized to disk, which implies that only standard operating
system flushing of writes will occur. A value greater than one
configures the binary log to be flushed only after
have been written. This can introduce a delay into writing
information to the binary log, and therefore replication, but also
opens the system to potential data loss if the binary log has not
been flushed when a fatal system error occurs.
Setting a value of value 1 (one) will synchronize the binary log
on disk after each event has been written.
sync_binlog = 1
Increase MySQL protocol packet sizes
The replicator can apply statements up to the maximum size of a
single transaction, so the maximum allowed protocol packet size
must be increase to support this:
max_allowed_packet = 52m
Configure InnoDB as the default storage engine
Tungsten Replicator needs to use a transaction safe storage engine to
ensure the validity of the database. The InnoDB storage engine
also provides automatic recovery in the event of a failure. Using
MyISAM can lead to table corruption, and in the event of a
switchover or failure, and inconsistent state of the database,
making it difficult to recover or restart replication effectively.
InnoDB should therefore be the default storage engine for all
tables, and any existing tables should be converted to InnoDB
before deploying Tungsten Replicator.
default-storage-engine = InnoDB
Configure InnoDB Settings
Tungsten Replicator creates tables and must use InnoDB tables to
store the status information for replication configuration and
The MySQL option
configures how InnoDB writes and confirms writes to disk during a
transaction. The available values are:
A value of 0 (zero) provides
the best performance, but it does so at the potential risk of
losing information in the event of a system or hardware
failure. For use with Tungsten Replicator™ the value should
never be set to 0, otherwise the cluster health may be
affected during a failure or failover scenario.
A value of 1 (one) provides
the best transaction stability by ensuring that all writes to
disk are flushed and committed before the transaction is
returned as complete. Using this setting implies an increased
disk load and so may impact the overall performance.
When using Tungsten Replicator™ in a multi-master,
multi-site, fan-in or data critical cluster, the value of
should be set to 1. This not only ensures that the
transactional data being stored in the cluster are safely
written to disk, this setting also ensures that the metadata
written by Tungsten Replicator™ describing the cluster and
replication status is also written to disk and therefore
available in the event of a failover or recovery situation.
A value of 2 (two) ensures
that transactions are committed to disk, but data loss may
occur if the disk data is not flushed from any OS or
hardware-based buffering before a hardware failure, but the
disk overhead is much lower and provides higher performance.
This setting must be used as a minimum for
all Tungsten Replicator™ installations,
and should be the setting for all configurations that do not
At a minimum
should be set to
warning will be generated if this value is set to zero:
innodb_flush_log_at_trx_commit = 2
MySQL configuration settings can be modified on a running cluster,
providing you switch your host to maintenance mode before
reconfiguring and restarting MySQL Server. See
Section 8.13, “Performing Database or OS Maintenance”.