Pre-General Availability Draft: 2017-10-20
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 18.2, “Replication Implementation”.
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 7.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”.
The binary log is not used for statements such as
SHOW that do not modify data. To
log all statements (for example, to identify a problem query), use
the general query log. See Section 5.4.3, “The General Query 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.
The binary log is resilient to unexpected halts. Only complete events or transactions are logged or read back.
Passwords in statements written to the binary log are rewritten by the server not to occur literally in plain text. See also Section 220.127.116.11, “Passwords and Logging”.
The following discussion describes some of the server options and variables that affect the operation of binary logging. For a complete list, see Section 18.104.22.168, “Binary Logging Options and Variables”.
Binary logging is enabled by default (the
log_bin system variable is set to
ON). The exception is if you use mysqld to
initialize the data directory manually by invoking it with the
--initialize-insecure option, when
binary logging is disabled by default, but can be enabled by
To disable binary logging, you can specify the
option at startup. If either of these options is specified and
--log-bin is also specified, the
option specified later takes precedence. If you disable binary
logging, you must also specify
is enabled by default and requires binary logging.
option is used to specify the base name for binary log files. The
default base name is the value of the
option (which by default is the name of host machine) followed by
-bin. The default base name is used if you omit
--log-bin option, or
if you specify the option with no string or with an empty string.
It is recommended that you specify a base name, so that if the
host name changes, you can easily continue to use the same binary
log file names (see Section B.5.7, “Known Issues in MySQL”). 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 base name 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
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 base name 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 default location for binary log files and the binary log index
file is the data directory. You can use the
--log-bin option to
specify an alternative location, by adding a leading absolute path
name to the base name to specify a different directory. When the
server reads an entry from the binary log index file, which tracks
the binary log files that have been used, it checks whether the
entry contains a relative path. If it does, the relative part of
the path is replaced with the absolute path set using the
--log-bin option. An
absolute path recorded in the binary log index file remains
unchanged; in such a case, the index file must be edited manually
to enable a new path or paths to be used. The binary log file base
name and any specified path are available as the
log_bin_basename system variable.
In MySQL 5.7, a server ID had to be specified when binary logging
was enabled, or the server would not start. In MySQL
system variable is set to 1 by default. The server can be started
with this default ID when binary logging is enabled, but a warning
message is issued if you do not specify a server ID explicitly
option. For servers that are used in a replication topology, you
must specify a unique non-zero server ID for each server.
By default, the server logs the length of the event as well as the
event itself and uses this to verify that the event was written
correctly. You can also cause the server to write checksums for
the events by setting the
binlog_checksum system variable.
When reading back from the binary log, the master uses the event
length by default, but can be made to use checksums if available
by enabling the
variable. The slave I/O thread also verifies events received from
the master. You can cause the slave SQL thread to use checksums if
available when reading from the relay log by enabling the
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 22.214.171.124, “Binary Logging Formats”. For detailed information about the format of the binary log, see MySQL Internals: The Binary Log.
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 126.96.36.199, “Evaluation of Database-Level Replication and Binary Logging Options”.
A replication slave server is started with the
--log-slave-updates setting enabled
by default, meaning that the slave writes to its own binary log
any data modifications that are received from the replication
master. The binary log must be enabled for this setting to work
(see Section 188.8.131.52, “Replication Slave Options and Variables”). This setting
enables the slave 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 184.108.40.206, “RESET Syntax”, and Section 220.127.116.11, “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). See
Section 18.104.22.168, “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:
shell> mysqlbinlog log_file | mysql -h server_name
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.8, “mysqlbinlog — Utility for Processing Binary Log Files”. For more information about the binary log and recovery operations, see Section 7.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”.
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.
Within an uncommitted transaction, all updates
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
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 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 binary log and row based logging, 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. If you are using statement-based logging, the
original statement is written to the log.
The binary log format has some known limitations that can affect recovery from backups. See Section 18.4.1, “Replication Features and Issues”.
Binary logging for stored programs is done as described in Section 24.7, “Binary Logging of Stored Programs”.
Note that the binary log format differs in MySQL 8.0 from previous versions of MySQL, due to enhancements in replication. See Section 18.4.2, “Replication Compatibility Between MySQL Versions”.
Writes to the binary log file and binary log index file are
handled in the same way as writes to
tables. See Section B.5.3.4, “How MySQL Handles a Full Disk”.
By default, the binary log is synchronized to disk at each write
sync_binlog was not enabled, and
the operating system or machine (not only the MySQL server)
crashed, there is a chance that the last statements of the binary
log could be lost. To prevent this, enable the
sync_binlog system variable to
synchronize the binary log to disk after every
N commit groups. See
Section 5.1.5, “Server System Variables”. The safest value for
sync_binlog is 1 (the default),
but this is also the slowest.
In earlier MySQL releases, there was a chance of inconsistency
between the table content and binary log content if a crash
occurred, even with
set to 1. For example, if you are using
tables and the MySQL server processes a
COMMIT statement, it writes many
prepared transactions to the binary log in sequence, synchronizes
the binary log, and then commits the transaction into
InnoDB. If the server crashed between those two
operations, the transaction would be rolled back by
InnoDB at restart but still exist in the binary
log. Such an issue was resolved in previous releases by enabling
InnoDB support for two-phase commit in XA
transactions. In 5.8.0 and higher, the
support for two-phase commit in XA transactions is always enabled.
InnoDB support for two-phase commit in XA
transactions ensures that the binary log and
InnoDB data files are synchronized. However,
the MySQL server should also be configured to synchronize the
binary log and the
InnoDB logs to disk before
committing the transaction. The
InnoDB logs are
synchronized by default, and
ensures the binary log is synchronized. The effect of implicit
InnoDB support for two-phase commit in XA
sync_binlog=1 is that at
restart after a crash, after doing a rollback of transactions, the
MySQL server scans the latest binary log file to collect
xid values and calculate
the last valid position in the binary log file. The MySQL server
InnoDB to complete any prepared
transactions that were successfully written to the to the binary
log, and truncates the binary log to the last valid position. This
ensures that the binary log reflects the exact data of
InnoDB tables, and therefore the slave remains
in synchrony with the master because it does not receive 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
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
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 expected size
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: