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
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".
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
that do not modify data. To log all statements (for example, to identify a problem query), use the general query
log. See Section
5.2.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 crash-safe. 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 184.108.40.206, "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 220.127.116.11, "Binary Log Options and Variables".
To enable the binary log, start the server with the
--log-bin[= 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 C.5.8, "Known
Issues in MySQL", for the reason.
If you supply an extension in the log name (for example,
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
--log-bin-index[= option. You should not manually edit this
file while mysqld is running; doing so would confuse mysqld.
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.
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
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
master_verify_checksum system 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
slave_sql_verify_checksum system variable.
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
18.104.22.168, "Binary Logging Formats". For detailed information about the format of the binary log, see
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 22.214.171.124,
"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 with the
--log-slave-updates option in addition to the
--log-bin option (see Section
126.96.36.199, "Replication Slave Options and Variables"). This is done when a slave is also to act as a
master to other slaves in chained replication.
You can delete all binary log files with the
MASTER statement, or a subset of them with
PURGE BINARY LOGS. See Section 188.8.131.52, "
RESET Syntax", and Section
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
PURGE BINARY LOGS, which also safely updates the binary log index file for
you (and which can take a date argument). See Section 184.108.40.206,
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.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
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.
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
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.
The binary log format has some known limitations that can affect recovery from backups. See Section 16.4.1, "Replication Features and Issues".
Binary logging for stored programs is done as described in Section 18.7, "Binary Logging of Stored Programs".
Note that the binary log format differs in MySQL 5.7 from previous versions of MySQL, due to enhancements in replication. See Section 16.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
MyISAM tables. See Section C.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
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 before committing the 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
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
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 . 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: