Logging is about recording what happened in your databases. Just as some people might keep a personal journal to write down what happens in their daily lives, a database log keeps track of things like logins and transactions. More importantly, an effective log should include entries about access control and input validation failures. Is it any wonder then that the only MySQL log that is enabled by default is the error log (at least on Windows)?

Last week's blog provided an overview of the different log types on MySQL, highlighted the most important of these - namely, the error, general, binary, and slow logs - and covered the first two of these. Today we'll be taking a look at the binary log in more detail. That will leave the slow log for Part 3.

Statements Recorded by the Binary Log

The binary log stores events that describe database changes, for example, table creation operations or changes to table data via statements such as INSERT and UPDATE. Events for statements that potentially could have made changes, such as a DELETE which matched no rows, are also saved for posterity, except where row-based logging is used (see below for more on this). Hence, the binary log does not include statements such as SELECT or SHOW that do not modify data. These would be found in the general query log.

The binary log serves two important purposes:

• For replication, the binary log on a master replication server provides a record of the data changes to be sent to slave servers. In fact, the master server sends the events contained in its binary log to its slaves, so that they execute those same commands in order effectuate identical data changes as on the master.
• Certain data recovery operations make use of the binary log. After a backup has been restored, the events in the binary log pertaining to the backup are re-executed in order to synchronize databases to the point that the backup took place.

Despite these very significant uses, binary logging is disabled by default as it can degrade performance slightly. However, the benefits offered by the binary log in setting up replication and for restoring from a backup generally tend to outweigh this minor performance hit.

Binary Logging Formats

MySQL offers three logging formats for binary logging, each with its own pros and cons. Unlike other logs, you can't enable it using a simple ON/OFF switch. Instead, you have to select the binary logging format explicitly by starting the MySQL server with "--binlog-format=type". The exact statements for each type are described below:

• Statement-Based
  

  Statement-based logging logs all SQL statements that make changes to the data or structure of a table. Enable with --binlog-format=STATEMENT.

  Certain non-deterministic statements may not be safe for replication. If MySQL determines this to be the case, it will issue the warning "Statement may not be safe to log in statement format".

• Row-Based
  

  In row-based logging, the master writes events to the binary log that indicate how individual table rows are affected. For that reason, it is important that tables always include a primary key to ensure rows can be efficiently identified. You can tell the server to use row-based logging by starting it with --binlog-format=ROW.

• Mixed
  

  A third option is mixed logging. With this logging format, statement-based logging is used by default, but the logging mode switches automatically to row-based in certain cases. To use mixed logging, start MySQL with the option --binlog-format=MIXED.

  
  Fig.1 - the binlog_format server variable in the Navicat Server Monitor tool