Bug #20202 mysqlbinlog command, --database option

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

http://bugs.mysql.com/bug.php?id=20205 is the related feature request against the mysqlbinlog command.

Thank you for a reasonable documentation request.

Updated in the client documentation:

List entries for just this database (local log only). You can only specify one database with this option - if you specify multiple <option>--database</option> options, only the last one is used. This option forces <command>mysqlbinlog</command> to output entries from the binary log where the default database (that is, the one selected by <literal>USE</literal>) is <replaceable>db_name</replaceable>. Note that this does not replicate cross-database statements such as <literal>UPDATE <replaceable>some_db.some_table</replaceable> SET  foo='bar'</literal> while having selected a different database or no database.