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 replication source servers and replicas are covered separately, as are options and variables relating to binary logging. 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.
Command-Line Format | --server-id=# |
---|---|
System Variable | server_id |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 0 |
Minimum Value | 0 |
Maximum Value | 4294967295 |
This variable specifies the server ID.
On a replication source server and each replica, you
must specify
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 replication source
server or replica. For additional information, see
Section 17.1.4.2, “Replication Source Options and Variables”, and
Section 17.1.4.3, “Replica Server Options and Variables”.
If you do not specify server_id
,
the default server ID is 0. 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.
In MySQL 5.6, whether the server ID is set to 0
explicitly or the default is allowed to be used, the server sets the
server_id
system variable to 1; this is a known
issue that is fixed in MySQL 5.7.
For more information, see Section 17.1.1.2, “Setting the Replica Configuration”.
Beginning with MySQL 5.6, the server generates a true UUID in
addition to the server_id
value
supplied by the user. This is available as the global, read-only
server_uuid
system variable.
System Variable | server_uuid |
---|---|
Scope | Global |
Dynamic | No |
Type | String |
When starting, the MySQL server automatically obtains a UUID as follows:
The auto.cnf
file has a format similar to that
used for my.cnf
or my.ini
files. In MySQL 5.6, auto.cnf
has
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]
server_uuid=8a94f357-aab4-11df-86ab-c80aa9429562
The auto.cnf
file is automatically generated;
do not attempt to write or modify this file.
Also beginning with MySQL 5.6, when using MySQL replication, sources
and replicas know one another's UUIDs. The value of a
replica's UUID can be seen in the output of
SHOW SLAVE HOSTS
. Once
START SLAVE
has been executed (but
not before), the value of the source's UUID is available on the
replica in the output of SHOW SLAVE
STATUS
.
Issuing a STOP SLAVE
or
RESET SLAVE
statement does
not reset the source's UUID as used on
the replica.
A server's server_uuid
is also used in GTIDs
for transactions originating on that server. For more information,
see Section 17.1.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_uuid
exists.The source's
server_uuid
has changed, although noCHANGE MASTER TO
statement has ever been executed.
The addition of the server_uuid
system variable in MySQL 5.6 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.