Results 1 to 11 of 11
-
08-29-2006, 06:39 PM #1Newbie
- Join Date
- Aug 2006
- Posts
- 28
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.
-
08-29-2006, 07:24 PM #2Web Hosting Rockstar
- Join Date
- Dec 2001
- Location
- 127.0.0.1
- Posts
- 3,642
Originally Posted by EyeLikeBandwidth
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!
-
08-29-2006, 07:30 PM #3Newbie
- Join Date
- Aug 2006
- Posts
- 28
How about without shell access?
I say that because the shell I am using with Site5 is always screwed up.
-
08-29-2006, 08:25 PM #4is a threadkiller
- Join Date
- Jan 2002
- Location
- Ohio
- Posts
- 3,155
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.
-
08-30-2006, 03:36 AM #5WHT Addict
- 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.
-
08-30-2006, 04:32 AM #6Web Hosting Master
- Join Date
- Oct 2003
- Posts
- 9,264
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.
-
08-30-2006, 06:45 AM #7Junior Guru Wannabe
- Join Date
- Aug 2006
- Posts
- 50
You can transfer it through PHPMyadmin or if you used CPanel you can choose the backup option.
Regards,www.hostingbug.co.uk - Hosting BUG - UK
- WebHosting Services and Domain Name Registration -
Sales Department::: sales@hostingbug.co.uk - 30 Day Money back guarantee + 24/7 support
-
08-30-2006, 09:02 AM #8Newbie
- Join Date
- Aug 2006
- Posts
- 25
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...
-
08-30-2006, 11:29 AM #9Web Hosting Master
- Join Date
- Nov 2001
- Location
- Philadelphia, Pa
- Posts
- 948
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.
-
08-31-2006, 09:28 PM #10Newbie
- Join Date
- Aug 2006
- Posts
- 28
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!
-
08-31-2006, 11:25 PM #11WHT Addict
- 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