Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

what goes wrong is in some occasion, 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.

But in some occasion when it misses out randomly i am unable to troubleshoot or narrow down problem area.

Any suggestion?

FYI, dont have skip-error in place.

share|improve this question
    
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 '12 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 '12 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 '12 at 18:13
add comment

3 Answers

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.

share|improve this answer
add comment

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

share|improve this answer
    
Can you provide link to the docs that this is expected behaviour? –  ypercube Dec 24 '12 at 17:54
add comment

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.*
share|improve this answer
    
+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. –  Michael - sqlbot Dec 25 '12 at 5:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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