Description:
http://dev.mysql.com/doc/refman/5.0/en/mysqlbinlog.html
Documentation on --database filter option does not describe that this option works on current database only, just as replicate-do-db does in a server. Statements with fully qualified table names referring to tables outside of the current database will be filtered wrongly.
This was reported to me in a private talk on MySQL UC 2006.
How to repeat:
"Girish Prabhavalkar" <girishp@shreeconsult.com>
On Tuesday, 9. May 2006 22:09, Girish Prabhavalkar wrote:
> Hello Kristian,
> As per our discussion in MYSQL user conference (MYSQL for
> windows session) I am
> forwarding you a critical bug in MYSQL mysqlbinlog program.
>
> Distrib 5.0.18, for Win32 on ia32
> Server version 5.0.18-max-log
>
>
> The following command is suppossed to retrieve database changes
> from the binary log pertaining to database new_test into
> statements.sql text file.
>
> mysqlbinlog binlog.000043 --database=new_test > statements.sql
>
> For this to be always true, the changes to tables in database new_test has
> to be started with the USE NEW_TEST command.
>
> If you don't use the USE NEW_TEST command before the changes, let's say
> we do it this way instead....
>
> USE MYSQL;
> update new_test.test_table1 set desc1 = 'This is a test' where id=5;
>
> the above mysqlbinlog command would not capture this change, even though
> the change is for database new_test.
>
> ALso if you issue the following commands
>
> USE NEW_TEST;
> update menagerie.event set remark = 'a remark for Bowser' where name =
> 'Bowser';
>
> this change is captured in the above mysqlbinlog command, even though the
> change is not for new_test database.
>
> This could be very fatal if you were to recover just the database new_test
> using an old backup and apply the logs with mysqlbinlog utility.
> This could potentially corrupt databases after the recovery.
>
> Thanks,
> Girish Prabhavalkar
Suggested fix:
Hi, Girish!
Sorry to answer so late to your mail - I have been on the road the last few weeks and found little time to tend to my INBOX.
Looking at your description I can see what is going on. mysqlbinlog is acting just like "replicate-do-db" is on a slave. It is working on the current database, and does not look at the actual commands.
In replication we do have replicate-do-table and replicate-wild-do-table to repair this - these options will do what you request. But mysqlbinlog is missing these options and consequently this functionality.
I will file this as two bugs, a documentation oversight and a feature request.
The documentation oversight is that the manual is missing a more detailed explaination what --database does.
The feature request is to duplicate the functionality of the mysql server binlog filters in the mysqlbinlog command, including replicate-do-table and replicate-wild-do-table.
Kristian
