Just wanted to ask an advice on this situation here:
What I’m thinking of doing is to setup two sets of Master-Slave Replication. So lets call those M1-S1 and M2-S2.
The main idea behind this is that the live site should be running off of one set at a time and mean while the other set should be used for lots of importing/updating, basically heavy writes.
M1 should be sending updates to S1 and M2 meanwhile M2 Should NOT send its updates to M1 but should send them to S2.
After importing is done the live site is switched to the second set (m2-s2) and M1 should be set as slave to M2 so it can catch up on fresh imports.
Basically what I’ve tested so far works but I think with some other mysql settings can be done even better (less stuff to do).
M2 receives the updates from M1 but doesn’t pass them to S2 (it would be nice if it did since after the importing is done I have to switch S2 to receive updates from M1 and then switch it back)
On M2 I have to set the auto_increment to smth like current_value + 10000 since while the importing is done M1 might have new writes and stuff and that needs to be replicated to M2. I don’t think there is something out there that can somehow help me with this auto_increment thing, even though it would make things less complicated…
Any suggestion are welcomed )