I have a master-master (circular) replication configured between two MySQL servers, call them West and East. There is also a web app that interfaces with each DB, also on its own West and East server, such that the West web app talks to the West database and the East web app talks to the East database. This all works fine during normal operations, but the problem arises when it comes time to do quarterly deployments.
In these deployments, almost always, there are database content updates and frequently there are database structure changes as well (e.g., adding a table, adding columns to a table, etc). The web app is also updated at the same time so that it stays in sync with the database structure.
Thus far, we have not been able to perform a successful deployment without breaking replication, leading to a very messy situation and potential loss of data. The way we planned to do deployments is as follows:
- Shut down the West side web app and direct all traffic to the East side
- Stop the East side slave
- Deploy the West side web app + DB updates
- Turn back on the West side web app, shut down the East web app, and direct all traffic to the updated West side
- Start the East side slave
- Deploy to East side web app + DB updates
- Return to nominal
The issue seems to be around step 3. When users are using the web app on the East side (thus modifying the DB) and the deployment is occurring on the West side (modifying that DB), this often stops replication from working, necessitating manual intervention, data copying, altering the pointers to the master logs, etc. Is there a better way to do this to allow each master to be updated in isolation without risking replication getting out of sync? I fear our setup is not correct and/or sustainable.
Important considerations:
- There can be no downtime for the web app
- The updates the users may be making while directed to one side must be preserved when both sides go back online
Also, please note that I am not a DBA, so I apologize if I failed to include any important details about the configuration settings. I will try to update the question ASAP if needed.