How to migrate a MySQL database between two servers

How to migrate a MySQL database between two servers

Migrate MySQL database between two servers

Often, databases need to be replicated / transferred from one server to another. Each time you move a project from the old server to the new server, you need to copy files between servers. The SCP (Secure Copy) command is used to transfer files between two servers. In this tutorial, you will learn how to migrate a MySQL database between two servers using SCP commands.

Prerequisites

Before you start migrating a MySQL database between two servers. Both servers require a non-root user account with sudo privileges.

Migrate a database between two servers

You must first back up the database and transfer it remotely to the destination server. Finally, restore the backup to the destination server.

To back up the database, enter the 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

Use the following command to copy the backup file to the destination server.

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

Example:

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

Import the backup file to the 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 back up a database on a remote server using port and host. Use the following command:

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

To backup Multiple databases, use the following command

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

To import Multiple databases on the destination server.

sudo mysql -u root -p < databases.sql

To back up all databases, enter the following command:

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

If you need to back up specific database tables, use the following command:

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

Conclusion

You have learned how to migrate a MySQL database between two servers using SCP commands. If you have questions about this, don't forget the comments below.

Sidebar