greping multiple lines from MySql binlog

Question

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
#

What information do you wish to find - your example appears to show 2 entries at 09:17 what distinguishes the two ? — user9517, 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 ? — user9517, Jan 22 '13 at 17:26

tacotuesday · Answer 1 · 2013-01-23T01:43:44.607

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}'

score 0 · Answer 2 · answered Jan 22 '13 at 17:39

0

this did the job:

# mysqlbinlog /var/lib/mysql/log/logbin/mysql-bin.001036 | sed -e '/130120 13/,/ at /!d' > /tmp/13
#

answered Jan 22 '13 at 17:39

alexus

12,342
27
115
173

score 0 · Accepted Answer · answered Jan 23 '13 at 02:00

0

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

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

answered Jan 23 '13 at 02:00

R. S.

1,624
12
19

score -1 · Answer 4 · answered Jan 22 '13 at 16:53

-1

grep -C 1 COMMIT filename

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

-A is after

-B is before

answered Jan 22 '13 at 16:53

jftuga

5,572
4
39
50

please see my update. – alexus Jan 22 '13 at 17:02

greping multiple lines from MySql binlog

4 Answers4