Comparing MySQL Statement-Based and Row-Based Replication

[Chris]

There are many ways you can replicate MySQL data, whether it be a simple two-node cluster, chain or master/master. This article explains the two types of data replication (Statement-Based Replication and Row-Based Replication) available in MySQL.

There are many ways you can replicate MySQL data whether it be a simple two-node cluster, chain or master/master. If you use MySQL you probably know about replication and might have experimented with the replication layouts' listed above. In this article I will explain not the layout of replication and why or why not to use a specific layout but the types of replication you can use. In MySQL, you can use two types of replication, Statement-Based Replication (SBR) and Row-Based Replication (RBR).

In MySQL, replication is basically the slave server reading binary logs on the master server then running the statements or applying blocks to the slave server. Depending on the type of replication you are using, events are recorded in different formats to the binary log. Below are the different formats explained:

• At first, replication was based on propagation of SQL statements from the master server to the slave server. This replication format is called statement-based replication (SBR) and is default in older versions of MySQL (<= 5.1.4). Just as a note that in later versions of MySQL, especially with the Innodb Plugin, you NEED to run your transaction_isolation with REPEATABLE-READ.
• The newer replication type is row-based replication (RBR), which logs changes in individual table rows to the binary log. Basically, logging the actual change and not the SQL statement itself.
• MySQL also has the ability to change its binary logging format in real time depending on the type of event using the mixed format binary logging. When the mixed format is turned on, statement-based replication is on by default but will change to row-based replication is particular cases. For more information on Mixed Binary Logging please see Mixed Binary Logging Format in the MySQL Documentation.

 

Please click here for the full article.

[Chris]

Correct... The ordinal position of the column matters very much.  But try to convert a column on the slave to a different column type, lets say, from varchar(255) to text, and you'll start running into issues.

[E. Souhrada]

It's not true that the schemas must be the same between master and slave if you're using row-based replication. The slave schema can have additional columns not present in the master provided that those columns are at the end of the table definition.