MySql Replication masterclass — Part 2

4 min readDec 31, 2022

Introduction

In the previous article we spoke about the concept of replication and introduced its types, synchronization variations and also talked about its advantages and drawbacks. In this article we will try to learn more about the specifics of Binlogs in MySQL and how logs are structured and stored. In the upcoming articles we will try to get into the details of how exactly the replication is done both in Binlog format as well as GTIDs.

Before we move ahead, we need to understand more about the binlogs and very importantly introduce the concept of replication formats.

Binlogs as described in the previous article as well are basically log files that contain database events that describe database changes such as table creations or any changes or updates to the data. Binlogs also contain events for the statements that could have potentially made changes(eg, a DELETE that matched no rows). The Binary logs are not used to log all the statements like SELECT or SHOW. To do so we can use the General Query Log. Performance takes a slight hit when Binary logging is enabled but the benefits in replication and data recovery outweighs this slight performance decrement. Binary logging is enabled by default (log_bin system variable is set to ON). If for some reason it’s disabled you can enable it in the configuration file and restart the server. You can check this variable as below:

show global variables like "log_bin";

Replication Formats

The Replication works because the events written in the source DB’s binary log are read from the source by the replicas and processed on the replica. These events are recorded in different formats according to the type of the event. With respect to MySql replication, the binary log format can be of three types that decides the main type of replication:

Statement Based Replication (SBR)
Row Based Replication (RBR)
Mixed Replication

You can check the replication format on MySql as below:

show global variables like "binlog_format";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+

You can also set the binlog format at runtime, although setting the logging format does not set the binary logging on the server. The setting only takes effect when the binary logging is enabled on the server(log_bin is set to ON).

SET GLOBAL binlog_format = ‘STATEMENT’; // For all clients that connect subsequent to the change*
SET SESSION binlog_format = ‘STATEMENT’; // *For an individual client*

Statement Based Replication(SBR)

This was the default logging format in older versions of MySql(≤5.1.4). When using SBR logging, the source writes the exact SQL statements to the binary log as executed by the source. Replication of the source to the replica works by sending the same statements to the replicas and executing these SQL statements on the replica. For example consider the below two DML statements.

For these queries, the SBR logs the statements as below:

Row Based Replication

This is now the default method in newer versions of MySql. When using Row Based Replication logging(RBR), the source writes events to the binlog that indicate how individual table rows are changed. The replication works by copying these events representing the changes to the table rows to the replica. Lets see an example below:

The binlog records these changes as given below:

We can see here the RBR has created three individual statements for one update statement along with the values for the rows. This always ensures that data remains consistent between source and the replicas.

Let’s take a look at the operational differences between SBR and RBR:

Mixed Replication Format

We can configure MySQL to use a mix of SBR and RBR. This is called Mixed-format logging. When using Mixed format logging, SBR is used by default. Depending on certain circumstances and also the storage engine being used the log is automatically switched to Row based logging in particular cases. When running MIXED logging format, the server automatically switches from Statement-based to Row-based logging under the following few conditions:

When a function contains UUID()
When a call to a loadable function is involved
When the LOAD_FILE() function is used
When a statement refers to one or more system variables.

Conclusion

As we have already gathered, all three types of logging have their unique characteristics, advantages and disadvantages. Many users still prefer statement based replication for their use cases. However, the Mixed replication format should be sufficient when it comes to performance and data integrity.

Check out the part 1 by clicking Here!

Next Part here!