How to Migrate MySQL Databases Using Migration Tools

There is often a need to migrate MySQL databases - either to transfer data to a testing server or to completely migrate the entire database to a new production server. dbForge Studio for MySQL provides robust database migration capabilities, supporting the most popular MySQL-compatible cloud databases, storage, and search engines including MariaDB, AmazonRDS, Amazon Aurora, etc.

Read the guide to find out:

How to migrate data and/or databases with the mysqldump utility
How to transfer databases to a new server using the Copy Database functionality
How to back up and restore a MySQL database
How to migrate databases between servers using the Export/Import functionality

Data migration in MySQL

In MySQL, databases can be transferred between the same or different instances. You can back up, restore, and migrate or copy data and/or database structure between servers using the mysqldump command-line utility or third-party tools like dbForge Studio for MySQL.

With dbForge Studio for MySQL, you can transfer data using one of the following features:

Backup and Restore
Copy Database
Export/Import

However, prior to migrating MySQL data, schemas, and/or databases:

Make sure that you have corresponding rights and privileges
Drop the database with the same name on the destination server
When using the Copy Database functionality, make sure that a version of the target MySQL instance is the same or higher than that of the source one

Migrate data between different instances

dbForge Studio for MySQL can be used to migrate data between the following server instances:

MySQL server versions 5.5-8.0
MariaDB
Percona PAM Authentication for MySQL
Amazon RDS for MariaDB

You can check the complete list of servers supported by the tool at Broad Compatibility Options of dbForge Studio for MySQL.

In addition, the Export/Import functionality built into dbForge Studio for MySQL enables you to use different data sources and export formats, and transfer data to SQL Server (using load script generation).

Transfer databases with the command line

In MySQL, you can migrate databases between servers using the mysqldump command-line utility. It generates a single MySQL database backup file with a set of logically connected SQL statements.

To back up a MySQL database, you need to execute the mysqldump command. After a dump file has been created, you can copy the database to the destination server. Make sure that you have created an empty database with a unique name on the target server. Once done, you can execute the mysql command to restore the database. For more information, see Different Ways to Back up MySQL Databases and Tables.

Note that it is much easier and faster to back up/restore databases using a third-party tool, such as dbForge Studio, that requires a few clicks to transfer a MySQL database.

How to migrate a MySQL database to a new server

This method is the most simple and requires the fewest clicks. To copy a MySQL database to a new server, you need to perform the following steps:

Go to the Database menu and select Copy Databases.
On the Copy Database page that opens, select the source and target connections.
Select the required databases and the Include Data and Drop if exists on target checkboxes if needed.
Click the settings icon at the top right corner of the Copy Database page.
In the Copy Databases Settings dialog, make sure that all necessary options are selected as needed.
To save the changes, click OK.

To start the migration process, click the green arrow icon. Once the process is over, you will see the migration status notification. Learn more about the process of copying MySQL databases.

Duplicate a database to another server

To back up a MySQL database:

On the Database menu, select Backup and Restore > Backup Database.
This will open the Database Backup Wizard.
On the Database Backup Wizard > General page, select the source instance connection, the database you want to back up, and specify the path to the backup folder. Then, switch to each page to configure optional settings. When all necessary options are set up, click Next.
To start the backup process, click Backup. After the process is complete, you will see the progress status and can open the output file by selecting Open Script.
Click Finish.

Restore a database backup on the destination server

Now that we have a database backup, we can restore it on a different server.

On the Database menu, select Backup and Restore > Restore Database.
This will open the Database Restore Wizard.
On the Database Restore Wizard > Database Script File page, select the source instance connection and specify the name of the restored database, the path to the backup file, and, if needed, SQL file encoding.
To start the restoration process, click Restore.
When the process is successfully finished, you will see that the database was restored on the target instance.

Learn more about the database backup process.

Bi-directional data transfer: Data Export

You can also use the Data Import and Export functionality built into dbForge Studio for MySQL to transfer databases between servers. To export data:

On the Database menu, select Export Data.
This will open the Data Export [File Format] Wizard.
In the Data Export Wizard, choose an export format and click Next. Then, switch to each page to select a source table you want to export, specify an export file name, set table options, select columns for export, and configure additional options.
The export template can be saved for later use in the Wizard or from the command line.
To launch the export process, click Export. After the process is complete, you will see the progress status and can open the output file by selecting Open Script.
Click Finish.

Bi-directional data transfer: Data Import

After the file has been created, you can import it to the target server by performing the following steps:

On the Database menu, select Import Data.
This will open the Data Import from [File Format] Wizard.
In the Data Import Wizard, select a file to import and click Next. Then, on each page of the wizard, you need to choose a destination table for import and set up import options, data formats, output script, and error handling behavior with logging options.
The wizard also allows you to save import templates.
To start the import process, click Import.

Learn more about data import and export capabilities.

The fastest way to transfer databases in MySQL:
dbForge Studio for MySQL

dbForge Studio for MySQL provides a quick and simple way that does not require coding to migrate MySQL data and/or database from one server, such as MySQL, MariaDB, Amazon Aurora, etc.

Watch the video to see how to transfer databases from the source to the destination server in the easy-to-use graphic interface with the following functionalities:

Copy Databases
Database Backup and Restore Wizards

Why choose our migration tools for MySQL?

As you can see, dbForge Studio for MySQL has robust database migration capabilities for different cases and scenarios. The most simple way to transfer MySQL databases is to use the Copy Database functionality - only a few clicks are required in this case. However, other ways, such as Import/Export and Backup can be used to achieve slightly more control over the migration process. You can try this out for yourself by downloading dbForge Studio for MySQL and testing the functionality as described in this guide.

Availability in the editions of dbForge Studio for MySQL

Feature

Enterprise

Professional

Standard

Express

Copy Databases

Database Backup and Restore Wizards

Data Import and Export

^* The following functionality is not supported: backup projects, auto-deletion of old backup files, compression, and creation of backup files larger than 1MB.

^** CSV format has no restrictions for data export and import; for other formats, only first 50 rows are processed. User templates are not supported.

Database Tools

Data Connectivity

Productivity Tools

Data Services

About us

Resources

Community