MySQL Bugs: #55733: replication I/O slave is replicating statements that should be ignored

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

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...

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?

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...

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?

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.

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.

I think WL#2387 is what this bug report requested.

Related: BUG#2917, BUG#21146, BUG#41267, WL#1049.

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...