I am trying figure out how to "query" binlogs to see what was exectued in the database for a specific date. For example, if I execute mysqlbinlog binlog then the thing just runs forever and it is had to see what exactly was executed there, especially if binlog is huge. Thank you!


You can give mysqlbinlog datetime stamps

Here is a sample datadir folder

[root@********* mysql]# ls -l /var/lib/mysql/mysql-bin.*
-rw-rw---- 1 mysql mysql 1073742028 Aug 13 03:29 /var/lib/mysql/mysql-bin.003111
-rw-rw---- 1 mysql mysql 1073741943 Aug 15 13:37 /var/lib/mysql/mysql-bin.003112
-rw-rw---- 1 mysql mysql  434864318 Aug 16 16:00 /var/lib/mysql/mysql-bin.003113
-rw-rw---- 1 mysql mysql         57 Aug 15 13:37 /var/lib/mysql/mysql-bin.index

Suppose you want to see everything executed from 10:45 AM to 2:25 PM on Aug 14th. All of Aug 14th is in /var/lib/mysql/mysql-bin.003112. Run the following:

mysqlbinlog --start-datetime="2012-08-14 10:45:00" --stop-datetime="2012-08-14 14:25:00" /var/lib/mysql/mysql-bin.003112 > SQL.txt

If your range was Aug 13th 5:00PM to Aug 14th 2:00AM, you must span two binary logs. So, you would run the following:

mysqlbinlog --start-datetime="2012-08-13 17:00:00" --stop-datetime="2012-08-14 02:00:00" /var/lib/mysql/mysql-bin.003111 /var/lib/mysql/mysql-bin.003112 > SQL.txt

Give it a Try !!!


quickest way I'd do it,

mysqlbinlog | grep 'YYYY-MM-DD HH:MM'

if you are setting the max binlog size to a high value or even not setting it at all (I think the default is 2 GB) then that is why mysqlbinlog takes a while to translate it and that is just how it's gonna be

another reason to make the bin log files huge and take a while to parse, is the replication mode you're using. Row based files are larger.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.