5.2.4 The Binary Log

The binary log contains “events” that describe
database changes such as table creation operations or changes to
table data. It also contains events for statements that
potentially could have made changes (for example, a
DELETE which matched no rows),
unless row-based logging is used. The binary log also contains
information about how long each statement took that updated data.
The binary log has two important purposes:

For replication, the binary log on a master replication server
provides a record of the data changes 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 16.2, “Replication Implementation”.

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.

To enable the binary log, start the server with the
--log-bin[=base_name]
option. If no base_name value is given,
the default name is the value of the pid-file
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
explicitly rather than using the default of the host name; see
Section B.5.8, “Known Issues in MySQL”, for the reason.

Note

From MySQL 5.1.18 through 5.1.22, “mysql” was used
when no base_name was specified. Also
in these versions, a path given as part of the
--log-bin options was treated as
absolute rather than relative. The previous behaviors were
restored in MySQL 5.1.23. (See Bug #28603 and Bug #28597.)

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
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.

The format of the events recorded in the binary log is dependent
on the binary logging format. Three format types are supported,
row-based logging, statement-based logging and mixed-base logging.
The binary logging format used depends on the MySQL version. For
general descriptions of the logging formats, see
Section 5.2.4.1, “Binary Logging Formats”. For detailed information
about the format of the binary log, see
MySQL Internals:
The Binary Log.

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 PURGE BINARY
LOGS, which also safely updates the binary log index
file for you (and which can take a date argument). See
Section 13.4.1.1, “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:

Binary logging is done immediately after a statement or
transaction completes but before any locks are released or any
commit is done. This ensures that the log is logged in commit
order.

Updates to nontransactional tables are stored in the binary log
immediately after execution. In MySQL 5.1.22 and earlier versions
of MySQL 5.1, an
UPDATE statement using a stored
function that modified a nontransactional table was not logged if
it failed, and an
INSERT ... ON
DUPLICATE KEY UPDATE statement that encountered a
duplicate key constraint—but did not actually change any
data—was not logged. Beginning with MySQL 5.1.23, both of
these statements are written to the binary log. (Bug #23333)

Within an uncommitted transaction, all updates
(UPDATE,
DELETE, or
INSERT) that change transactional
tables such as 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.

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
ROLLBACK
statement at the end to ensure that the modifications to those
tables are replicated.

When a 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.

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 and row based logging, concurrent
inserts are converted to normal inserts for CREATE ...
SELECT or
INSERT ...
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. If you are using statement-based logging, the
original statement is written to the log.

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.1.4, “Server 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. To resolve this, you should set
--innodb_support_xa to 1. Although
this option is related to the support of XA transactions in
InnoDB, it also ensures that the binary log and InnoDB data files
are synchronized.

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).

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/file system do an actual sync when they are requested to
(some do not), so the server prints an error message The
binary log file_name is shorter than
its expected size. In this case, this binary log is not
correct and replication should be restarted from a fresh snapshot
of the master's data.

For MySQL 5.1.20 and later (and MySQL 5.0.46 and later for
backward compatibility), the session values of the following
system variables are written to the binary log and honored by the
replication slave when parsing the binary log: