MySQL replication - some values are not being replicated

Question

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.

Answer 1

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

Answer 2

I've seen something like that happen too. In my case, I have found how to consistently reproduce: data was not being replicated when "USE" was not run prior to calling UPDATE (maybe INSERT INTO too, haven't tried).

In other words, the issue would reproduce in the following sequence:

1. Start a new session with the MySQL server
2. UPDATE db.tbl.col = 666 WHERE db.tbl.id = 42;

but NOT in this sequence:

1. Start a new session with the MySQL server
2. USE db;
3. UPDATE db.tbl.col = 666 WHERE db.tbl.id = 42;

and in fact, not even in THIS sequence:

1. Start a new session with the MySQL server
2. USE arbitraryDB;
3. UPDATE db.tbl.col = 666 WHERE db.tbl.id = 42;

Note the use of fully qualified table names (i.e. we did use db.tbl.col, and not just tbl.col).

It seems to be solved by removing binlog-ignore-db and using only replicate-wild-ignore-table. The binlog-ignore-db in our configuration was used for ignoring a third, completely unrelated database (i.e. not db and not arbitraryDB). AFAIK there is no sensible reason for it to cause this bizarre phenomena, but removing it seems to solve it.

In our case, it was SQLyog that was causing these seemingly random issues. From time to time we manually run an UPDATE query using SQLyog. SQLyog itself only executes USE when a database is actually selected (either at the top or by clicking on a database or table on the left). However, even if we make sure USE is executed - it may still not be enough. If from some reason the session is lost (due to session timeout configuration, or internet connectivity problems) - the next time we run a query SQLyog will automatically re-establish a new session, but it will not re-execute USE, even if according to the GUI a database IS selected. I consider it an SQLyog bug; it might have been solved by now, but we saw it happen with a fairly new version.

I know that not using binlog-ignore-db was already recommended by another answer, but I decided to write this answer anyway because it shows that binlog-ignore-db can be problematic and cause MySQL bugs to manifest, regardless of how carefully and how well you write your queries.

Answer 3

When you setup a filtered replication, you should prefix all tables by database name. All transactions with table not prefixed will not replicate... It's not a bug but a choice of MySQL team... Strange not? :)