DEVELOPER ZONE ::
Login / Register
The binary log contains all statements that update data or potentially could have updated it (for example, a DELETE
which matched no rows). Statements are stored in the form of “events” that describe the modifications. The binary log also contains information about how long each statement took that updated data.
Note: The binary log has replaced the old update log, which is no longer available as of 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 transaction-safe. If you are using transactions, you must use the MySQL binary log for backups instead of the old update log.
The binary log is not used for statements such as SELECT
or SHOW
that do not modify data. If you want to log all statements (for example, to identify a problem query), use the general query log. See Section 5.11.2, “The General Query Log”.
The primary purpose of the binary log is to be able to update databases during a restore operation as fully as possible, because the binary log contains 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 6, Replication.
MySQL Enterprise. The binary log can also be used to track significant DLL events. Analyzing the binary log in this way is an integral part of the MySQL Network Monitoring and Advisory Service. For more information see http://www.mysql.com/products/enterprise/advisors.html.
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[=
option, mysqld writes a log file containing all SQL commands that update data. If no base_name
]base_name
value is given, the default name is the name of the host machine followed by -bin
. If the basename is given, but not as an absolute pathname, the server writes the file in the data directory. It is recommended that you specify a basename; see Section B.1.8.1, “Open Issues in MySQL”, for the reason.
If you supply an extension in the log name (for example, --log-bin=
), the extension is silently removed and ignored.base_name.extension
mysqld appends a numeric extension to the binary log basename. The number increases each time the server creates a new log file, thus creating an ordered series of files. The server creates a new binary log file each time it starts or flushes the logs. The server also creates a new binary log file automatically when the current log's size reaches max_binlog_size
. A binary log file may become larger than max_binlog_size
if you are using large transactions because a transaction is written to the file in one piece, never split between files.
To keep track of which binary log files have been used, mysqld also creates a binary log index file that contains the names of all used binary log files. By default this has the same basename 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[=
option. You should not manually edit this file while mysqld is running; doing so would confuse mysqld.file_name
]
Replication slave servers by default do not write to their own binary log any statements that are received from the replication master. To cause these statements to be logged, start the slave with the --log-slave-updates
option.
Writes to the binary log file and binary log index file are handled in the same way as writes to MyISAM
tables. See Section B.1.4.3, “How MySQL Handles a Full Disk”.
You can delete all binary log files with the RESET MASTER
statement, or a subset of them with PURGE MASTER LOGS
. See Section 13.5.5.5, “RESET
Syntax”, and Section 13.6.1.1, “PURGE MASTER LOGS
Syntax”.
The binary log format has some known limitations that can affect recovery from backups. See Section 6.7, “Replication Features and Known Problems”.
Binary logging for stored routines and triggers is done as described in Section 17.4, “Binary Logging of Stored Routines and Triggers”.
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.
If you are using replication, the options described here affect which statements are sent by a master server to its slaves. There are also options for slave servers that control which statements received from the master to execute or ignore. For details, see Section 6.8, “Replication Startup Options”.
Tell the server to restrict binary logging to updates for which the default database is db_name
(that is, the database selected by USE
). All other databases that are not explicitly mentioned are ignored. If you use this option, you should ensure that you do updates only in the default database.
There is an exception to this for CREATE DATABASE
, ALTER DATABASE
, and DROP DATABASE
statements. The server uses the database named in the statement (not the default database) to decide whether it should log the statement.
An example of what does not work as you might expect: If the server is started with binlog-do-db=sales
, and you run USE prices; UPDATE sales.january SET amount=amount+1000;
, this statement is not written into the binary log.
To log multiple databases, use multiple options, specifying the option once for each database.
Tell the server to suppress binary logging of updates for which the default database is db_name
(that is, the database selected by USE
). If you use this option, you should ensure that you do updates only in the default database.
As with the --binlog-do-db
option, there is an exception for the CREATE DATABASE
, ALTER DATABASE
, and DROP DATABASE
statements. The server uses the database named in the statement (not the default database) to decide whether it should log the statement.
An example of what does not work as you might expect: If the server is started with binlog-ignore-db=sales
, and you run USE prices; UPDATE sales.january SET amount=amount+1000;
, this statement is written into the binary log.
To ignore multiple databases, use multiple options, specifying the option once for each database.
The server evaluates the options for logging or ignoring updates to the binary log according to the following rules. As described previously, there is an exception for the CREATE DATABASE
, ALTER DATABASE
, and DROP DATABASE
statements. In those cases, the database being created, altered, or dropped replaces the default database in the following rules:
Are there --binlog-do-db
or --binlog-ignore-db
rules?
No: Write the statement to the binary log and exit.
Yes: Go to the next step.
There are some rules (--binlog-do-db
, --binlog-ignore-db
, or both). Is there a default database (has any database been selected by USE
?)?
No: Do not write the statement, and exit.
Yes: Go to the next step.
There is a default database. Are there some --binlog-do-db
rules?
Yes: Does the default database match any of the --binlog-do-db
rules?
Yes: Write the statement and exit.
No: Do not write the statement, and exit.
No: Go to the next step.
There are some --binlog-ignore-db
rules. Does the default database match any of the --binlog-ignore-db
rules?
Yes: Do not write the statement, and exit.
No: Write the query and exit.
For example, a slave running with only --binlog-do-db=sales
does not write to the binary log any statement for which the default 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. For example, if your slaves never run more than three days behind, once a day you can execute mysqladmin flush-logs on the master and then remove any logs that are more than three days old. You can remove the files manually, but it is preferable to use PURGE MASTER LOGS
, which also safely updates the binary log index file for you (and which can take a date argument). See Section 13.6.1.1, “PURGE MASTER LOGS
Syntax”.
A client that has the SUPER
privilege can disable binary logging of its own statements by using a SET SQL_LOG_BIN=0
statement. See Section 13.5.3, “SET
Syntax”.
You can display the contents of binary log files 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 8.11, “mysqlbinlog — Utility for Processing Binary Log Files”, for more information on the mysqlbinlog utility and how to use it. mysqlbinlog also can be used with relay log files because they are written using the same format as binary log files.
Binary logging is done immediately after a statement completes but before any locks are released or any commit is done. This ensures that the log is logged in execution order.
Updates to non-transactional tables are stored in the binary log immediately after execution. Within an uncommitted transaction, all updates (UPDATE
, DELETE
, or INSERT
) that change transactional tables such as BDB
or InnoDB
tables are cached until a COMMIT
statement is received by the server. At that point, mysqld writes the entire 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 statements. 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.
Modifications to non-transactional tables cannot be rolled back. If a transaction that is rolled back includes modifications to non-transactional tables, the entire transaction is logged with a ROLLBACK
statement at the end to ensure that the modifications to those tables are replicated.
The Binlog_cache_use
status variable shows the number of transactions that used this buffer (and possibly a temporary file) for storing statements. The Binlog_cache_disk_use
status variable shows how many of those transactions actually had to use a temporary file. These two variables can be used for tuning binlog_cache_size
to a large enough value that avoids the use of temporary files.
The max_binlog_cache_size
system variable (default 4GB, which is also the maximum) can be used to restrict the total size used to cache a multiple-statement transaction. If a transaction is larger than this many bytes, it fails and rolls back. The minimum value is 4096.
If you are using the binary log, concurrent inserts are converted to normal inserts for CREATE ... SELECT
or INSERT ... SELECT
statement. This is done to ensure that you can re-create an exact copy of your tables by applying the log during a backup operation.
Note that the binary log format is different in MySQL 5.0 from previous versions of MySQL, due to enhancements in replication. See Section 6.5, “Replication Compatibility Between MySQL Versions”.
By default, the binary log is not synchronized to disk at each write. So if the operating system or machine (not only the MySQL server) crashes, there is a chance that the last statements of the binary log are lost. To prevent this, you can make the binary log be synchronized to disk after every N
writes to the binary log, with the sync_binlog
system variable. See Section 5.2.3, “System Variables”. 1 is the safest value for sync_binlog
, but also the slowest. Even with sync_binlog
set to 1, there is still the chance of an inconsistency between the table content and binary log content in case of a crash. For example, if you are using InnoDB
tables and the MySQL server processes a COMMIT
statement, it writes the whole transaction to the binary log and then commits this transaction into InnoDB
. If the server crashes between those two operations, the transaction is rolled back by InnoDB
at restart but still exists in the binary log. This problem can be solved with the --innodb-safe-binlog
option, which adds consistency between the content of InnoDB
tables and the binary log. (Note: --innodb-safe-binlog
is unneeded as of MySQL 5.0; it was made obsolete by the introduction of XA transaction support.)
For this option to provide a greater degree of safety, the MySQL server should also be configured to synchronize the binary log and the InnoDB
logs to disk at every transaction. The InnoDB
logs are synchronized by default, and sync_binlog=1
can be used to synchronize the binary log. The effect of this option is that at restart after a crash, after doing a rollback of transactions, the MySQL server cuts rolled back InnoDB
transactions from the binary log. This ensures that the binary log reflects the exact data of InnoDB
tables, and so, that the slave remains in synchrony with the master (not receiving a statement which has been rolled back).
Note that --innodb-safe-binlog
can be used even if the MySQL server updates other storage engines than InnoDB
. Only statements and transactions that affect InnoDB
tables are subject to removal from the binary log at InnoDB
's crash recovery. If the MySQL server discovers at crash recovery that the binary log is shorter than it should have been, it lacks at least one successfully committed InnoDB
transaction. This should not happen if sync_binlog=1
and the disk/filesystem do an actual sync when they are requested to (some don't), so the server prints an error message The binary log
. In this case, this binary log is not correct and replication should be restarted from a fresh snapshot of the master's data.
User Comments
Add your own comment.