The following sections contain information about mysqld options and server variables that are used in replication and for controlling the binary log. Options and variables for use on sources and replicas are covered separately, as are options and variables relating to binary logging and global transaction identifiers (GTIDs). A set of quick-reference tables providing basic information about these options and variables is also included.
Of particular importance is the
server_id system variable.
This variable specifies the server ID. In MySQL 5.7,
server_id must be specified if
binary logging is enabled, otherwise the server is not allowed to
server_id is set to 0 by default.
On a replication source server and each replica, you
server_id to establish a unique
replication ID in the range from 1 to 232
− 1. “Unique”, means that each ID must be
different from every other ID in use by any other source or replica
in the replication topology. For additional information, see
Section 2.6.2, “Replication Source Options and Variables”, and
Section 2.6.3, “Replica Server Options and Variables”.
If the server ID is set to 0, binary logging takes place, but a source with a server ID of 0 refuses any connections from replicas, and a replica with a server ID of 0 refuses to connect to a source. Note that although you can change the server ID dynamically to a nonzero value, doing so does not enable replication to start immediately. You must change the server ID and then restart the server to initialize the replica.
For more information, see Section 18.104.22.168, “Setting the Replica Configuration”.
In MySQL 5.7, the server generates a true UUID in
addition to the
supplied by the user. This is available as the global, read-only
server_uuid system variable.
The presence of the
system variable in MySQL 5.7 does not change the
requirement for setting a unique
server_id value for each MySQL
server as part of preparing and running MySQL replication, as
described earlier in this section.
When starting, the MySQL server automatically obtains a UUID as follows:
auto.cnf file has a format similar to that
files. In MySQL 5.7,
only a single
[auto] section containing a single
server_uuid setting and value; the
file's contents appear similar to what is shown here:
auto.cnf file is automatically generated;
do not attempt to write or modify this file.
When using MySQL replication, sources and replicas know each
other's UUIDs. The value of a replica's UUID can be seen
in the output of
SHOW SLAVE HOSTS.
START SLAVE has been executed,
the value of the source's UUID is available on the replica in
the output of
SHOW SLAVE STATUS.
STOP SLAVE or
RESET SLAVE statement does
not reset the source's UUID as used on
server_uuid is also used in GTIDs
for transactions originating on that server. For more information,
see Section 2.3, “Replication with Global Transaction Identifiers”.
When starting, the replication I/O thread generates an error and
aborts if its source's UUID is equal to its own unless the
--replicate-same-server-id option has
been set. In addition, the replication I/O thread generates a
warning if either of the following is true:
No source having the expected
server_uuidhas changed, although no
CHANGE MASTER TOstatement has ever been executed.