How to transfer a MySQL Database to a remote server

This tutorial will show you how to transfer your mysql databases to a remote server.  The databases will be directly transferred over TCP which removes the time spent downloading .sql files from your old server and re-uploading them to your new server.  This is the fastest way to transfer a MySQL database. This guide is written using linux commands but how to do the same thing on windows should be obvious.  If it isn’t, leave a comment below and I’ll explain step by step.

Preperation

The new server (the destination for the database) must be accepting connections on port 3306 to MySQL.  This is acheived using the following steps:

• Edit it’s my.cnf file and ensure that the line “skip-networking” is removed
• Ensure the server is accepting connections on port 3306:  “[]$ iptables -I INPUT -p tcp –dport 3306 -j ACCEPT”
• Add a user to mysql who is authorised to connect from a remote IP.
  <mysql>: CREATE USER ‘transfer’@’[OLD SERVER IP]‘ IDENTIFIED BY ‘[PASSWORD]‘;
  <mysql>: GRANT ALL PRIVILAGES  ON *.* TO ‘transfer’@’[OLD SERVER IP]‘ WITH GRANT OPTION;
  Don’t forget to replace [OLD SERVER IP] and [PASSWORD] with your own values

Process

Now we will dump the data from our old server at [OLD SERVER IP] to the new server.  From a shell on our new server run the following command:
[]$ mysqldump -u[USER] -p[PASS] –all-databases | mysql -utransfer -p[PASSWORD] -h[NEW SERVER IP]

This command will then transfer all databases from [OLD SERVER IP] to [NEW SERVER IP].  Remember to delete the user ‘transfer’ from the mysql database for security reasons after this process is complete. For information on the mysqldump command see the mysqldump page and for any additional information please leave a comment below

Thanks
Stephen

No Comments

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a comment