How to Migrate MySQL Databases Using Migration Tools

The sphere of database administration often causes significant challenges for developers and DBAs. The database design process with an efficient structure and relationships can be complex and lead to errors and inefficiencies. To manage large volumes of data, it's necessary to keep data consistent and relevant all the time. This, in turn, requires regular database backups, performance monitoring, and user management. Additionally, executing routine database operations, such as querying and indexing, can be time-consuming and error-prone without the right tools. dbForge Studio for MySQL has everything to cope with these problems: an intuitive visual designer for easy database creation, powerful data editing and import/export tools for seamless data management, and powerful performance monitoring and backup features for efficient database administration.

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 up to the latest version
MariaDB server up to the latest version
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. Additionally, dbForge Studio supports Kamatera MySQL Cloud and HeatWave to provide seamless integration and enhanced performance for cloud-based database solutions. You can try this out yourself by downloading the tool and testing the functionality 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.

Frequently Asked Questions

How to transfer all MySQL databases from an old server to a new one?

To transfer all databases to another server, follow these steps:

1. Open dbForge Studio for MySQL, in the Database Explorer, select the required databases by pressing Ctrl + Shift.

2. Make a right click and navigate to Tasks > Copy Databases. Then, pick the desired target server from the dropdown menu.

3. Optionally, click the gear icon to change the default Copy Database Settings. For instance, you can change the maximum number of simultaneous database copings, include or exclude triggers, partition clause, etc.

4. To copy data along with schema, select a checkbox in the Include Data column next to a database you want to copy.

5. Optionally, you can modify the name of the target database. For this, click the database name in the Target column of the window and type a new name.

6. Click the green arrow icon to start copying the selected databases to the target server, and wait until the process is complete.

How to move data from one database to another in MySQL?

In dbForge Studio for MySQL, you can easily transfer data from one database to another using the backup and restore method. This approach is appropriate for complete data migration. However, if you only need to move specific data, this method is not appropriate, as it replaces all existing data in the target database. For partial data transfer, you can use the functionality of dbForge Data Compare for MySQL.

To move all data from one database to another:

1. In the Database Explorer, right-click the database you want to move data from and go to Tasks > Backup Database.

2. Customize all the required options and click Backup.

3. After the process is completed, click Finish.

4. In the Database Explorer, right-click the database you want to insert data and navigate to Tasks > Restore Database.

5. Finally, click Finish.

How to import MySQL data to another database?

dbForge Studio for MySQL allows importing data in the following file formats: Text, MS Excel, MS Excel 2007, Google Sheets, MS Access, XML, JSON, CSV, ODBC, and DBF.

1. Navigate to Database > Tasks > Import Data.

2. Select the corresponding file and click Next.

3. Choose the database where you want to import data from Database. If you want the data to be added to a new table, type its name in the New table field. To import data into an existing table, select Existing table and choose the desired table from the list.

4. To continue adjusting other options, click Next. To import data immediately, click Import.