Transfer MySQL database to remote server

This post has been rewritten.  Please see How to transfer a MySQL Database to a remote server for updated information

Hey,

This week I’ve moved back from my ultimate dedi to a Futurehosting VPS in an effort to cut costs and turn a larger profit from Drag Racer.  I tried to make the transfer of data as quick as possible and seeing as how my database is a few hundred MB I transferred it using mysqldump.

First thing’s first, you need to prepare the receiving server.  Create a user for the sending server to connect as using this command from the mysql console:

GRANT ALL PRIVILAGES ON *.* TO ‘username’ @ ‘  [OLD SERVER IP] ‘ IDENTIFIED BY ‘password’;

This will allow your mysql server with the IP [OLD SERVER IP] (replace this with it’s real ip ofcourse) to connect using the username ‘username’ and password ‘password’.

Next we are going to use mysqldump in the linux shell (or windows cmd prompt, I’m not 100% where it’s located in windows) to dump our databases to our new server.  The syntax of the command is as follows:

# mysqldump -u username -p -h [NEW SERVER IP] [DATABASE NAME(S)]

The command to dump two databases (one called database1, the other database2) to ip 192.168.1.1 is as follows.

# mysqldump -u username -p -h 192.168.l.1 database1 database2

mysqldump will now take a few minutes (depending on the size of the database in question) to dump the entire contents of database1 and database2 onto your new server (in this example 192.168.1.1).  I recommend that once you are finished with this, you remove the user we created to copy the database over.  To do this we use this command from the mysql console:

drop user username;

If you have any questions please leave them below in the comments.  I also recommend using the following website to look up the commands I showed and their uses:
mysql.org

3 Comments

• 

  By Craig, April 22, 2009 @ 11:18 am

  Craig <3 Futurehosting – I miss them LOL

• 

  By Stephen Groom, January 5, 2010 @ 11:59 pm

  So the day comes when I actually have to use this. Firstly there is a typo at ” GRANT ALL PRIVILAGES ON *.* TO ‘username’ @ ‘ [OLD SERVER IP] ’ IDENTIFIED BY ‘password’;”

  it should read
  ” GRANT ALL PRIVILEGES ON *.* TO ‘username’ @ ‘ [OLD SERVER IP] ’ IDENTIFIED BY ‘password’;”

  

  More to follow probably

• 

  By Stephen Groom, January 6, 2010 @ 12:09 am

  Uhhh.. Stupid tutorial, this needs rewritten.. I was obviously exstatic when I wrote it as it’s total BS

Other Links to this Post

RSS feed for comments on this post. TrackBack URI

Leave a comment