Bug #3315 Binary log extract for given table.

• Submitted: 27 Mar 2004 17:06
• Modified: 29 Mar 2004 8:06
• Reporter: James Sleeman
• Status: Won't fix
• Category: MySQL Server
• Severity: S4 (Feature request)
• CPU Architecture: Any

[27 Mar 2004 17:06] James Sleeman

Description:
While you can extract statements executed to a given database from the binary log (mysqlbinlog -d), it is not possible to extract the statements just for a given table.  

This is quite a lacking feature, for example, suppose just one (large) table is corrupted due to human error.  Currently to restore using binary logs you must restore at least the entire database - you cannot simply extract the statements relevant to that table.

How to repeat:
Bork a large table.
Get forced to restore entire database from binary logs taking many many many hours.

Suggested fix:
Add option (and support to the binary log format if necessary) to mysqlbinlog so that you can do 

mysqlbinlog --database=foo --table=bar foobar.001

[29 Mar 2004 5:08] Guilhem Bichot

Filtering based on the table is much more complicated than on the database.
The -d option filters the database which was selected by the USE command when the statement was issued. It does not parse the statement to find which database was exactly used.
Filtering on the table would require parsing the statement, which is a complicated task which mysqlbinlog cannot do.
We are working on implementing a row-level format in the binary log (log the changed rows instead of the statement), then mysqlbinlog will be able to filter on the table (because no more parsing will be required, it will be just a simple string comparison, like for -d presently).
Until then, the only solution is:
mysqlbinlog your_binlogs > tmp_file, and edit tmp_file to keep only the lines which affect your table.

[29 Mar 2004 8:06] Guilhem Bichot

Here's another workaround:
at http://www.mysql.com/doc/en/CHANGE_MASTER_TO.html, starting from this line:
"The second example shows...". It explains how you can use replication commands to replay your binary logs for disaster recovery / rollforward, which is what you are looking for.
The good thing is that if during this your start your server with
--replicate-do-table=your_db.your_table
then only the binary log statements updating your_db.your_table will be executed (the slave code does the parsing so can filter like this). Which is exactly the kind of filtering you need.
You can even have wildcards with --replicate-wild-do-table, etc (see
http://www.mysql.com/doc/en/Replication_Options.html).
Hope this helps!