[Some information in this section is derived from Chapter 20, The Binary Log, in the MySQL Reference Manual.]
The binary log is a set of log files that contain information
about data modifications made to a MySQL server instance. The log
is enabled by starting the server with the
The binary log was introduced in MySQL 3.23.14. It contains all
statements that update data. It also contains statements that
potentially could have updated it (for example, a
DELETE which matched no rows), unless row-based
logging is used. 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.
The binary log also contains some other metadata, including:
Information about the state of the server that is needed to reproduce statements correctly
Metadata needed for the maintenance of the binary llog itself (for example, rotate events)
The binary log is a trace of changes of the server's global state generated during its operation. The events that it contains describe changes of this state. More precisely, binary log events describe actions that can be used to reproduce the same changes of global state which have happened on server.
The binary log has two important purposes:
For replication, the binary log is used on master replication servers as a record of the statements to be sent to slave servers. Many details of binary log format and handling are specific to this purpose. 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. A slave stores events received from the master in its relay log until they can be executed. The relay log has the same format as the binary log.
Certain data recovery operations require use of the binary log. After a backup file 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.
There are two types of binary logging:
Statement-based logging: Events contain SQL statements that produce data changes (inserts, updates, deletes)
Row-based logging: Events describe changes to individual rows
Mixed logging uses statement-based logging by default but switches to row-based logging automatically as necessary.
Row-based (and mixed) logging is available beginning with MySQL 5.1.
For more details about row-based logging, see Row-Based Binary Logging.
mysqlbinlog utility can be used to print
binary or relay log contents in readable form.