0

I have an unusual situation where I am migrating websites and their corresponding databases from one server to another.

I have a database that is hosted on one server, A, where new records are created, and these are accessed and modified on the second server B (but no new records are created).

I have set up A as master, and B as slave to ensure that B has access to new records created by A, but A does not need access to the amended record data that are changed on B.

I know there are lots of warnings about not changing data on the slave, but it seems to me that this should work OK (until I finish the migration, at which point I will move the record creation facilities to server B and break the slave link).

Any thoughts?

0

On this scenario replication should not face any issues.

MASTER (A): INSERT

SLAVE (B): UPDATE,DELETE

The only issue i can think is to change a unique constraint on B and try to insert the same record on A.

| improve this answer | |
  • You have it - Master A is only used to add new records... Slave B is used to access these records and update them - no deletes or changes to the primary key fields are made. – Andy Stokes Sep 4 '14 at 15:35
0

So, if I understood correctly, you want to update records on a slave while the originals are still accessed on the master.

You are completely right in that it is possible, but very easy to get it broken:

  • If you have a primary key or unique keys that can change, you may end up with duplicate keys, breaking replication
  • Foreign keys, triggers, etc. may end up creating unexpected results
  • Modifying values based on other rows (INSERT SELECT, IODKU, REPLACE, INSERT IGNORE, or just reading rows and then updating them will end up with different results)
  • Autoincrement keys may end up being different because of the previous case (even if you do not insert on the slave)

Best case scenario, replication will break. Worst case, slave and master will drift in a way that it is not intended. Row-based replication may reduce the drifting, and it should be reliable if you only update the table using the primary key. In practice it is very difficult to preview all possibilities, as databases and SQL is complex, unless you do very contained/controlled writes on both servers.

However, assuming that B is not in production, you can do regular checks and fix replication errors if they occur, making it possible.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.