The MySQL binary log is a critical component of database management, offering robust capabilities for recovery and replication. Knowing the advantages, disadvantages, and functionality is the first step in increasing availability and developing a recovery plan. In my previous blog, I discussed what they are, how to read them, and managing their size. That is a good place to start if this is a new topic for you.
Binlog Format Differences and Functionality
MySQL offers multiple binlog formats: statement-based, row-based, and mixed mode. The choice of format depends on the requirements of the application and the desired balance between performance and data integrity.
Statement-based Format
In this format, the binlog contains the SQL statements executed on the server. For example, if an INSERT statement is executed, the binlog will contain the exact SQL statement used. While this format is simple and efficient, it has limitations. For instance, non-deterministic functions or data manipulation involving user variables may produce different results on different MySQL servers, leading to inconsistencies during replication.
Row-based Format
In row-based format, the binlog contains the actual row changes made to the affected tables. Instead of recording the SQL statement, it logs the before and after values of the rows that were modified. This format provides a more accurate representation of the changes made, ensuring consistency during replication. However, it can generate larger binlog files due to the inclusion of row-level data.
Mixed Format
The mixed format combines the benefits of both statement-based and row-based formats. It switches between the two based on the type of SQL statement executed. For statements that are not safe for row-based logging, it falls back to the statement-based format. This format strikes a balance between efficiency and accuracy.
When choosing a format, you must also consider the advantages and disadvantages. Table 1 below outlines the advantages and disadvantages of statement-based and row-based formats. For more details on advantages and disadvantages, check out the MySQL Reference Manual.
|
Binlog Format |
Advantages |
Disadvantages |
|
Statement-based |
Proven technology. Less data written to log files. Log files contain all statements that made any changes, so they can be used to audit the database. |
Not all statements which modify data (such as INSERT DELETE, UPDATE, and REPLACE statements) can be replicated. Any nondeterministic behavior is difficult to replicate Deterministic loadable functions must be applied on the replicas. Statements using functions might not be replicated properly. For complex statements, the statement must be evaluated and executed on the replica before the rows are updated or inserted. As of MySQL 8.0.22, DML operations that read data from MySQL grant tables are not safe for replication. |
|
Row-based |
All changes can be replicated. Safest option for replication. Fewer row locks are required on the source, which thus achieves higher concurrency for INSERT . . . SELECT, INSERT, UPDATE, or DELETE statements. |
You cannot see on the replica what statements were received from the source and executed. Generates more data that must be logged. Deterministic loadable functions that generate large BLOB values take longer to replicate. For tables using the MyISAM storage engine, a stronger lock is required on the replica for INSERT statements. |
Changing the Binlog Format
As of MySQL 5.5, the default is row-based mode. Prior to that, the default is statement-based mode. The first step to changing the binlog format is to make sure that they are enabled.
The log_bin variable will have an ON, OFF, or absolute path as its value. This variable is not dynamic. It needs to be defined in the configuration file and requires a restart of the database. The following command will show if the binary logs are enabled.
mysql> SHOW VARIABLES LIKE 'log_bin';Next, use the following query to check the current binlog format. The default is ROW.
mysql> SHOW VARIABLES LIKE 'binlog_format';If you need to change the binlog format, one of the following queries can be used.
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';Conclusion
The MySQL binlog format is a critical component of database management, offering robust capabilities for recovery and replication. Whether you are recovering from a system failure or building a highly available database infrastructure, understanding the binlog format and its nuances is essential. By selecting the appropriate binlog format and leveraging its features effectively, you can ensure the reliability, scalability, and integrity of your MySQL database system.
For more information, please contact us!