MySQL replication
Topics: MySQL
There are two main replication modes in MySQL, “statement-based replication” and “row-based replication”. “Mixed replication” dynamically switches between the two.
Statement-based replication simply transfers every statement that has been executed successfully on the leader node to its follower nodes where it is executed again. This has a huge downside in terms of data consistency because there are statements that are non-deterministic and will yield different results when executed repeatedly, e.g. INSERT INTO t VALUES(RANDOM()).
That’s why row-based replication transfers the actual results of each statement, thus ensuring identical data on leader and follower nodes.
Row-based replication is also more secure in the context of in-database code such as stored functions. Because the SQL thread on follower nodes runs with SUPER permissions, a stored function call transferred via statement-based replication might cause a privilege escalation. Even though this is prevented by row-based replication, MySQL always requires SUPER privileges for statements like CREATE FUNCTION. That’s presumably because a switch from row-based to statement-based replication can be executed at any time, and then stored functions not created/audited by a DBA could jeopardize database security.
Subtopics
- Purge MySQL binary logs
- MySQL replication problem - Impossible replication position
- MySQL replication problem - Corrupted relay log