Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

2 Answers 2

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.

share|improve this answer

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 !!!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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