Moving MySQL ?

[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

[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 -]

Originally posted by JSA Matt
Can't you just move the MYD files?

Not everyone is rute

[NetRodent]

Originally posted by JSA Matt
Can't you just move the MYD files?

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

[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.

[Project-Shadow]

Originally posted by 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.

phpMyAdmin - Export DB

Then import it on your new box? 0.o

[sweet7]

Originally posted by 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?

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

[foe]

Originally posted by - 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

This is definetly the best way to do it.

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