How to Read MySQL Binary Logs

MySQL database server utilizes a transaction log to track all of the modifications performed within the databases. This log ensures both that the database is able to recover when abruptly interrupted (such as a loss of power) and that users are able to undo (or “rollback” in database language) the results of a database transaction. Except for the cases where the administrator has disabled or commented out the log-bin parameter in my.cfg file, this log is generated by default. As the name implies, binary log (transaction log) files are written in binary format, but sometimes you may need to examine the content of the transaction logs in text format, where the mysqlbinlog utility will come in handy.

A binlog file normally is named hostname-bin.xxxxx and stored in /var/lib/mysql directory. It cannot be opened and read straight away as it’s a binary file so we can make use of mysqlbinlog command.

For example, to display the contents of the binary log file named localhost-bin.000004, use this command:

mysqlbinlog localhost-bin.000004

The output includes all events contained in localhost-bin.000004. Event information includes the statement executed, the time the statement took, the thread ID of the client that issued it, the timestamp when it was executed, and so forth.

The binlogs are likely to be very huge thus making it almost impossible to read anything on screen. However, you can pipe the output into a file which can be open up for later browsing in text editor:

mysqlbinlog localhost-bin.000004 > filename.txt

To reduce the amount of data retrieved from binary logs, there are several options that can be used to limit the data that is been returned. Among the useful ones are listed below:

–start-datetime=[DATETIME]

–stop-datetime=[DATETIME]

The datetime value is relative to the local time zone on the machine where you run mysqlbinlog. The value should be in a format accepted for the DATETIME or TIMESTAMP data types. For example:

mysqlbinlog --start-datetime="2008-05-22 00:00:00" --stop-datetime="2008-05-29 00:00:00" localhost-bin.000004

For more information, you can take a look at the official mysqlbinlog documentation.