How to migrate a Mysql database/table from one server to another?

The easiest way to transfer a Mysql database from one server to another is by creating a backup, copy the mysql database backup to another server and restore it on the remote server.

The first thing is to create a backup of the mysql database using mysqldump. It creates a backup in the .sql format. You have to use your SSH user/password while backup/restore of database.

To backup a specific database:

# mysqldump -u root -p db_name > db_name.sql

To backup a specific Mysql table:

# mysqldump -u root -p db_name tb_name > tb_name.sql

Copy the database/table backups to the remote server using SCP (ssh):

# scp db_name.sql root@remote_server_ip:/backup/
# scp tb_name.sql root@remote_server_ip:/backup/

Login to the remote server and restore the .sql file. You should be in the backup directory while restoring

# mysql -u root -p db_name < db_name.sql
# mysql -u root -p db_name < tb_name.sql

On a cPanel server, the mysql user is ‘root’ and the mysql password.
On a Plesk server the mysql user is ‘admin’ and the Plesk admin password.

This entry was posted on Saturday, March 3rd, 2012 and is filed under Mysql & PostGres SQL. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

2 Responses to “How to migrate a Mysql database/table from one server to another?”

  1. Art Zemon

    Adding the –opt option is a good idea, too. E.g.,

    mysqldump –opt -u root -p db_name > db_name.sql

  2. admin

    Thanks for sharing.. will see a few more combinations/options and will add them to the post as well.