Backups and Recovery - Learning MySQL - page 365

The Binary Log
An update log contains all the information needed to re-create any changes to the da-
tabase 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 exten-
sions .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 to log all modifications to the database since
the restart. The logs can be manually flushed at any time using the FLUSH LOGS command
in the monitor, or the mysqladmin flush-logs command from the command line.
You can view the SQL statements in the binary log by using the msqlbinlog command
and specifying the full path to the binary logfile. For example, if on this system the
MySQL data directory is /usr/lib/mysql/data , you can view the contents of the binary
logfile eden-bin.000002 by typing:
# mysqlbinlog /usr/lib/mysql/data/eden-bin.000002
You'll need to have the necessary permissions to access the MySQL data directory and
to read the binary logfile on your host system. You might see something like this when
you open a logfile:
...
use music;
SET TIMESTAMP=1151221361;
SET @@session.foreign_key_checks=0, @@session.unique_checks=0;
SET @@session.sql_mode=524288;
/*!\C utf8 */;
SET @@session.character_set_client=33,@@session.collation_connection=33,
@@session.collation_server=8;
DROP TABLE IF EXISTS `artist`;
# at 30551
#060625 17:42:41 server id 1 end_log_pos 30794 Query thread_id=168
exec_time=0 error_code=0
SET TIMESTAMP=1151221361;