Bug #55733 replication I/O slave is replicating statements that should be ignored
Submitted: 4 Aug 2010 8:47 Modified: 23 Feb 2011 15:41
Reporter: zohar aharoni Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S5 (Performance)
Version:5.1.37 OS:Linux (CentOS 5.3)
Assigned to: Assigned Account CPU Architecture:Any
Tags: relay-log, replication
Triage: Triaged: D5 (Feature request)

[4 Aug 2010 8:47] zohar aharoni
Description:
Hi,
It's not really a bug but more like a question:
I see in my environment that the I/O thread is pulling statements to relay-log even if they should be ignored by the SQL thread (in replicate-ignore-db).

Is there a way to configure the I/O thread not to pull the ignore-list?
I think this could ease on the replication (I/O wise...) and on traffic...

Thanks,
Zohar

How to repeat:
Create master/slave environment with replicate-ignore-db list

Suggested fix:
I/O thread will not write to the relay-log statement that will be ignored by the SQL thread anyway...
[4 Aug 2010 10:03] Valeriy Kravchuk
Do you use row-based or statement-based replication? Are you sure that http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_replic... does NOT explain the results you get?
[4 Aug 2010 12:28] zohar aharoni
Hi valeriy, thanks for the quick reply!

I'm using Mixed mode.

I understand the explanation. What I don't understand is if the filtering is done by the SQL thread or by the I/O thread (before even writing it to the relay-log).

When I'm analyzing the relay-log on the slaves using mysqlbinlog I see update statements for tables that should be ignored and in fact the SQL thread is not applying it but the I/O thread is writing it to the relay-log file...

I would expect that statements that should be ignored will not be in the slave relay-log in the first place...
[4 Aug 2010 12:28] Rene' Cannao'
The filtering is performed by the SQL_thread and not by the IO_thread .

I am not sure about the reason of this implementation.
Perhaps to not add parsing in the IO_thread?
[4 Aug 2010 14:13] Valeriy Kravchuk
So, as filtering is performed by SQL thread, this is a feature request for I/O thread to do it (optionally maybe?) and for documentation to have a note about current implementation.
[17 Sep 2010 9:00] Daogang Qu
Hi, zohar aharoni
I think --binlog-ignore-db option will meet your requirement. Please review:
http://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html#option_mysqld_b...

If so, Please close the bug. Thanks!

Currently relay-log is designed for guaranteeing log integrality.
So the slave I/O thread always replicates statements from master
binlog relay-log one by one.

If you very want the feature, we should create a WL to make dump thread
filter the ignored db from master side in order to reduce network
transmit load.
[26 Sep 2010 7:24] Zhenxing He
I think WL#2387 is what this bug report requested.
[26 Sep 2010 22:55] Luis Soares
Related: BUG#2917, BUG#21146, BUG#41267, WL#1049.
[23 Feb 2011 15:41] zohar aharoni
Thanks for the reply!

If I understand correctly, the option "--binlog-ignore-db" tells the server to not log relevant statement to binary-logs. This way the IO thread will not replicate those changes to the slave.

If what I wrote above is correct, than I will be able restore to the DB from backup in case of crisis but I will lose the changes in the databases that were mentioned in the --binlog-ignore-db option.

So - this is not an option for me.
The preferred option for me is if the I/O thread was aware to the replicate-ignore-* options in the slave and will not replicate the statements to the relay-logs on the slaves. This solution will be good for low-bandwidth environment and it will also reduce the IO on the slaves...