The binary log contains “events” that describe
database changes such as table creation operations or changes to
table data. As of MySQL 4.1.3, it also contains events for
statements that potentially could have made changes (for example,
DELETE which matched no rows).
The binary log also contains information about how long each
statement took that updated data. The binary log has two important
For replication, the binary log is used on master replication servers as a record of the statements to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master. See Section 14.2, “Replication Implementation Overview”.
Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup. See Section 6.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”.
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.
Running a server with binary logging enabled makes performance slightly slower. However, the benefits of the binary log in enabling you to set up replication and for restore operations generally outweigh this minor performance decrement.
For information about server options and variables affecting the operation of binary logging, see Section 14.8.4, “Binary Log Options and Variables”.
The binary log is not used for statements such as
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.3.2, “The General Query Log”.
The binary log should be protected because logged statements might contain passwords. See Section 18.104.22.168, “Administrator Guidelines for Password Security”.
For detailed information about the format of the binary log, see MySQL Internals: The Binary Log.
To enable the binary log, start the server with the
option. If no
base_name value is given,
the default name is the value of the
option (which by default is the name of host machine) followed by
-bin. If the basename is given, the server
writes the file in the data directory unless the basename is given
with a leading absolute path name to specify a different
directory. It is recommended that you specify a basename; see
Section B.5.8.4, “Open Issues in MySQL”, for the reason.
If you supply an extension in the log name (for example,
the extension is silently removed and ignored.
mysqld appends a numeric extension to the
binary log basename to generate binary log file names. The number
increases each time the server creates a new log file, thus
creating an ordered series of files. The server creates a new file
in the series each time it starts or flushes the logs. The server
also creates a new binary log file automatically after 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
'.index'. You can change the name of
the binary log index file with the
option. You should not manually edit this file while
mysqld is running; doing so would confuse
The term “binary log file” generally denotes an individual numbered file containing database events. The term “binary log” collectively denotes the set of numbered binary log files plus the index file.
The server evaluates the
--binlog-ignore-db options in the
same way as it does the
--replicate-ignore-db options. For
information about how this is done, see
Section 14.9.1, “Evaluation of Database-Level Replication and Binary Logging Options”.
A replication slave server by default does not write to its own
binary log any data modifications that are received from the
replication master. To log these modifications, start the slave
in addition to the
(see Section 14.8.3, “Replication Slave Options and Variables”). This is done
when a slave is also to act as a master to other slaves in chained
You can delete all binary log files with the
RESET MASTER statement, or a subset
of them with
PURGE BINARY LOGS. See
Section 22.214.171.124, “RESET Syntax”, and Section 126.96.36.199, “PURGE BINARY LOGS Syntax”.
If you are using replication, you should not delete old binary log
files on the master 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
LOGS, which also safely updates the binary log index
file for you (and which can take a date argument as of MySQL 4.1).
See Section 188.8.131.52, “PURGE BINARY LOGS 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 a recovery operation. For example, you can update a MySQL server from the binary log as follows:
log_file| mysql -h
mysqlbinlog also can be used to display replication slave relay log file contents because they are written using the same format as binary log files. For more information on the mysqlbinlog utility and how to use it, see Section 4.6.6, “mysqlbinlog — Utility for Processing Binary Log Files”. For more information about the binary log and recovery operations, see Section 6.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”.
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 nontransactional tables are stored in the binary log
immediately after execution. Within an uncommitted transaction,
all updates (
INSERT) that change transactional
tables such as
tables are cached until a
statement is received by the server. At that point,
mysqld writes the entire transaction to the
binary log before the
Modifications to nontransactional tables cannot be rolled back. If
a transaction that is rolled back includes modifications to
nontransactional tables, the entire transaction is logged with a
statement at the end to ensure that the modifications to those
tables are replicated. This is true as of MySQL 4.0.15.
When a thread that handles the transaction starts, it allocates a
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.
variable shows the number of transactions that used this buffer
(and possibly a temporary file) for storing statements. The
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.
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 update log or binary log, concurrent inserts
are converted to normal inserts for
SELECT statements. This is done to ensure that you can
re-create an exact copy of your tables by applying the log during
a backup operation.
The binary log format has some known limitations that can affect
recovery from backups, especially in old versions. These caveats,
which also affect replication, are listed at
Section 14.7, “Replication Features and Issues”. One caveat which does not
affect replication but only recovery with
mysqlbinlog: before MySQL 4.1,
mysqlbinlog could not prepare output suitable
for mysql if the binary log contained
interlaced statements originating from different clients that used
temporary tables of the same name. This is fixed in MySQL 4.1.
However, the problem still existed for
INFILE statements until it was fixed in MySQL 4.1.8.
The binary log format differs between versions 3.23 and 4.0. (These format changes were required to implement enhancements to replication.) However, MySQL 4.1 has the same binary log format as 4.0. See Section 14.5, “Replication Compatibility Between MySQL Versions”.
Before MySQL 4.1.9, writes to a binary log file or binary log
index file that failed due to a full disk or an exceeded quota
resulted in corruption of the file. Starting from MySQL 4.1.9,
writes to the binary log file and binary log index file are
handled the same way as writes to
tables. See Section B.5.4.3, “How MySQL Handles a Full Disk”.
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
writes to the binary log, with the
sync_binlog system variable. See
Section 5.1.3, “Server System Variables”. 1 is the safest value
sync_binlog, but also the
slowest. Even with
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
statement, it writes the whole transaction to the binary log and
then commits this transaction into
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
(available starting from MySQL 4.1.3), which adds consistency
between the content of
InnoDB tables and the
For this option to provide a greater degree of safety, the MySQL
server should also be configured to synchronize the binary log and
InnoDB logs to disk at every transaction.
InnoDB logs are synchronized by default,
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
InnoDB tables, and so, that the slave
remains in synchrony with the master (not receiving a statement
which has been rolled back).
be used even if the MySQL server updates other storage engines
InnoDB. Only statements and transactions
InnoDB tables are subject to
removal from the binary log at
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
This should not happen if
sync_binlog=1 and the
disk/file system do an actual sync when they are requested to
(some do not), so the server prints an error message
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.
file_name is shorter than its