Migrating your live database

[cancer10]

Hi People,

I am not too sure if this is the correct section to post this. (Mods pls move this to the appropriate section if not)

This is a common scenario when you need to move your live website which has a good amount of traffic all the time to a new server. How do you manage to move the db WITHOUT locking the db or putting up a "site under maintenance" page and loosing any data?

Whats the best approach assuming the db server is on the same web server ?

Thanks in advance

[djr33]

Honestly, it varies entirely by site. You can't avoid all problems because the DNS records for your domain will need to update to reflect the location of the new server and that happens for each individual whenever their ISP/browser updates the info. So you'll have about 2 hours (technically up to 24, I think) for the transition.

If you're just switching the DB, then all you need to do is shut down the site for as long as it takes you to copy the DB. You'll need to backup the DB, then import it to the new server. Then just switch the connection info for your site. But you'll lose any intermediate posts any way that you do that because they either won't be recorded (if you have the site "down") or they'll be uselessly recorded on the old server after you've backed up the DB (if you leave the site running while moving).

The best approach will be to turn off the site for a few minutes once everything is really ready to go then move it quickly and turn it back on. You can do that at 3am or whenever there isn't much use to minimize the effect.

[cancer10]

Thanks for the reply.

What do the big giants like facebook/twitter do when they upgrade their site / db? Do they follow the same approach, as I have never seen an "upgrade" banner on their site?

[djr33]

Many big sites do have slight downtime. It might not be while you're awake, though.

It is theoretically possible to upgrade different users at different times. Remember that big sites like that aren't on one server. They're usually using a cloud server system so that redirecting traffic within that works differently than moving to a new server.

Another way to make it almost instantaneous would be to automate the process: use a script on the server to switch everything all at the same moment. Of course you'd need to be incredibly careful and so forth, but that way you would not have any problems with timing since it would take less than a second of downtime to make it work (if everything went right).

[cancer10]

Alright. Thanks for all ur replies.

[djr33]

Now that I'm seeing this thread again, there's another important point:
It's not necessarily that everything takes a long time to setup. It's usually just a moment or two (probably less than a minute) to change a line or two of code and upload all of your new files.

Then the site works. But the reason sites usually have 15 minutes to several hours of downtime is so you can check that everything is working-- downtime is better than debugging a live site.