The Binary Log
An update log contains all the information needed to
re-create any changes to the database since the server was started or
the logs were flushed; this feature allows you to always
have an up-to-date backup of your database. You can keep a list of
every SQL query that changes data on the server by passing the
log-bin
option to the MySQL server
(mysqld_safe
, mysqld-nt.exe
, or mysqld
).
If no preferred name and directory is specified for the logfile,
the server will use the file <hostname>-bin
in the MySQL data directory. Individual logfiles will have the
extensions .000001, .000002, and so on; any extensions you
specify to the log-bin
option are
ignored. For example, on a machine with the hostname eden
, the binary logfiles are typically
named eden-bin.000001, eden-bin.000002, and so on. It’s also
common to see the word mysql used in place of the
hostname. The update log is saved in a compact binary format; prior to
MySQL version 5.0, the log-update
option would save an update log in text format. However, the text
format is deprecated and is treated the same as log-bin
in MySQL 5.0 and later.
When the server is shut down, it ensures that all modifications to data have been written (flushed) to the binary log. The next time the server is started, it opens a new logfile alongside the old one with an incremented number in the extension. For example, the current binary logfile might be called eden-bin.000012; after the server is restarted, it creates the new logfile eden-bin.000013 ...
Get Learning MySQL now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.