Steps to Migrate MySQL Database Between 2 Servers: 3 Easy Steps

on Database, MySQL, Tutorial • September 4th, 2017 • Write for Hevo

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. It will also provide you with a brief overview of MySQL in further sections. Let’s get started.

Table of Contents

Introduction to MySQL

MySQL Logo
Image Source

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 was initially created in 1994 by MySQL AB, a Swedish business. Sun Microsystems bought the firm in 2008, and Sun Microsystems was eventually bought out by Oracle, a US IT behemoth. Oracle is currently in charge of MySQL’s development and expansion. Although MySQL is open-source and free to use for everyone, it has some premium features that Oracle exclusively offers to clients who are ready to pay for it.

MySQL is the database of choice for over 5000 businesses, including Uber, Netflix, Pinterest, Amazon, Airbnb, Twitter, and others.

To know more about MySQL, visit this link.

Simplify Integrations Using Hevo’s No-code Data Pipeline

Hevo Data helps you directly transfer data from MySQL, and 100+ data sources (including 30+ free sources) to Business Intelligence tools, Data Warehouses, or a destination of your choice in a completely hassle-free & automated manner. Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

Hevo takes care of all your data preprocessing needs required to set up the integration and lets you focus on key business activities and draw a much powerful insight on how to generate more leads, retain customers, and take your business to new heights of profitability. It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination.

Get Started with Hevo for Free

Let’s look at some unbeatable features of Hevo:

  • Real-Time Data Transfer: Hevo with its strong Integration with 100+ Sources (including 30+ Free Sources), allows you to transfer data quickly & efficiently. This ensures efficient utilization of bandwidth on both ends.
  • Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer. 
  • Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
  • Tremendous Connector Availability: Hevo houses a large variety of connectors and lets you bring in data from numerous Marketing & SaaS applications, databases, etc., such as HubSpot, Marketo, MongoDB, Oracle, Salesforce, Redshift, etc. in an integrated and analysis-ready form.
  • Simplicity: Using Hevo is easy and intuitive, ensuring that your data is exported in just a few clicks. 
  • Completely Managed Platform: Hevo is fully managed. You need not invest time and effort to maintain or monitor the infrastructure involved in executing codes.
  • Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
Sign up here for a 14-Day Free Trial!

Steps to Migrate MySQL Database Between 2 Servers

Migrate MySQL Database
Image Source

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 the MySQL database is to take a dump of the data that you want to transfer. 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

Note:

  • 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 transferring the data dump file to the destination server. You will have to use scp command for that.

Scp -P [port] [dump_file].sql [username]@[servername]:[path on destination]

Examples:

scp dump.sql root@130.243.18.62:/var/data/mysql
scp -P 3306 dump.sql root@130.243.18.62:/var/data/mysql

3) Restore the Dump

The last step 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

Example:

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

Conclusion

Following the above-mentioned steps, you can migrate MySQL database between two servers easily but it can be quite cumbersome if it is going to be a recurring task. An all-in-one solution like Hevo takes care of this effortlessly and helps in managing all your data pipelines in an elegant and fault-tolerant manner.

Hevo will automatically catalog all your table schemas and will do all the necessary transformations to migrate MySQL Database between these MySQL instances. 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 any schema changes or network failures through email and Slack. All of this can be achieved from the Hevo UI, no need to manage any servers or cron jobs.

Businesses can use automated platforms like Hevo Data to set the integration 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 a hassle-free experience

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. 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.

No-code Data Pipeline for your Data Warehouse