Moving MySQL ?
 

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?

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

Thank you going to try this now :thumbsup

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

Yep

Thanks will lyk how it goes and if it worked :thumbsup

Can't you just move the MYD files? :winkwink:

Not everyone is rute :Graucho

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

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.

phpMyAdmin - Export DB

Then import it on your new box? 0.o

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

This is definetly the best way to do it.

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