How to Transfer MySQL Databases


There is often a need to migrate MySQL databases - be it to transfer the 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, as most popular MySQL-compatible cloud databases, storage and search engines are supported - including MariaDB, AmazonRDS, Amazon Aurora etc.
Databases can be transferred either within the same instance or between different instances, and there are three ways in which you can approach this:

  • Copy MySQL Database
  • Backup and Restore MySQL Database
  • Import/Export

Let's look at each one of them in more detail.

Copy MySQL Database

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

  1. Go to the Database menu and select the Copy Database option.
  2. In the Copy Database tab you just opened, select the source and target connections.
  3. Select the required databases and toggle on the Include Data and Drop if Exists on Target checkboxes if it's needed.
Copy MySQL Database
  1. Click on the gear icon at the top right corner of the Copy Database tab.
  2. In the Copy Databases Settings window, make sure that all necessary options are toggled as needed.
  3. When all necessary options are selected, click OK.
Copy Databases Settings
  1. Click the green arrow icon to start the migration process.
    While migration is in process, you will see the operation's progress bar and the amount of elapsed time.
    Once the process is over, you will see the migration status notification. If the migration is successful, you will be able to see that the database was added to the target instance.

Learn more about the process of copying MySQL databases.

Completed Migration Process

Backup and Restore MySQL Database

To backup a MySQL database and restore it on a different server, you will need to:

  1. Go to the Database menu, then to Backup and Restore, and choose Backup Database....
    This will open the Database Backup Wizard.
  2. In the Database Backup Wizard's General tab, select the source instance connection, the database you want to backup, the path to the backup folder and other optional settings.
    When all necessary options are set up, click Next
Backup and Restore MySQL Database
  1. In the Backup Content tab, select the object types and specific objects you want to backup. Then, click Next
  2. In the Options tab, specify how exactly the backup process will be carried out by toggling all necessary parameters. When it's done, click Next
  3. In the Errors handling tab, choose the desired error processing behavior and logging options.
  4. To start the backup process, click Backup
  5. When the process is finished, the operation's status will be shown and you will have an option to open the result file by toggling Open Script.
    Click Finish.
Database Backup Wizard
  1. Now that we have a backup of the database, we can restore it on a different server.
    First, go to the Database menu, then to Backup and Restore, and choose Restore Database....
    This will open the Database Restore Wizard.
  2. In the Database Restore Wizard's Database Script File tab, select the source instance connection, the name that should be given to the restored database, the path to the backup file, and, if needed, the SQL file encoding.
  3. To start the restoration process, click Restore
  4. When the process is successfully finished, you will be able to see that the database was restored on the target instance.

Learn more about the database backup process.

Database Restore Wizard

Import/Export

You can also use dbForge Studio for MySQL's built-in data import and export functionality to transfer databases between servers. To do this, you will need to:

  • Export the data of the desired database to one of 14 supported formats - a specialized Wizard will guide you in this process. You can select specific columns and range of rows for export and configure other options.
    The export template can be saved for later use in the Wizard or via the command line.
Data Export Wizard
  • When the file is successfully created, import it as a database to the target server. 10 formats are supported for import and, once again, the process is performed in a Wizard which allows you to save import templates. You will be able to import the data either to a new or to an existing table, with various options such as different import modes and error handling behavior.

Learn more about data import and export capabilities.

Data Import Wizard

Conclusion

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, the other ways, such as Import/Export and and Backup we covered here 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 described in this guide.

dbForge Studio for MySQL

All-in-One MySQL GUI Tool

Download

Availability in the editions of dbForge Studio for MySQL

Feature

Enterprise
Professional
Standard
Express
Copy Databases
Yes
None
None
None
Database Backup and Restore Wizards
Yes
Yes
Yes
Click to view restrictions
Data Import and Export
Yes
Yes
Yes
Click to view restrictions

* 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.