MySQL 5.6 Release Notes
[+/-]
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 masters and replication slaves 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 option.
| Property | Value |
|---|---|
| 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 option specifies the server ID that is set in the
server_id system variable.
On a replication master and each replication slave, you
must specify the
--server-id option 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 master or slave. For example,
server-id=3. For additional information, see
Section 17.1.4.2, “Replication Master Options and Variables”, and
Section 17.1.4.3, “Replication Slave Options and Variables”.
If you omit --server-id, the
default server ID is 0. If the server ID is set to 0, binary logging
takes place, but a master with a server ID of 0 refuses any
connections from slaves, and a slave with a server ID of 0 refuses
to connect to a master. 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 replication slave.
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 Replication Slave Configuration”.
Beginning with MySQL 5.6, the server generates a true UUID in
addition to the --server-id supplied
by the user. This is available as the global, read-only variable
server_uuid.
| Property | Value |
|---|---|
| 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
Important
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, masters
and slaves know one another's UUIDs. The value of a
slave'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 master's UUID is available on the
slave in the output of SHOW SLAVE
STATUS.
Note
Issuing a STOP SLAVE or
RESET SLAVE statement does
not reset the master's UUID as used on
the slave.
In MySQL 5.6.5 and later, 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 slave I/O thread generates an error and aborts if
its master's UUID is equal to its own unless the
--replicate-same-server-id option has
been set. In addition, the slave I/O thread generates a warning if
either of the following is true:
No master having the expected
server_uuidexists.The master's
server_uuidhas changed, although noCHANGE MASTER TOstatement has ever been executed.
Note
The addition of the server_uuid
system variable in MySQL 5.6 does not change the
requirement for setting a unique
--server-id for each MySQL server
as part of preparing and running MySQL replication, as described
earlier in this section.
User Comments
User comments in this section are, as the name implies, provided by MySQL users.
The MySQL documentation team is not responsible for, nor do they endorse, any of
the information provided here.
Posted by
Rolf Martin-Hoster
on
February 6, 2017
It should be noted that server_id is filtered before the binlog event is written to the relay log. In the case where replication events are written from multiple sources (switchover/failover event or circular replication topologies) and an mysql instance is recovered from backup or other event occurs requiring a replay of binlogs, the previously committed events will be filtered out. This is well known historical behavior/feature, however with GTID support this is still a factor. You may find yourself surprised that you have missing transactions in your retrieved and executed transaction sets.