Learning MySQL

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