Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    907
    Thanks
    301
    Thanked 2 Times in 2 Posts

    Question Migrating your live database

    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
    Last edited by phantom007; 11-24-2011 at 03:35 AM.

  • #2
    New Coder
    Join Date
    Sep 2011
    Location
    England
    Posts
    39
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    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.

    Example steps:

    (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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •