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?

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
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? Commented 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. Commented Jul 29, 2011 at 15:04
  • 1
    @samxli: Definitely. Commented 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 :) Commented 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/… Commented Jul 30, 2011 at 11:18

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.