Greping multiple lines from MySql binlog

greploggingmysql-replicationpcre

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
#

Best Answer

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

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

Related Topic