How to transfer a MySQL database to another server?

server-migration

Let’s say you want to transfer your blog to a new server. You have uploaded all the files and what is left is just the database.

You can import the database to the new server by using phpMyAdmin but it will take a lot of time if the database size is large. So, the best and easiest solution that I would suggest is, transfer the database by using SSH.

To do that, you must make sure you have SSH access in both servers – old and new server. You can request the access by contacting your hosting provider to enable it for you.

Old Server

  1. Telnet/SSH into the old server.
  2. Backup your database by issuing this command:
    mysqldump –opt -Q -u USERNAME -p DATABASENAME > /PATH/TO/DATABASENAME.sql
  3. Use the MySQL database password when the old server asks for password.
  4. Then, zip the file to reduce the file size:
    zip DATABASENAME.zip DATABASENAME.sql
  5. Done.

Note:

  • USERNAME – This is the username that you use to access MySQL.
  • DATABASENAME – Name of the database.
  • /PATH/TO/DATABASENAME.sql – The path of the database file that will be created.

Now you have the the database in a zip file format, DATABASENAME.zip.

New Server

  1. Telnet/SSH into the new server.
  2. Download DATABASENAME.zip from the old server:
    wget www.oldserver.com/PATH/TO/DATABASENAME.zip
  3. Unzip the file:
    unzip DATABASENAME.zip
  4. Import the database file into your new server:
    mysql -u NEWUSERNAME -p NEWDBNAME < /PATH/TO/NEW/DATABASENAME.sql
  5. Use the new MySQL database password when the server asks for password.
  6. Done.

Note:

  • NEWUSERNAME – This is the new username that you use to access MySQL.
  • NEWDBNAME – Name of the new database.
  • /PATH/TO/NEW/DATABASENAME.sql – The path of the database file that you have extracted in New Server – Step 3.

That is how to transfer a MySQL database to another server. I have used this technique a lot when migrating my blogs to a new server without any problem. So, good luck! 🙂

Comments

  1. Milly Tillburg says:

    Hi, thanks for a very useful post! I was just looking around for exactly this issue, so I’m glad I found it. Thing is, I wonder how this would work with a MySQL database that’s online, or stored online, i.e – it’s a cloud database, and I’m trying to understand how one could move it to or from the cloud as quickly (and securely) as possible… I will try to figure it out with this code anyway. Thanks 🙂