Avoiding duplicate server IDs
A key configuration item in any replication setup is server IDs. They must be unique across all participating master and slave machines. Unfortunately, there is no official way to verify this reliably. Instead, when you introduce duplicates by mistake, strange behavior may surface. Generally, this happens when cloning the machines from an image.
Most importantly, on the master server you will not see any indication of the problem. The problem arises only on the slaves without clearly stating the root cause of the problem. See the There's more... section of this recipe for more details.
Getting ready
The server-id setting does not carry any meaning in and of itself, but is only used to internally distinguish servers from each other. Generally, administrators setting up new MySQL servers enter sequential or random values for this field. This requires a list of server IDs already issued, preferably including the host name. As with most things in life that need to be done manually, maintaining this list is likely to become a burden and will be forgotten.
Instead, you can assign server IDs based on features of the individual machines that are usually unique already, for example, the network interface's MAC address or the IP address, which should remain reasonably fixed for any server machine as well.
IP addresses are usually shown in a dotted notation of four numbers between 0 and 255. Because MySQL requires server-id to be specified as single decimal value, you need to convert it first.
How to do it…
- Determine your server's IP address. Make sure not to use the loop-back adapter or a similar pseudo-interface. In this example we assume an IP address of
10.0.159.22. - Convert the 4 bytes of the address to hexadecimal. Mostly any calculator application can do this for you. You enter each of the four numbers in decimal mode and then switch to hexadecimal mode. Just replace each individual decimal value with its hexadecimal counterpart. For the address above you will come up with:
0a.00.9f.16 - Append the bytes (that is just remove the dots between them) and convert them back to decimal by switching modes:
0a009f16HEX=167812886DEC - Insert that final value as the server ID in the [mysqld] section of that server's configuration file:
[mysqld] server-id=167812886
How it works...
The IP address serves to uniquely identify a network interface (and therefore a machine) on a network. We leverage this uniqueness by recycling the IP address as the server ID. Most operating systems will issue a warning when an IP address conflict is detected, so this indirectly points to a replication problem as well.
Note
Of course, traditional IPv4 addresses (those usually noted in the above notation) are only unique in their respective subnet. That means you should not rely on this recipe alone for your server IDs if master and slave machines are located in different locations from a network topology point of view!
There's more...
The IP address is only one possible unique value you can use. Anything that you can fit in the valid numeric range of the server-id setting can be used. Ideally that value should never change over the lifetime of a server, much like a good Primary key, just not for a single database record, but the server as a whole.
You could use any sort of serial number your hardware vendor already assigns to the machine, if it is purely numeric and fits the valid range of 4 bytes. However, this ties you to the vendor's idea of uniqueness, which you cannot verify reliably. Alternatively, the last 4 bytes of the server's MAC address (those are 6 bytes long, starting with a vendor specific prefix) could be used as well. However, beware that unless you exclusively use network adapter chip sets from a single vendor, there remains a certain danger of duplicates.
Recognizing symptoms of duplicate server IDs
Despite all care, errors can happen and duplicate server-ids can be issued. Unfortunately, MySQL will not tell you explicitly when you have non-unique server-ids in your replication setup. While on the master, you will not see any evidence in the log files that something is wrong, slaves will show strange behavior and issue seemingly unrelated error messages to their log files in short succession:
Of course, the names of machines, log files, and positions will vary, but the message of an assumed shutdown of the master, followed by immediate retries and failing again is a clear indication of a problem with replication server-ids.
