Administering MySQL: International Usage and Log Files - 4.8.4 The Binary Log (
Page 4 of 5 )
The binary log has replaced the old update log, which is unavailable starting from MySQL 5.0. The binary log contains all information that is available in the update log in a more efficient format and in a manner that is transactionally safe.
The binary log, like the old update log, logs only statements that really update data. So an UPDATE or a DELETE with a WHERE that finds no rows is not written to the log. It even skips UPDATE statements that set a column to the value it already has.
The binary log also contains information about how long each statement took that updated the database. It doesn't contain statements that don't modify any data. If you want to log all statements (for example, to identify a problem query) you should use the general query log. See Section 4.8.2, "The General Query Log."
The primary purpose of the binary log is to be able to update the database during a restore operation as fully as possible, because the binary log will contain all updates done after a backup was made.
The binary log is also used on master replication servers as a record of the statements to be sent to slave servers. See Chapter 5, "Replication in MySQL."
Running the server with the binary log enabled makes performance about 1% slower. However, the benefits of the binary log for restore operations and in allowing you to set up replication generally outweigh this minor performance decrement.
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 value is given, the default name is the name of the host machine followed by -bin. If the file name is given, but it doesn't contain a path, the file is written in the data directory.
If you supply an extension in the log name (for example, --log-bin=file_name.extension), the extension is silently removed and ignored.
mysqld appends a numeric extension to the binary log name. The number is incremented each time you start the server or flush the logs. A new binary log also is created automatically when the current log's size reaches max_binlog_size. A binary log may become larger than max_binlog_size if you are using large transactions: A transaction is written to the binary log in one piece, never split between binary logs.
To be able to know which different binary log files have been used, mysqld also creates 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=[file_name] option. You should not manually edit this file while mysqld is running; doing so would confuse mysqld.
You can delete all binary log files with the RESET MASTER statement, or only some of them with PURGE MASTER LOGS.
You can use the following options to mysqld to affect what is logged to the binary log. See also the discussion that follows this option list.
-
--binlog-do-db=db_name
Tells the master that it should log updates to the binary log if the current database (that is, the one selected by USE) is db_name. All other databases that are not explicitly mentioned are ignored. If you use this, you should ensure that you only do updates in the current database.
An example of what does not work as you might expect: If the server is started with binlog-do-db=sales, and you do USE prices; UPDATE sales.january SET amount=amount+1000;, this statement will not be written into the binary log.
-
--binlog-ignore-db=db_name
Tells the master that updates where the current database (that is, the one selected by USE) is db_name should not be stored in the binary log. If you use this, you should ensure that you only do updates in the current database.
An example of what does not work as you might expect: If the server is started with binlog-ignore-db=sales, and you do USE prices; UPDATE sales.january SET amount=amount+1000;, this statement will be written into the binary log.
To log or ignore multiple databases, specify the appropriate option multiple times, once for each database.
The rules for logging or ignoring updates to the binary log are evaluated in the following order:
-
Are there binlog-do-db or binlog-ignore-db rules?
-
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?)?
-
There is a current database. Are there some binlog-do-db rules?
-
There are some binlog-ignore-db rules. Does the current database match any of the binlog-ignore-db rules?
For example, a slave running with only binlog-do-db=sales will not write to the binary log any statement whose current database is different from sales (in other words, binlog-do-db can sometimes mean "ignore other databases").
If you are using replication, you should not delete old binary log files until you are sure that no slave still needs 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 three days old. You can remove them manually, or preferably using PURGE MASTER LOGS, which will also safely update the binary log index file for you (and which can take a date argument since MySQL 4.1).
A client with the SUPER privilege can disable binary logging of its own statements by using a SET SQL_LOG_BIN=0 statement.
You can examine the binary log file with the mysqlbinlog utility. This can be useful when you want to reprocess statements in the log. For example, you can update a MySQL server from the binary log as follows:
shell> mysqlbinlog log-file | mysql -h server_name
See Section 7.5, "The mysqlbinlog Binary Log Utility," for more information on the mysqlbinlog utility and how to use it.
If you are using transactions, you must use the MySQL binary log for backups instead of the old update log.
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 statement is received by the server. At that point, mysqld writes the whole transaction to the binary log before the COMMIT is executed. When the thread that handles the transaction starts, it allocates a buffer of binlog_cache_size to buffer queries. If a statement is bigger than this, the thread opens a temporary file to store the transaction. The temporary file is deleted when the thread ends.
The max_binlog_cache_size (default 4GB) can be used to restrict the total size used to cache a multiple-statement transaction. If a transaction is larger than this, it will fail and roll back.
If you are using the update log 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 re-create an exact copy of your tables by applying the log on a backup.
The binary log format is different in versions 3.23, 4.0, and 5.0.0. Those format changes were required to implement enhancements to replication. MySQL 4.1 has the same binary log format as 4.0. See Section 5.5, "Replication Compatibility Between MySQL Versions."
