Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I've set up a MySQL replication scheme between 2 remote databases. I've realized that if I change data on the slave DB, the changed data does not get updated again.

For example, I have a table User and I create a new user Joe on the Master DB. Joe is replicated on the slave DB. But if I delete Joe on Slave DB later, the Joe on the Master is not copied back to the slave again.

Why is this happening? Is there a way to stop this?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

This happens because MySQL replication doesn't work the way you think it does. All it does is take the queries that changed data on the master and re-run them on the slave. There is no ongoing reconciliation of changes.

The upshot of this is that you must treat slaves as read-only, and never modify the data on them. Whatever you want to achieve by modifying the slave data, you'll have to come up with a different way of achieving it.

share|improve this answer
    
Ah I see. So going along with this knowledge, if I set up a master-slave replication without first copying the master DB's mysqldump to the slave and just started the replication it would break the slave because it tries to execute a query on a non-existent table on the slave, right? –  samxli Jul 29 '11 at 14:27
1  
A good idea is to make your slave readonly read-only = 1 in my.cnf. If you want to make sure your data is syncronized between the master and slave, Maatkit has a tool for this called mk-table-sync. –  sreimer Jul 29 '11 at 15:04
1  
@samxli: Definitely. –  womble Jul 30 '11 at 0:23
    
@sreimer: if I set the salve as readonly wouldn't that make the replication fail? And I'm going to be using the mysql instance for different DBs as well, so I might be setting a certain DB for replication but other DBs will still be actively utilized by other apps. But I'll take a look at Maatkit :) –  samxli Jul 30 '11 at 2:32
1  
@samxli: --read-only option still allow update from the SQL threads or from users having SUPER privilege: dev.mysql.com/doc/refman/5.1/en/… –  quanta Jul 30 '11 at 11:18

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.