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.