How to transfer a MySQL database to another server?

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.