Databases Reference
In-Depth Information
log_bin = mysql-bin
server_id = 2
relay_log = /var/lib/mysql/mysql-relay-bin
log_slave_updates = 1
read_only = 1
Several of these options are not technically necessary, and for some we're just making
defaults explicit. In reality, only the
server_id
parameter is required on a replica, but
we enabled
log_bin
too, and we gave the binary log file an explicit name. By default it
is named after the server's hostname, but that can cause problems if the hostname
changes. We are using the same name for the master and replicas to keep things simple,
but you can choose differently if you like.
We also added two other optional configuration parameters:
relay_log
(to specify the
location and name of the relay log) and
log_slave_updates
(to make the replica log
the replicated events to its own binary log). The latter option causes extra work for the
replicas, but as you'll see later, we have good reasons for adding these optional settings
on every replica.
Some people enable just the binary log and not
log_slave_updates
, so they can see
whether anything, such as a misconfigured application, is modifying data on the replica.
If possible, it's better to use the
read_only
configuration setting, which prevents any-
thing but specially privileged threads from changing data. (Don't grant your users more
privileges than they need!) However,
read_only
is often not practical, especially for
applications that need to be able to create tables on replicas.
Don't place replication configuration options such as
master_host
and
master_port
in the replica's
my.cnf
file. This is an old, deprecated way
to configure a replica. It can cause problems and has no benefits.
Starting the Replica
The next step is to tell the replica how to connect to the master and begin replaying its
binary logs. You should not use the
my.cnf
file for this; instead, use the
CHANGE MASTER
TO
statement. This statement replaces the corresponding
my.cnf
settings completely. It
also lets you point the replica at a different master in the future, without stopping the
server. Here's the basic statement you'll need to run on the replica to start replication:
mysql>
CHANGE MASTER TO MASTER_HOST='server1',
->
MASTER_USER='repl',
->
MASTER_PASSWORD='p4ssword',
->
MASTER_LOG_FILE='mysql-bin.000001',
->
MASTER_LOG_POS=0;
The
MASTER_LOG_POS
parameter is set to
0
because this is the beginning of the log. After
you run this, you should be able to inspect the output of
SHOW SLAVE STATUS
and see
that the replica's settings are correct:
