11-24-2011, 03:26 AM
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
11-28-2011, 01:07 PM
To my knowledge, it isn't possible. You will have downtime whilst moving the database. As for losing data, if you use dump files, then you should keep all of the data that you previously had.
11-29-2011, 03:32 AM
Sure it's possible! Not trivial, but possible.
It's not even overly hard if you are willing to live with not being able to see data that is *ADDED* while you are doing the transition until the transition is complete.
(1) Set up the new database and clone the old table *structure* to the new database.
(2) Change your web code so that any new INSERTs are inserted into the new database.
(3) Continue to allow web code that does SELECTs to access the old database.
(4) DUMP the old database.
(5) IMPORT into the new database.
(6) Switch web code doing SELECTs to the new database.
If possible, disallow UPDATEs during the transition. If not, have your UPDATE code actually do a SELECT from the old DB, change the data in a pseudo-update, then store the data in the new DB. In the IMPORT process, ignore duplicate keys (that is, if one of these pseudo-updates has already created the data in the new db, don't overwrite it with the dumped data). Or, if the data already exists before the pseudo-update completes, *do* allow the pseudo-update to overwrite.
It will take a lot of planning and careful testing, to be sure that everything gets swapped from one server to the other at the appropriate times, but I don't see why it can't be done. The more you have properly used MVC coding techniques the easier it will be, since presumably in a well coded MVC system you will have centralized all your database manipulation. If coding is scattered willy nilly throughout your web pages as is typical in many web sites, then it's going to take a lot of work to make the changeover happen smoothly. But it should be possible.