Causes of MySQL Replication Data Drift

In MySQL replication setups, ensuring data consistency between the primary and secondary (replica) servers is critical for disaster recovery, load balancing, and data integrity. However, data drift—where the secondary server’s data silently diverges from the primary—remains a common challenge for database administrators. This article explores the primary causes of this divergence, including replication formats, server configurations, direct replica writes, and error-handling policies, to help you identify and prevent consistency issues.

1. Statement-Based Replication (SBR) and Non-Deterministic Queries

When MySQL is configured to use Statement-Based Replication (binlog_format=STATEMENT), the primary server writes the literal SQL statements to the binary log, which the secondary server then re-executes. This becomes a major source of data drift when queries are non-deterministic.

• Functions: Functions like UUID(), RAND(), NOW(), and SYSDATE() yield different results when executed at different times or on different servers.
• Unordered Limits: A query utilizing LIMIT without an explicit ORDER BY clause can update or delete different rows on the secondary server because the physical storage layout or index selection might differ.

Switching to Row-Based Replication (RBR) or Mixed-mode replication solves this issue by replicating the actual raw data changes rather than the SQL statements.

2. Direct Writes to the Secondary Server

A secondary server should ideally be used strictly for read operations. If the replica is not configured as read-only, users or application processes can accidentally write, update, or delete data directly on the secondary.

Because replication is one-way (from primary to secondary), these direct modifications on the replica will not be sent back to the primary, leading to immediate data drift. To prevent this, secondary servers should always be configured with: * read_only = ON * super_read_only = ON (to prevent even users with administrative privileges from making accidental changes)

3. Ignored Replication Errors

When the replication SQL thread encounters an error on the secondary server (such as a duplicate key or a missing row during an update), replication normally halts to prevent further drift.

However, if the system is configured to bypass these errors using the slave_skip_errors (or replica_skip_errors in newer versions) configuration directive, the secondary will ignore the failed transaction and continue executing subsequent statements. This keeps the replication pipeline running but leaves the database in an inconsistent state.

4. Differences in Server Configurations and Environments

Discrepancies in the underlying configuration of the primary and secondary servers can cause identical queries to yield different results. Common environmental culprits include:

• Timezones: If the primary and secondary servers are set to different system timezones, date and time calculations inside queries may produce different values.
• Character Sets and Collations: Differences in default character sets can cause string comparisons or sorting to behave differently, leading to mismatched index lookups or data truncation.
• SQL Modes: A strict SQL mode on the primary but a permissive mode on the secondary can result in the secondary accepting truncated data or invalid dates that the primary would have rejected (or vice versa).

5. Triggers and Stored Procedures

If triggers or stored procedures are defined differently on the primary and secondary servers, they can cause silent data drift. For example, a trigger on the primary that logs actions to an audit table might not exist on the secondary, or it might perform different logic.

In Statement-Based Replication, triggers run independently on both servers. If the data state of the secondary is already slightly different, the trigger might execute on a different set of rows, compounding the drift.

6. Non-Transactional Storage Engines

Using non-transactional storage engines like MyISAM can easily lead to data drift. Unlike InnoDB, MyISAM does not support crash-safe replication. If a secondary server crashes mid-transaction, MyISAM tables cannot roll back to a consistent state, resulting in partially applied transactions and permanent data divergence from the primary.