Friday, August 22, 2014

MySQL Binary Log

The MySQL Binary Log records completed transactions that have applied to a MySQL instance. The MySQL Binary log is a critical necessary component for point-in-time MySQL Data Recovery and for MySQL Replication.

The Binary log is configured with the following MySQL Configuration setting:

[mysqld]
log-bin[=[/path/to/]file]

As the name suggests this file contains binary information. The mysqlbinlog utility can be used to extract text based information from the Binary Log.

Prior to MySQL 5.1, all SQL statements were logged in STATEMENT format. MySQL 5.1 introduced the Binary Log Row Format --binlog-format to support additional MIXED and ROW formats.

The full list of MySQL Variables that relate to the MySQL Binary Log are:

  • log-bin
  • log-bin-index
  • sync_binlog (Since 5.0)
  • binlog-cache-size
  • binlog-format (Since 5.1)
  • binlog-do-db
  • binlog-ignore-db
  • max_binlog_size
  • max_binlog_cache_size
  • binlog_stmt_cache_size (Since 5.5)
  • max_binlog_stmt_cache_size (Since 5.5)
  • binlog_direct_non_transactional_updates (Since 5.1)
  • binlog-row-event-max-size (Since 5.1)
  • log-bin-trust-function-creators (Since 5.0)

References