Tell me more ×
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.
up vote 1 down vote favorite

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

2 Answers

up vote 1 down vote

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
up vote -1 down vote

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

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.