Bug #20205 mysqlbinlog command, extend filtering options
Submitted: 1 Jun 2006 13:47 Modified: 1 Jun 2006 15:43
Reporter: Kristian Koehntopp Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S4 (Feature request)
Version:5.0 OS:Any (any)
Assigned to: CPU Architecture:Any
Tags: Contribution
Triage: Triaged: D5 (Feature request)

[1 Jun 2006 13:47] Kristian Koehntopp
Description:
The mysqlbinlog command offers the --database option to filter the binlog. The filter duplicates the replicate-do-db filter functionality of a slave server.

Filter functionality mirroring replicate-do-table and replicate-wild-do-table is missing.

See also http://bugs.mysql.com/bug.php?id=20202 for a documentation bug related to this feature request.

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
[1 Jun 2006 15:43] Valeriy Kravchuk
Thank you for a reasonable feature request.