Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.9Mb
PDF (A4) - 39.0Mb
PDF (RPM) - 38.1Mb
HTML Download (TGZ) - 10.8Mb
HTML Download (Zip) - 10.8Mb
HTML Download (RPM) - 9.5Mb
Man Pages (TGZ) - 210.9Kb
Man Pages (Zip) - 320.0Kb
Info (Gzip) - 3.5Mb
Info (Zip) - 3.5Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Replication and Binary Logging Options and Variables

16.1.6 Replication and Binary Logging Options and Variables

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 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 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. In MySQL 5.7, the --server-id option must be specified if binary logging is enabled, otherwise the server is not allowed to start.

The server_id system variable is set to 0 by default. 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 16.1.6.2, “Replication Master Options and Variables”, and Section 16.1.6.3, “Replication Slave Options and Variables”.

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.

For more information, see Section 16.1.2.5.1, “Setting the Replication Slave Configuration”.

server_uuid

In MySQL 5.7, 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.

Note

The presence of the server_uuid system variable in MySQL 5.7 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.

Property Value
System Variable server_uuid
Scope Global
Dynamic No
Type String

When starting, the MySQL server automatically obtains a UUID as follows:

  1. Attempt to read and use the UUID written in the file data_dir/auto.cnf (where data_dir is the server's data directory).

  2. If data_dir/auto.cnf is not found, generate a new UUID and save it to this file, creating the file if necessary.

The auto.cnf file has a format similar to that used for my.cnf or my.ini files. In MySQL 5.7, 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.

When using MySQL replication, masters and slaves know each other'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, 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.

A server's server_uuid is also used in GTIDs for transactions originating on that server. For more information, see Section 16.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:


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.
Sign Up Login You must be logged in to post a comment.