How to migrate MySQL database between two servers

Migrate MySQL database between two servers

Many times we need to clone/transfer database from one server to another. Whenever we are moving projects to new server from old server we need to copy files between to servers. SCP (Secure Copy) command is used to transfer files between two servers. In this tutorial you are going to learn to migrate MySQL database between two servers by using SCP command.

Prerequisites

Before you start to migrate MySQL databases between two servers. You must have the non-root user account on both of your servers with sudo privileges.

Migrate Database between two servers

First you will need to backup the database then transfer it remotely to destination server and finally you will restore backup on destination server.

To backup the database enter following command.

sudo mysqldump -u root -p --opt [database_name] > [database_name].sql

Example:

sudo mysqldump -u root -p --opt test_database > test_database.sql

Copy backup file to destination server by using following command.

sudo scp [database_name].sql [user_name]@[server_name]:path/on/destination/

Example:

sudo scp test_database.sql [email protected]:/temp/backup/

Import backup file on destination server.

sudo mysql -u root -p database_name < /path/to/database_name.sql

Example:

sudo mysql -u root -p test_database < /temp/backup/test_database.sql

Useful Commands

To backup database on remote server using port and host. Use following command.

sudo mysqldump -P [port] -h [host] -u [user_name] -p [database_name] > database_name.sql

To backup multiple databases, Use following command

sudo mysqldump -u [user_name] -p --databases [database_name1] [database_name2] > databases.sql

To import multiple databases on destination server.

sudo mysql -u root -p < databases.sql

Backup all the databases, enter following command.

sudo mysqldump -u [user_name] -p --all-databases > databases.sql

When you need to backup specific database tables then use following command.

sudo mysqldump -u [user_name] -p [database_name] [table1] [table2] > database_name.sql

Conclusion

You have successfully learned to migrate MySQL databases between two servers by using SCP command. If you have any queries regarding this then dont forget to comment bellow.