Stud Money

Got a problem that i am hoping someone may have the answer too.

We want to move one of our MySQL databases from server a to server b.

What is the easiest way of doing this?

We have already tried to view a data dump but there is so much info in this single DB that it keeps crashing IE.

Any easy way around this short of asking our host to do it?

- Jesus Christ -

Step One - Backup current database:

Telnet/SSH into your system (we'll call it machine1)
from the root directory, type
mysqldump --opt -uUSERNAME -p databasename > /path/to/dump.sql
this should only take about than 30 seconds to a few minutes, depending on your database size
when it is completed, it will bring you back to the prompt
verify that you have a dump.sql in the /path/to area you specified
Step Two - Transfer to new server

Telnet/SSH into machine1, and from the command prompt type the following:
cd /path/to/yourbackupdirectory
Then type
ftp MACHINE2
Replace MACHINE2 with the host name (eg. www.example.com ) or IP address (eg. 192.168.0.0 ) of your new server. This should open a connection and ask for your username/password, then it should say ftp>
Type the following, hitting enter after each line:
bin
cd /path/to/newdirectory
put dump.sql
It should take between 20 seconds and a few minutes depending on the size of the file. Now type:
close
quit
verify dump.sql is in the /path/to/new area on the new server.

Step Three - Restore to new server

Telnet/SSH into machine2
create a database on the mysql if necessary (ask your server admin for help with this as it varies from machine to machine)
at the prompt type
mysql -uUSERNAME -p newdbname < /path/to/dump.sql
this takes a couple of minutes depending on your database size, but you can track the progress either by FTP (checking the /mysql/data/newdbname and see the files appearing) or by phpMyAdmin (you should see the files appearing there)
when it is completed, it will bring you back to the prompt

__________________

Amen

Stud Money

Thank you going to try this now

A quick question though if i may, will this be the same even if machine2 is hosted at a different company to machine 1?

- Jesus Christ -

Yep

Stud Money

Thanks will lyk how it goes and if it worked

JSA Matt

Can't you just move the MYD files?

- Jesus Christ -

Not everyone is rute

__________________

Amen

NetRodent

Not if the servers aren't running the same version of mysql.

__________________
"Every normal man must be tempted, at times, to spit on his hands, hoist the black flag, and begin slitting throats."
--H.L. Mencken

PbG

If you have Cpanel you can simply download a backup of the database and restore it on the new box.

Additionally you could SCP the file using the method described above.

__________________

Uncensored-Hosting | Photography by Gus

Project-Shadow

phpMyAdmin - Export DB

Then import it on your new box? 0.o

sweet7

That's definately in their job description. Otherwise telnet/ssh into them and do what others are telling you.

__________________
ICQ: 282814268

foe

This is definetly the best way to do it.

Dont use phpMyadmin, I found that for very big files it messes up sometimes.