Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have binlog from MySQL and I need to output certain time frame into a seperate file, how would I do that?

here is sample of what binlog file contains:

# at 460
#130120  0:09:17 server id 1  end_log_pos 487   Xid = 79514636
COMMIT/*!*/;
# at 487
#130120  0:09:17 server id 1  end_log_pos 560   Query   thread_id=248447    exec_time=0 error_code=0

I'm looking to grep following:

#130120  0:09:17 server id 1  end_log_pos 487   Xid = 79514636
COMMIT/*!*/;
# at 487

I've tried pcregrep -M, but so far without any luck, my regex skills isn't where I thought they are, here is my actual line:

# mysqlbinlog /var/lib/mysql/log/logbin/mysql-bin.001036 | pcregrep -M '130120(\n|.*)\ at\ '
# 

* UPDATE *

  • number of lines between varies between different queries.

* UPDATE 2 *

this actually did the job...

# mysqlbinlog /var/lib/mysql/log/logbin/mysql-bin.001036 | sed -e '/130120 13/,/ at /!d' > /tmp/13
#
share|improve this question
    
What information do you wish to find - your example appears to show 2 entries at 09:17 what distinguishes the two ? –  Iain Jan 22 '13 at 17:09
    
I need to grep/grab all queries that were made from 11:30 13:30 and put that into a separate file, this is just an example of what binlog looks like, i don't want to post my actual data... –  alexus Jan 22 '13 at 17:19
    
Is the snippet from the log 9 minutes and 17 seconds past midnight ? What does the time look like at 13:30 ? –  Iain Jan 22 '13 at 17:26
1  
kinda sounds like a job for awk to me. –  Zoredache Jan 22 '13 at 17:34
    
@Iain yeah, it says #130120 13:30 –  alexus Jan 22 '13 at 17:37
show 1 more comment

4 Answers

up vote 0 down vote accepted

The real answer is to use the command line options to mysqlbinlog

mysqlbinlog --start-datetime=datetime --stop-datetime=datetime /path/to/mysql-bin.001036

share|improve this answer
add comment

This could have been done with awk very easily.

E.g

mysqlbinlog mysql-bin.001 | awk '($1 == "130120") {print $0}' > results.txt

This is tells awk to find anything in the first column that matches 130120 exactly and then print that entire line.

If you needed the minute and second exactly, you could then do something like the following:

awk '(($1 == "130120") && ($2 == "0:09:17")) {print $0}'

If you just needed the minute, you could use something like this:

awk '(($1 == "130120") && ($2 ~ "^0:09:")) {print $0}'
share|improve this answer
add comment

this did the job:

# mysqlbinlog /var/lib/mysql/log/logbin/mysql-bin.001036 | sed -e '/130120 13/,/ at /!d' > /tmp/13
#
share|improve this answer
add comment
grep -C 1 COMMIT filename

-C is context, 1 line before and 1 line after

-A is after

-B is before

share|improve this answer
    
please see my update. –  alexus Jan 22 '13 at 17:02
add comment

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.