Home > Databases > How to Read MySQL Binary Logs

How to Read MySQL Binary Logs

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

Advertisements
Categories: Databases Tags: ,
  1. gurl
    February 23, 2009 at 07:20

    hye..
    im new in linux +phpmyadmin
    i have already installed phpmyadmin in my pc fedora 7
    can anyone help me
    what is the command for me to write in the command line to start using phpmyadmin in gui?
    one more thing,how can i import file .pcap into mysql?
    can it be readable later on?

  2. pcabrerat
    October 7, 2009 at 14:17

    phpmyadmin uses a web interface. You have to open a web brower and point it to the path where you installed phpmyadmin. It might be something like “http://127.0.0.1:80/phpmyadmin”

  3. andrew
    March 26, 2010 at 10:35

    Can we transport localhost-bin.00000 to another PC and run the reading from there?

  4. neomancer
    March 26, 2010 at 13:21

    @andrew as long as you have the mysqlbinlog utility, yes.

  5. July 20, 2014 at 03:01

    Users can choose their systems depending upon the size of the property
    and requirements desired to them. Besides getting information on the positive and
    negative aspect of any device, reviews also offer information on the feature of the
    gadget. They could similarly be utilized to stop vehicle drivers from
    using your gateway or driveway as a way to turn their
    autos around.

  6. February 23, 2016 at 06:29

    Great post.

  7. June 2, 2017 at 00:00

    Your personal view on binary options trading is nicely planned out, I most certainly will share
    this with my readership.

  8. June 8, 2017 at 00:03

    I trust that it’s all right that I share this with a couple
    of my visitors, this will assist their familiarity with binary options trading greatly.

  9. June 15, 2017 at 00:10

    I expect that it’s alright that I write about this with many
    of my customers, it will help their familiarity with
    binary options trading considerably.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: