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.