4.9.4 The Binary Log

The intention is that the binary log should replace the update log, so we recommend you to switch to this log format as soon as possible! The update log will be removed in MySQL 5.0.

The binary log contains all information that is available in the update log in a more efficient format. It also contains information about how long each query took that updated the database. It doesn't contain queries that don't modify any data. If you want to log all queries (for example to find a problem query) you should use the general query log. See section 4.9.2 The General Query Log.

The binary log is also used when you are replicating a slave from a master. See section 4.10 Replication in MySQL.

When started with the --log-bin[=file_name] option, mysqld writes a log file containing all SQL commands that update data. If no file name is given, it defaults to the name of the host machine followed by -bin. If file name is given, but it doesn't contain a path, the file is written in the data directory.

If you supply an extension to --log-bin=filename.extension, the extension will be silenty removed.

To the binary log filename mysqld will append an extension that is a number that is incremented each time you execute mysqladmin refresh, execute mysqladmin flush-logs, execute the FLUSH LOGS statement or restart the server. A new binary log will also automatically be created when the current one's size reaches max_binlog_size. Note if you are using transactions: a transaction is written in one chunk to the binary log, hence it is never split between several binary logs. Therefore, if you have big transactions, you may see binlogs bigger than max_binlog_size.

You can delete all binary log files with the RESET MASTER command (see section 4.5.4 RESET Syntax), or only some of them with PURGE MASTER LOGS (see section 4.10.7 SQL Commands Related to Replication).

You can use the following options to mysqld to affect what is logged to the binary log (please make sure to read the notes which follow this table):

The rules are evaluated in the following order, to decide if the query should be written to the binary log or not:

1. Are there binlog-do-db or binlog-ignore-db rules?
   • No: write the query to the binlog and exit.
   • Yes: go to step below.
2. So there are some rules (binlog-do-db or binlog-ignore-db or both). Is there a current database (has any database been selected by USE?)?
   • No: do NOT write the query, and exit.
   • Yes: go to step below.
3. There is a current database. Are there some binlog-do-db rules?
   • Yes: Does the current database match any of the binlog-do-db rules?
     • Yes: write the query and exit.
     • No: do NOT write the query, and exit.
   • No: go to step below.
4. There are some binlog-ignore-db rules. Does the current database match any of the binlog-ignore-db rules?
   • Yes: do not write the query, and exit.
   • No: write the query and exit.

So for example, a slave running with only binlog-do-db=sales will not write to the binlog any query whose current database is different from sales (in other words, binlog-do-db can sometimes mean ``ignore other databases'').

To be able to know which different binary log files have been used, mysqld will also create a binary log index file that contains the name of all used binary log files. By default this has the same name as the binary log file, with the extension '.index'. You can change the name of the binary log index file with the --log-bin-index=[filename] option. You should not manually edit this file while mysqld is running; doing this would confuse mysqld.

If you are using replication, you should not delete old binary log files until you are sure that no slave will ever need to use them. One way to do this is to do mysqladmin flush-logs once a day and then remove any logs that are more than 3 days old. You can remove them manually, or preferably using PURGE MASTER LOGS (see section 4.10.7 SQL Commands Related to Replication) which will also safely update the binary log index file for you (and which can take a date argument since MySQL 4.1)

A connexion with the SUPER privilege can disable the binary logging of its queries using SET SQL_LOG_BIN=0. See section 4.10.7 SQL Commands Related to Replication.

You can examine the binary log file with the mysqlbinlog utility. For example, you can update a MySQL server from the binary log as follows:

shell> mysqlbinlog log-file | mysql -h server_name

See section 4.8.4 mysqlbinlog, Executing the queries from a binary log for more information on the mysqlbinlog utility and how to use it.

If you are using BEGIN [WORK] or SET AUTOCOMMIT=0, you must use the MySQL binary log for backups instead of the old update log, which will be removed in MySQL 5.0.

The binary logging is done immediately after a query completes but before any locks are released or any commit is done. This ensures that the log will be logged in the execution order.

Updates to non-transactional tables are stored in the binary log immediately after execution. For transactional tables such as BDB or InnoDB tables, all updates (UPDATE, DELETE or INSERT) that change tables are cached until a COMMIT command is sent to the server. At this point mysqld writes the whole transaction to the binary log before the COMMIT is executed. Every thread will, on start, allocate a buffer of binlog_cache_size to buffer queries. If a query is bigger than this, the thread will open a temporary file to store the transaction. The temporary file will be deleted when the thread ends.

The max_binlog_cache_size (default 4G) can be used to restrict the total size used to cache a multi-query transaction. If a transaction is bigger than this it will fail and roll back.

If you are using the update or binary log, concurrent inserts will be converted to normal inserts when using CREATE ... SELECT or INSERT ... SELECT. This is to ensure that you can recreate an exact copy of your tables by applying the log on a backup.

User Comments

Add your own comment.