Managing & Using MySQL, 2nd Edition

The Binary Log

The binary log contains all SQL commands that update data. MySQL logs only statements that actually change data. For example, a delete that fails to affect any rows is not logged. Updates that set column values to their current values are also not logged. MySQL logs updates in execution order.

The binary log is useful for journaling all update operations since the last backup. For example, if you back up your database once a day, and your database crashes in the middle of the day, you can restore the database up to the last completed transaction by:

1. Restoring the database (see Section 4.4 and Section 4.5 in this chapter for more information on this task)

2. Applying the transactions from all binary logs since the last backup

Enable the binary log through the --log-bin=file option. If you specify no filename, MySQL uses the file hostname-bin. If you specify a relative path, the path is assumed to be relative to the data directory. MySQL appends a numeric index to the filename so that the file ends up being filename.number; e.g., hostname-bin.2. MySQL uses the index for rotating the files and rotates to the next index under the following conditions:

• The server is restarted.

• The server is refreshed.

• The log reaches the maximum log size.

• The logs are flushed.

MySQL also creates an index file that contains a list of all used binary log files. By default, this file is called hostname-bin.index. You may change the name and/or location of the index with the --log-bin-index=file ...