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.

  • does slave 3 (the one with SSL) replicate from the master or from slave 2 ?Can you estimate when (as in date and time) the statement modifying the database is not replicated?
    – redguy
    Dec 21, 2012 at 15:54
  • Slaves can be modified. There is a read_only system variable, but this won't prevent users with SUPER privilege from modifying the database.
    – redguy
    Dec 21, 2012 at 16:01
  • Hey redguy, both replicates from master, dont need read_only as its already readonly for everyone, i cannot think of where to look for problems.
    – tike
    Dec 22, 2012 at 18:13

3 Answers 3


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.*
  • +1 for a very good link to a widely misunderstood topic... and for offering the far less error-prone alternative. Even better, of course, is just to let MySQL do what it does best: replicate everything, set the replicas read-only, don't use your SUPER credentials when you don't have a reason to use them. Dec 25, 2012 at 5:12

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.


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? :)

  • Can you provide link to the docs that this is expected behaviour? Dec 24, 2012 at 17:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.