Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.0Mb
PDF (A4) - 31.0Mb
PDF (RPM) - 29.2Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb
HTML Download (RPM) - 6.2Mb
Man Pages (TGZ) - 175.7Kb
Man Pages (Zip) - 286.0Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb
Excerpts from this Manual

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

17.1.4 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. 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, “Replication Master Options and Variables”, and Section, “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, “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:

  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.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:


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.


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:


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
  Posted by kit chen on September 16, 2002
If you're attempting to use both
be aware that you need to actually say
because the rewrite rule apparently happens before
the do-db rule.

thanks to Therion on opn/freenode for
troubleshooting this with me.
  Posted by Michael Babcock on November 8, 2002
I was about to post the same comment, but as it
applies to replicate-wild-do-table.

replicate-wild-do-table = LocalTableName.%
replicate-rewrite-db = RemoteTableName ->
  Posted by Ken Allan on November 25, 2002
Be really careful with the use of the
replicate-wild-do-table=db_name.% configuration
option. In 4.0.4, this option caused updates to
any specified tables to not work for me.

I had read in the documentation that this was
needed for cross database updates, but it was
causing my same database updates to fail.

I had the following options set in my slave my.cnf:
server-id = 16
master-host = 64.xx.xx.xx
master-user = replicator
master-password = *****
replicate-wild-do-table = banner.%
replicate-do-db = banner
report-host = 64.xx.xx.xx

Also, worth mentioning is that there seems to be
some limit in the server-id's, initially i set my
server-id to 15001 and this caused replication to
fail silently to even start up. Changed it to 16,
and it works perfectly, all this despite the
alleged limit of 2^32-1.

  Posted by Fajar Nugraha on June 9, 2003
I have this setup working :

A -> B -> A

I got in running with mysql 4.0.13-max, using MyISAM and InnoDB tables.

Here's how I do it on A:
- enable bin-log (just add log-bin in /etc/my.cnf. Restart mysqld if necessary.)
- create a replication user on A (I give it all privileges. You probably shouldn't do that).
- execute query
- do
tar -cvf /tmp/mysql-snapshot.tar /path/to/data-dir
- execute query
write down the result for
- modify /etc/my.cnf to include
- shutdown mysqld on A (my root is password-protected, and I do it from another terminal)
mysqladmin -uroot -p shutdown
- start it back up

on B (make sure there are NO update queries on B at this point):
- make sure mysqld is dead
- copy and untar mysql-snapshot.tar created earlier
- copy my.cnf from A, put DIFFERENT number in server_id.
- start mysqld (make sure binary log is enabled)
- execute queries (this is where you put the values you got earlier from SHOW MASTER STATUS on A):
MASTER_USER='<replication user name>',
MASTER_PASSWORD='<replication password>',
MASTER_LOG_FILE='<recorded log file name>',
MASTER_LOG_POS=<recorded log offset>;
- execute query
write down the values

At this point you got A->B replication

on A again:
- copy B's *.bin.* (binary logs), put it in A's data dir
- execute queries (this is where you put the values you got earlier from SHOW MASTER STATUS on B):
MASTER_USER='<replication user name>',
MASTER_PASSWORD='<replication password>',
MASTER_LOG_FILE='<recorded log file name>',
MASTER_LOG_POS=<recorded log offset>;

And you're done! If you do what I do, you will have the same user on both A and B, and this replication setup :

A -> B -> A

You can now execute any query on any of them, and it will appear on both. You can even call it a mysql cluster.
  Posted by Steve Rapaport on July 22, 2003
Very nice, but remember that in my experience, setting up a working replication is the EASY part. The hard part is always what to do after one machine fails, to reset both
and restart the replication properly.

With A->B replication this is easy -- either switch masters as described in the Replication FAQ, or copy the slave back to the master, reset all the logs, and start again.

With A->B->A replication I would never be certain that I had reset correctly, or even that all my last transactions before the failure were all on the same machine! So I wouldn't do it. It's a low-reliability system, which kind of defeats the purpose (for me) of replication.
  Posted by Paul Howell on January 31, 2006
Fajar Nugraha has a great tip a few comments above me, however, he is missing one important step. On B, you need to do another GRANT to create a user so that A can access B as a slave.
  Posted by Justin Swanhart on March 15, 2007

slave-skip-errors is _not_ a good idea on dual-masters. If you have a dual-master setup you must ensure that writes go to one master, or that you run version 5+ and use the auto_increment offset and increment options.

If you use the slave-skip-errors option suggested by a previous commenter you will end up with hopelessly inconsistent data. With the slave-skip-errors set as suggested there will be records on one machine with the same primary key id, but different column values.

It is also difficult to ascertain the proper log positions when trying to restore a failed master when both masters are written to.
  Posted by Zoltán Pósfai on January 12, 2009
The above ABA setup has a couple of shortcomings everyone has to be aware before using it. (I've been using it for years...)

Never use both sides for writes as statements need time to get to the slave. For example:
- Sending two update on the same value makes it unpredictable which one will be the final one (you may even end up with different values on the two sides)
- Doing queries that use auto-incremented fields may give different results depending on which node you are when you just incremented the field.
- If sync breaks you loose the executed but not replicated queries on one side. If the sync breaks because of connection error between A-B but they are reachable from clients, you may en up with a completely screwed up db! (example client->frontend, replication->backend)

This setup is more a kind of HA/switchover setup than clustering...

If you want HA and clustering and use only 'basic' mysql features do:

- ABA setup in failover setup
- add VRRP'ed IP seen by slave farm as master (and same users to A and B; you may as well fire up the ABA setup for the 'mysql' db)
- separate rw and ro operations in clients, use A(B) for writes and use the slave farm for ro
- loadbalance between slaves (choose you flavour for lb)

  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.