MySQL replication - some values are not being replicated

MySQL Replication issues

I might sound clue less here as i am :) , I am having problem with mysql replication, i.e. replication seems to be missing in some random occasion.

My structure looks like:

1) Master
2) slave (without SSL)
3) Slave (with SSL)

On some occasions, what goes wrong is that data are not replicated to slave.


First my question, was if i someone is updating slaves. And i took off all
privilege from all user other than read.
which itself contradicts because both slave are not replicated and user's do not have permision on both slave.

Ignored DB:

binlog-ignore-db                                        = mysql
binlog-ignore-db                                        = test
replicate-ignore-db                                     = mysql
replicate-ignore-db                                     = test

Manual Test:
I created new database, created table, new filed all was replicated to both slave. Updated works, altered works and delete works.

On some occasions when it misses out, randomly I am unable to troubleshoot or narrow down problem area.

Any suggestions?

FYI, I do not have skip-error in place.

Are you aware of http://www.mysqlperformanceblog.com/2009/05/14/why-mysqls-binlog-do-db-option-is-dangerous/ ?

Perhaps some of the missing updates are being run with the test database as the current database?

It is less error-prone if you use the replicate-wild-* options. In your case

replicate-wild-ignore-table = mysql.*
    replicate-wild-ignore-table = test.*

StackExchange Database Administrators Q#30897, answer score: 2