Occasionally we need to deploy fairly complex changes to a website which require changes to databases (new or changed tables) and dynamic source code (such as PHP or ColdFusion). What is the best way to package and deploy these changes quickly without breaking everything on the live website?
Short answer: it is a pain in the neck!
I have had to do this several times (and every time makes me want to think about my design more on the next project I do). What I do is to fork the codebase to a Development section (or folder, or server, etc) so that I can work on it separately to make sure all the bugs are out before moving to deployment. I then create a SQL script which always has the same first two lines:
This will ensure that the script puts everything into a completely fresh and empty database. The rest of the script will recreate all tables that I'm reusing from the old database into the new one. Then I will create the new (or modified) tables into the new database. Finally I will move all the data from the live server to the development server. This can take a long time depending on how much data you are moving and how fast you can move it, so I recommend that you think about the script carefully because the less you are forced to run it, the better.
After I have the new development database up and running, the rest is to create the new code to use it. Once that is hashed out, then deployment should be relatively easy (though possibly time consuming). Simply use the same script to copy all the new live data to the new database on the deployment server, and copy all the new code there, and you are done!
This is how I do it, and while it is a pain, it does work. If there is a better way, I'd be happy to listen.
Update: In response to Tchalvak's comment, yes I usually use the following to get all the table definitions without their actual data:
I will then alter this script as described above.
I generally do (for LAMP style sites):
If you are running a cluster, then you may have bigger headaches with site state, caches and so on.
The days when you should work with your source code in flat files is over with (now you should work with it in flat files but have a really robust change control system tracking them).