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?

3 Answers 3


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:

DELETE IF EXISTS newDatabaseName;
CREATE DATABASE newDatabaseName;

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:

mysqldump --databases --no-data databaseName > newScript.sql

I will then alter this script as described above.

  • What about the state in those tables?
    – Aiden Bell
    Jul 8, 2010 at 20:51
  • This seems... ...strange given the availability of tools like pg_dump someDataBase > testing_database.sql Just run that on a live database and you have a copy that you can start testing on. Any -further- scripts beyond that could be additional files that would then be run on live. That's for postgresql, but there's also the mysql version, if I recall.
    – Kzqai
    Jul 8, 2010 at 21:12

To gain "the ability to rollback", I really suggest getting to know (well) either git or mercurial (distributed version control systems).

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).

  • 1
    To use mercurial to manage website revisions see this question. webmasters.stackexchange.com/questions/262/…. It's surprisingly easy to setup mercurial to do site revisions and you'll no longer need to use FTP to publish changes to the site. Jul 8, 2010 at 23:25

I generally do (for LAMP style sites):

  1. define your changes then write a script to apply them (I use a Makefile).
  2. Test your changes on a sample of data that represents the real environment
  3. Test again to make sure
  4. Give yourself a way out, have the ability to rollback

If you are running a cluster, then you may have bigger headaches with site state, caches and so on.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.