There are many use cases when you need to migrate MySQL database between 2 servers, like cloning a database for testing, a separate database for running reports, or completely migrating a database system to a new server. On the broad level, you will take a backup of data on the first server, transfer it remotely to the destination server, and finally restore the backup on the new MySQL instance.
This article will walk you through the steps to migrate MySQL Database between 2 Servers using 3 simple steps. Additionally, we will explore the process of performing a MySQL migration, using copy MySQL database from one server to another operation. This process is crucial when you want to move your MySQL database to another server without losing any data or functionality.
We will cover the necessary steps and considerations involved in successfully completing a MySQL migration. So, whether you are looking to clone a database, create a separate database for reporting purposes, or completely migrate your database to a new server, this guide will provide you with the information you need.
Introduction to MySQL
MySQL is a popular open-source Relational Database Management System (RDBMS). MySQL uses a simple Client-Server Model to assist users in managing Relational Databases, or data stored in rows and columns across tables. It makes use of the well-known query language Structured Query Language (SQL), which enables users to execute all CRUD (Create, Read, Update, and Delete) operations
MySQL is the database of choice for over 5000 businesses, including Uber, Netflix, Pinterest, Amazon, Airbnb, Twitter, and others. Companies may need to migrate MySQL databases for various reasons, such as upgrading hardware or software, consolidating servers, or moving to a cloud-based environment. By migrating MySQL database, companies can also improve performance and reduce downtime.
Additionally, it allows companies to test new applications or software in a safe environment without affecting the production database. Overall, migrate MySQL database is a critical process that companies must undertake to ensure the smooth running of their operations and to stay competitive in today’s digital landscape.
If you would like to know more about loading data from file to table in your MySQL database, we recommend exploring our informative guide on how to load data from file to table in MySQL. Additionally, to familiarize yourself with MySQL master slave replication, be sure to check out our guide on 7 easy steps on MySQL master slave replication.
Steps to Migrate MySQL Database Between 2 Servers
Now that you have a basic grasp of MySQL, let’s try to understand the steps to migrate MySQL database between 2 servers. Below are the steps you can follow to migrate MySQL database between 2 servers:
1) Backup the Data
The first step to migrate MySQL database is to take a dump of the data that you want to transfer. This operation will help you move mysql database to another server. To do that, you will have to use mysqldump command. The basic syntax of the command is:
mysqldump -u [username] -p [database] > dump.sql
If the database is on a remote server, either log in to that system using ssh or use -h and -P options to provide host and port respectively.
mysqldump -P [port] -h [host] -u [username] -p [database] > dump.sql
There are various options available for this command, let’s go through the major ones as per the use case.
A) Backing Up Specific Databases
mysqldump -u [username] -p [database] > dump.sql
This command dumps specified databases to the file.
You can specify multiple databases for the dump using the following command:
mysqldump -u [username] -p --databases [database1] [database2] > dump.sql
You can use the –all-databases option to backup all databases on the MySQL instance.
mysqldump -u [username] -p --all-databases > dump.sql
B) Backing Up Specific Tables
The above commands dump all the tables in the specified database, if you need to take backup of some specific tables, you can use the following command:
mysqldump -u [username] -p [database] [table1] [table2] > dump.sql
C) Custom Query
If you want to backup data using some custom query, you will need to use the where option provided by mysqldump.
mysqldump -u [username] -p [database] [table1] --where="WHERE CLAUSE" > dump.sql
Example: mysqldump -u root -p testdb table1 --where="mycolumn = myvalue" > dump.sql
- By default, mysqldump command includes DROP TABLE and CREATE TABLE statements in the created dump. Hence, if you are using incremental backups or you specifically want to restore data without deleting previous data, make sure you use the –no-create-info option while creating a dump.
mysqldump -u [username] -p [database] --no-create-info > dump.sql
- If you need to just copy the schema but not the data, you can use –no-data option while creating the dump.
mysqldump -u [username] -p [database] --no-data > dump.sql
2) Copy the Database Dump on the Destination Server
Once you have created the dump as per your specification, the next step is using the data dump file to move MySQL database to another server (destination). You will have to use the “scp” command for that.
Scp -P [port] [dump_file].sql [username]@[servername]:[path on destination]
scp dump.sql email@example.com:/var/data/mysql
scp -P 3306 dump.sql firstname.lastname@example.org:/var/data/mysql
3) Restore the Dump
The last step in MySQL migration is restoring the data on the destination server. MySQL command directly provides a way to restore to dump data to MySQL.
mysql -u [username] -p [database] < [dump_file].sql
mysql -u root -p testdb < dump.sql
If your dump includes multiple databases, don’t specify the database in the above command.
mysql -u root -p < dump.sql
Following the above-mentioned steps, you can migrate MySQL database between two servers easily, but to move MySQL database to another server can be quite cumbersome activity especially if it’s repetitive. An all-in-one solution like Hevo takes care of this effortlessly and helps manage all your data pipelines in an elegant and fault-tolerant manner.
Hevo will automatically catalog all your table schemas and do all the necessary transformations to copy MySQL database from one server to another. Hevo will fetch the data from your source MySQL server in an incremental fashion and restore that seamlessly onto the destination MySQL instance. Hevo will also alert you if there are schema changes or network failures through email and Slack. All of this can be achieved from the Hevo UI, with no need to manage any servers or cron jobs.
Businesses can use automated platforms like Hevo Data to connect 150+ data sources and handle the ETL process. It helps you directly transfer data from a source of your choice to a Data Warehouse, Business Intelligence tools, or any other desired destination in a fully automated and secure manner without having to write any code and will provide you with a hassle-free experience.
VISIT OUR WEBSITE TO EXPLORE HEVO
Want to take Hevo for a spin?
Sign Up for a 14-day free trial and experience the feature-rich Hevo suite firsthand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.
Share your experience of learning about the steps to migrate MySQL database between 2 servers in the comments section below.