Results 1 to 11 of 11
  1. #1

    Easiest way to transfer a MySQL database

    Hopefully I posted this in the correct section here..

    Anywho,
    I have finally made the switch from a shared to a VPS, but I have a MySQL Vbulletin database that has become rather large.. What's the easiest way to export it and import it? It's going from the same versions of MySQL too.

  2. #2
    Join Date
    Dec 2001
    Location
    127.0.0.1
    Posts
    3,640
    Quote Originally Posted by EyeLikeBandwidth
    Hopefully I posted this in the correct section here..

    Anywho,
    I have finally made the switch from a shared to a VPS, but I have a MySQL Vbulletin database that has become rather large.. What's the easiest way to export it and import it? It's going from the same versions of MySQL too.
    mysqldump -u <username> -p database > database.sql

    to restore:

    mysql -u <username -p database < database.sql via SSH.

    HTH,
    Simpli Networks, LLC :: http://www.simplinetworks.com :: Proudly 100% Owned.
    Providing Affordable Managed Cloud/VPS Servers & Server Management Solutions.
    We offer REAL 24x7x365 in-house support - proudly serving our customers since 2005!
    Want to learn more? Give us a call - +1 (844) 4SIMPLI or email sales[@]simplinetworks.com today!

  3. #3
    How about without shell access?

    I say that because the shell I am using with Site5 is always screwed up.

  4. #4
    Join Date
    Jan 2002
    Location
    Ohio
    Posts
    3,139
    It's pointless to download the database, and then reupload it. However, downloading for backup purposes is good. Not everybody has fat upload pipes.

    Ask your host if they'd be willing to copy over the mysql data files onto your new account, assuming you have the same host.

    You also might want to see if running a cron once would be more effective at running your commands.

  5. #5
    Join Date
    Oct 2004
    Location
    Canada
    Posts
    144
    You could use phpMyAdmin, if it is available. In case you are unfamiliar with it, php My Admin is an open source, web based MySQL admin tool. You should be able to just backup the database using the export function and saveing it to a file on your computer. Then you just login to phpMyAdmin on the new server and use the import function.

  6. #6
    Join Date
    Oct 2003
    Location
    Chattanooga
    Posts
    8,985
    The following pages have the details:

    How to back it up:


    http://php.about.com/od/learnmysql/s...l_backup_3.htm

    How to restore it:
    http://php.about.com/od/learnmysql/s...l_backup_4.htm

    Enjoy.

    Note: In the new versions of phpMyAdmin you can also 'restore from a url'. Try having your current host generate an sql file and place it in your public_html folder. You can then simply 'restore' it on the new one by using the url.

    You'll have to get the IP address url (i.e. http://255.255.255.255/~yourusername for cpanel boxes) though as the new host is going to have your domain's entries in their DNS so it will attempt to pull the backup locally.
    David
    Web hosting by Fused For businesses with more important things to do than worry about their hosting.

  7. #7
    Join Date
    Aug 2006
    Posts
    50

  8. #8
    I strongly recommend asking your hosting company if they can arange the transfer for you. Of course if they can't/don't want to and you don't want to use SSH, you will have to use phpMyAdmin...

  9. #9
    Join Date
    Nov 2001
    Location
    Philadelphia, Pa
    Posts
    949
    Use phpMyAdmin to dump the file, but use mysqldump to insert it into your VPS.

    but really, the easiest way would be to:
    - do a mysqldump to dump the database into your public_html folder on your shared.
    - wget the dump to get it on your VPS.
    - mysqldump to restore the database on your VPS.

    you could also run the mysqldump with a -h flag (specifying the server name) from your VPS if they've allowed remote connections to avoid the wget step, but I'd probably go with the extra step added in.

  10. #10
    Thanks for all the advice everyone.

    It was almost a 400MB database.

    I was able to export it via PHPMyadmin. Then I FTPed it up to my local public_html folder and used this script:
    http://forums.site5.com/showpost.php...75&postcount=5

    to import it..

    I always had timeout issues when trying to import it from PhpmyAdmin. But this system worked out well..
    I thought I would let you all know!
    Thanks!

  11. #11
    Join Date
    Oct 2004
    Location
    Canada
    Posts
    144
    I never thought about the size of your backup. The timeouts were most likely caused by the upload_max_filesize setting in php.ini. The default setting is 2 meg. That might cause a problem

Posting Permissions

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