How Statement-Based Replication Works?

Statement-based replication is a form of database replication.

In this post, I’m going to explain how it works, its advantages, and its limitations.

Understanding Statement-Based Replication

Let’s say you are going for leader-based replication and there are multiple followers under a single leader node. 

With statement-based replication, two things will happen:

• The leader will log every write request or statement that it executes.
• It will also send that statement log to its followers.

What this means is that every INSERT, UPDATE & DELETE statement is forwarded to followers. Followers parse and execute the SQL statement as if they had received it directly from a client.

Here’s what it looks like in practice.

Advantages of Statement-Based Replication

Statement-based replication has quite a few advantages:

• It’s more efficient in terms of network bandwidth. That’s because only SQL statements are sent to the followers rather than entire data changes. 
• Generally more portable across different database versions. As long as the replicated SQL statements are compatible with the target database, you can use them with different versions and platforms without much effort.
• Simpler and easier to use.

Limitations of Statement-Based Replication

Despite their advantages, statement-based replication has largely fallen out of favor.

This is because of some key limitations:

• Any statement that calls a nondeterministic function (for example, NOW() & UUID()) to get the current date and time will generate a different value on each replica.
• If statements use an auto-incrementing column, they must be executed in exactly the same order on each replica or they may have a completely different effect. This means you can’t have multiple concurrently executing transactions.
• Statements having side effects such as triggers or stored procedures can result in completely different side effects on the replicas.

While it is possible to work around some of the issues such as replacing nondeterministic function calls with a fixed return value but due to several edge cases, statement-based replication has reduced in terms of adoption.

Conclusion

That’s all for this post.

Statement-based replication is one of the important ways of replication. In this post, we have covered how it works and its pros and cons.