0

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?

1 Answer 1

4

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.

5
  • 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, 2011 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, 2011 at 15:04
  • 1
    @samxli: Definitely.
    – womble
    Jul 30, 2011 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, 2011 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, 2011 at 11:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .