MySQL – Partially restore binary log

dumplogsMySQLreplication

I have restored a dump into MySQL. I also have a binary log, which I want to partially restore, let's say from position 10 – 2500.

How can I do that?

Best Answer

Use the mysqlbinlog utility

mysqlbinlog --help gives the following:

-j, --start-position=#
                  Start reading the binlog at position N. Applies to the
                  first binlog passed on the command line.
--stop-position=#   Stop reading the binlog at position N. Applies to the
                  last binlog passed on the command line.

Suppose your binary log is called mysql-bin.012345. You would do the following for positions 10 - 2500 to attach it to a mysqldump called MySQLData.sql:

Either

mysqlbinlog --start-position=10 --stop-position=2500 mysql-bin.012345 > SQLFromBinLog.sql
cat SQLFromBinLog.sql >> MySQLData.sql

or append directly with

mysqlbinlog --start-position=10 --stop-position=2500 mysql-bin.012345 >> MySQLData.sql

Give it a Try !!!