MySQL 5.7 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 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.
| Command-Line Format | --server-id=# | ||
| System Variable | Name | server_id | |
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | Type | integer | |
| Default | 0 | ||
| Min Value | 0 | ||
| Max Value | 4294967295 | ||
This option is common to both master and slave replication servers, and is used in replication to enable master and slave servers to identify themselves uniquely. 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”.
On the master and each slave, you must use 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.
In MySQL 5.7.2 and earlier, if you start a master server without
using --server-id to set its ID, the
default ID is 0. In this case, the master refuses connections from
all slaves, slaves refuse to connect to the master, and the server
sets the server_id system variable
to 1. In MySQL 5.7.3 and later, the --server-id
must be used if binary logging is enabled, and a value of 0 is not
changed by the server. If you specify
--server-id without an argument, the
effect is the same as using 0. In either case, if the
server_id is 0, binary logging takes place, but
slaves cannot connect to the master, nor can any other servers
connect to it as slaves. (Bug #11763963, Bug #56718)
For more information, see Section 16.1.2.5.1, “Setting the Replication Slave Configuration”.
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.
| System Variable | Name | server_uuid | |
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | 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.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:
No master having the expected
server_uuidexists.The master's
server_uuidhas changed, although noCHANGE MASTER TOstatement has ever been executed.
replicate-do-db=from_name
and
replicate-rewrite-db=from_name->to_name
be aware that you need to actually say
replicate-do-db=to_name
because the rewrite rule apparently happens before
the do-db rule.
thanks to Therion on opn/freenode for
troubleshooting this with me.
applies to replicate-wild-do-table.
replicate-wild-do-table = LocalTableName.%
replicate-rewrite-db = RemoteTableName ->
LocalTableName
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.
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
FLUSH TABLES WITH READ LOCK;
- do
tar -cvf /tmp/mysql-snapshot.tar /path/to/data-dir
- execute query
SHOW MASTER STATUS;
write down the result for
- modify /etc/my.cnf to include
server-id=<number-of-your-choice>
- 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):
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='<A host name>',
MASTER_USER='<replication user name>',
MASTER_PASSWORD='<replication password>',
MASTER_LOG_FILE='<recorded log file name>',
MASTER_LOG_POS=<recorded log offset>;
START SLAVE;
- execute query
SHOW MASTER STATUS;
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):
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='<B host name>',
MASTER_USER='<replication user name>',
MASTER_PASSWORD='<replication password>',
MASTER_LOG_FILE='<recorded log file name>',
MASTER_LOG_POS=<recorded log offset>;
START SLAVE;
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.
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.
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.
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)