I want to set up a staging server to do final test for my website. The staging site's database is replicating production master mysql db in order to reflect a real environment.
If a deploy only contains code changes, it's easy to deploy it first to staging and then to production if it's stable enough.
But if a deploy contains database changes, how can I deploy it to staging without influencing master's database but still keep it realtime sync with master db?
Edit:
If it can't be done. How can I make the data write to staging server 'not lossing'? If some user use the beta site, I don't want their data be lost when staging finally became stable.
I wonder how facebook or google roll out their new features without losing beta tester's data?
Best Answer
You can't.
My suggestion is to not replicate production to staging, but to occasionally rebuild the staging database with one of your production backup files. Then you're working with production data (albeit a few hours or days old) but changes you make on staging won't affect production.
EDIT: With your edit, this is now a significantly different question.
If you have beta testers using your staging server and expecting their data to roll into production, it's not a staging server: it's a production server. I think that's the fundamental concept shift making this difficult to answer; most of us (I think) consider a staging server to be an etch-a-sketch that can be dumped and rebuilt without fear. If you're planning to allow some users to beta-test your changes and retain their data when you roll into production, you need a different framework than staging/production.
EDIT2: Here are some Stack Overflow questions that deal with this sort of setup: