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?

samxli
  • 203
  • 1
  • 3
  • 11

1 Answers1

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.

womble
  • 96,255
  • 29
  • 175
  • 230
  • 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](http://www.maatkit.org/doc/mk-table-sync.html). – sreimer Jul 29 '11 at 15:04
  • 1
    @samxli: Definitely. – womble Jul 30 '11 at 00: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 02:32
  • 1
    @samxli: `--read-only` option still allow update from the SQL threads or from users having `SUPER` privilege: http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_read-only – quanta Jul 30 '11 at 11:18