Results 1 to 15 of 15
  1. #1

    Transfering Databases from One Server to Another

    I'm having trouble transfering MySQL databases from one server to another. Is there anything I should know when doing this? When I go to site I receive errors such as this:

    Table 'upload_uploadhouse.setting' doesn't exist SQL=select * from setting

    or (on a different website):

    Database error: cannot use database vikingla_vikinglandsptc
    MySQL Error: 0 ()
    Session halted.

    Any help would be appreciated.

  2. #2
    Join Date
    Nov 2005
    Location
    Boston
    Posts
    816
    you can export the whole db using phpmyadmin then just import it on the new server
    Dimension Servers / Toll-Free: 1-888-750-6942
    RapidSSL Cert Included with ALL Reseller & Ultra Accounts
    24x7x365 Unbeatable Technical Support!
    Shared CPanel Hosting // Reseller WHM Hosting Solutions

  3. #3
    Where do I import in phpmyadmin ... I only see an export option.

  4. #4
    Join Date
    Dec 2002
    Location
    Quad Cities, Iowa
    Posts
    1,597
    First thing, check the mysql version on the server you are copying from.

    phpMyAdmin on that server should tell you the MySQL version. If it's 4.1 and the server you are copying to is 4.0 you will need to select "SQL export compatibility -> MYSQL40" when exporting.

    On the server you are copying to:
    Load up phpMyAdmin, on the left hand side of the page there will be 5 icons above the database dropdown. Select the 3rd Icon which is the Query Window. A new window will open up, choose the Import Files tab. Click Browse and locate the database dump file on your PC that should of been generated when exporting the database from the other server.

    From there it's pretty self explanatory. If you get errors when importing, it could be the database is to large to import through phpMyAdmin. If so post back here and I will tell you how to import from SSH (Assuming you have that capability).
    Need a new Web Host?
    Become a Host Refugee and receive TRUE 24/7 Support

    cPanel + Fantastico, PHP4 or PHP5
    HostRefugee.com - See our current promotions

  5. #5
    Thank you very much for that thorough answer. The database is 75mb and it shows the max is 51,000KB, so I suppose I'll have to go the SSH route if you don't mind explaining how that way will work.

  6. #6
    Join Date
    Oct 2002
    Location
    State of Disbelief
    Posts
    22,948
    mysql -u username -p database_name < dumpfile.sql

    Replace the obvious, and run it from the directory you placed the backup.
    Having problems, or maybe questions about WHT? Head over to the help desk!

  7. #7
    NVM got it

  8. #8
    Gah. Now when I go to the site I receive this error again:

    Database error: cannot use database vikingla_vikinglandsptc
    MySQL Error: 0 ()
    Session halted.

    Am I supposed to login in SSH as root or as the username the database is in? What password do I put? Must I create the user before hand?
    Last edited by spikez202; 12-12-2005 at 07:26 PM.

  9. #9
    What's supposed to happen if I did it correctly?

  10. #10
    Alright, well I think I got it now, but the creation seems to be hanging.

    It's a 75mb database, so how long do you suspect it'd take to finish?

    It's been like this for a few minutes:
    I entered the password and now when I go to the site I see this error:
    Database error: Invalid SQL: SELECT id FROM stats WHERE date='12/12/05'
    MySQL Error: 1146 (Table 'vikingla_vikinglandsptc.stats' doesn't exist)
    Session halted.

    Is it creating that database still?

  11. #11
    I love how this whole thread is just me whining and complaining. Well I got everything to work, and the site now loads fine.

  12. #12
    Join Date
    Mar 2004
    Location
    [BC]
    Posts
    161
    Just wondering, did you have any large jpg/pdf/etc... files stored in this database?
    | Priority support (Since July 2001) | 99.9%+ uptime (Alertra stats available) |
    | Dual Xeon servers at local data center | Premium PEER1 bandwidth |
    | Visit us today | www.webulex.com |

  13. #13
    Quote Originally Posted by wb-Edgar
    Just wondering, did you have any large jpg/pdf/etc... files stored in this database?
    Not in this one, but there will be in another site I'll be transferring.

  14. #14
    Join Date
    Oct 2002
    Location
    State of Disbelief
    Posts
    22,948
    Glad you got it to work. I was out this evening enjoying a concert my daughter was performing in, so I wasn't here....
    Having problems, or maybe questions about WHT? Head over to the help desk!

  15. #15
    Join Date
    Nov 2005
    Location
    Singapore
    Posts
    6
    Those of us who are challenged by similar database issues, really appreciate your willingness to go public and ask for help. Because you doggedly pursued this to its conclusion, we've got a pretty nice tutorial built into this thread. Thanks.

Posting Permissions

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