I have built a web app using MySQL and php and I'm at the moment trying to figure out the best approach to make a script to automatically update the customers based on the new changes on my development environment without affect the customer data.
So far my first attempt was to check the app version if there is a new one, download the zip with the new and changed files, then I do a mysqldump skipping triggers, etc. of the customers, drop all tables on the customers database, load the scheme and reload the dumped file.
The problem I am facing is that this work if the change in the scheme is a minor one, if I decide to add a couple of columns with new values, or remove unused tables, or to remove unused rows the upload fails.
- So my question is whats the best approach to safely update different databases, based on the my development database changes?
- I guess the best way is to include queries upgraded script with all the needed queries on it?
- But is this the right way?
- There is some automatic way to handle this to avoid to manually have to write each change and avoid as well to miss some changes on the script and screw it all?
I really appreciate your opinions as looking around, I didn't find any clear approach of this procedure.
