Binary Logging Formats

We already learned about what is binary log? Let’s have a look into types of binary logging

The server uses several logging formats to record information in the binary log. The exact format employed depends on the version of MySQL being used. There are three logging formats:

Binary Logging Formats1. Replication capabilities in MySQL originally were based on propagation of SQL statements from master to slave. This is called statement-based logging. You can cause this format to be used by starting the server with –binlog-format=STATEMENT.

2. In row-based logging, the master writes events to the binary log that indicate how individual table rows are affected. You can cause the server to use row-based logging by starting it with –binlog-format=ROW.

3. A third option is also available: mixed logging. With mixed logging, statement-based logging is used by default, but the logging mode switches automatically to row-based in certain cases. You can cause MySQL to use mixed logging explicitly by starting mysqld with the option –binlog-format=MIXED.

Note: Prior to MySQL 5.7.7, statement-based logging format was the default. In MySQL 5.7.7 and later, row-based logging format is the default.

Setting The Binary Log Format

You can select the binary logging format explicitly by starting the MySQL server with –binlog-format=type.

The logging format also can be switched at runtime. To specify the format globally for all clients, set the global value of the binlog_format system variable:

mysql> SET GLOBAL binlog_format = ‘STATEMENT’;
mysql> SET GLOBAL binlog_format = ‘ROW’;
mysql> SET GLOBAL binlog_format = ‘MIXED’;

Be careful changing the binary log format when using replication. The binary log format can only be set by the server for itself. Changing the binary log format on a master has no affect on the slave’s binary log format. This can cause replication to give inconsistent results or to fail.

Note: To change the global or session binlog_format value, you must have the SUPER privilege.

<<Click here to see all posts>>

If you found any of the information on this page helpful in anyway then please consider sharing this content with your favorite social network or by leaving your thoughts in the comment section. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.