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.
LATEST POSTS
-
How to Install Eclipse IDE on Ubuntu 18.04
-
How to Setup UFW firewall on Debian 9
-
How to Install Apache Tomcat 9 on CentOS 7
-
Secure Apache with Let’s Encrypt SSL on CentOS 7
-
How to Install Ruby on Debian 10
-
How to Install Netbeans on Ubuntu 18.04
-
How to Install Go on Ubuntu 18.04
-
How to Install Notepad++ on Linux Mint 19
-
How to Install Opera Browser on CentOS 7
-
How to setup UFW firewall on Ubuntu 18.04
-
How to Install CMake on CentOS 8
-
How to Install Apache on CentOS 7