replicate-wild-ignore-table does not stop already logged queries on slave

Question

We have a master DB where where multiple queries where fired on a specific table, these queries were taking too long to execute on slave and it was lagging behind and as a result of which queries were queued and executing one by one.

Later we identified that this table was not required to be replicated and we added this table to replicate-wild-ignore-table on the slave's my.cnf and restarted the slave. But the slave again picked up the remaining queries for that table. Ideally it should have skipped the queries for that table. We are not able to figure out why this is happening.

Can somebody explain?

Answer 1

Without seeing the exact my.cnf entries on the slave, or knowing where the update came from (normal UPDATE command, or a stored procedure for example), or knowing what type of binary log format (row vs statement) you are using, all I can do is give you a link to how MySQL handles the replicate table options here.

A couple of things to note on this to explain why the above points are needed:

• If you are using statement based logging and the statements are executed from a stored procedure, the slave will run it because --replicate-table rules do not apply to stored objects.

• If the table name you are trying to ignore contains an underscore, you might need to escape it like "foo_bar". The same applies to the database name.