MySQL Replication server_id and server_uuid: Pitfalls, Causes of Data Loss, and Best‑Practice Recommendations

In a scenario where two MySQL instances use master‑slave replication, a master failure triggers a high‑availability switchover; after the new master comes online, rebuilding the old master from backup and re‑establishing replication can lead to data loss.

server_id configuration :

Default value is 1 or 0.

When a specific value is needed, set it via the server-id parameter.

Background :

When configuring MySQL replication, server_id is mandatory to distinguish each instance in the topology, preventing unnecessary duplicate data in circular or cascading replication.

The slave’s I/O thread skips binlog events whose server_id matches its own, reducing relay‑log writes and disk pressure.

In HA failover scenarios this mechanism can create hidden risks:

These risks arise because duplicate server_id values appear in non‑directly connected parts of the replication topology. In simple master‑slave setups, the slave stops replication and raises an error when it detects a duplicate server_id with its direct master.

Note: The behavior can be changed with the --replicate-same-server-id option.

Recommendations :

Assign a unique server_id to every MySQL instance when configuring replication.

After restoring an instance from backup, give it a new server_id .

Additional topic – server_uuid (MySQL 5.6+) :

When MySQL starts, it attempts to read a UUID from data_dir/auto.cnf . If reading fails, a new UUID is generated and written to that file.

Background :

In master‑slave replication, the master and slave must have different server_uuid values; otherwise initialization fails.

GTID uses server_uuid as a globally unique identifier.

Recommendation :

If you create a slave by directly copying the master’s data files, delete auto.cnf before restarting so MySQL generates a new server_uuid , preventing replication errors.

Reference : MySQL 8.0 Replication Options – Slave