Previous Next

Best Way to Copy MySQL Databases

MySQL Copy Databases tool is a part of dbForge Studio for MySQL to help you move MySQL databases between different servers with a maximum speed. As a MySQL administrator, you will surely benefit from the tool as it provides the fastest and easiest way to copy database structure and data from source to target servers.

This tool lets you do the following:

  • Select a Source and Target servers
  • Select databases to copy
  • Select whether to copy data of databases
  • Drop a database if it already exists on a target server
  • Configure the parameters of database copying
  • See the time and progress of copying in percents and abort the copying

How to Copy MySQL Databases with dbForge Studio for MySQL

Below you will find a step-by-step guide on how to work with the Copy Databases tool.

Select Source and Target servers

1. The Copy Databases tool is on the Administration tab of dbForge Studio for MySQL.

2. Choose a source server to copy databases from. A list of databases hosted in the source server will fill the Source column.

3. Choose a target server to copy databases to. The names of databases from the Source column will be duplicated to the Target column. If the target server includes a database with the same name as in the source server, "_copy" is added to the database name.

4. In the Source column, select the databases you want to copy.

Include data

5. Once you've selected the databases, the corresponding fields in the Include Data and Drop if exists on target become editable. If you need to copy a database together with its data, then select the corresponding checkbox in the Include Data column.

6. To avoid duplication of databases in the target server, select the corresponding checkboxes in the Drop if exists on target column. This option works only for a database that has a counterpart on the target server.

7. Database names in the Target column can be edited. However, if you delete "_copy" from the database name and the name will coincide with a database name in the Source column, the corresponding Drop if exists on target checkbox will be selected. Moreover, if you clear that checkbox, "_copy" will again be added to the databse name.

Set up copying

8. Take a notice of the Settings icon in the top right that opens the Copy Databases Settings dialog box, where you can configure certain parameters of copying, such as:

  • Disable foreign keys
  • Exclude DEFINER and SQL SECURITY clauses
  • Flush logs
  • Include NO_AUTO_VALUE_ON_ZERO in script
  • Include partition clause
  • Include triggers
  • Lock all tables for read (Flush Read Locks)
  • Use a single transaction
  • Use bulk insert
  • Maximum number of simultaneous database copyings

Start copying

9. After having set up the target and source, as well as the copying seetings, click the green arror button to run the copying process. In the Progress column, you will see a progress bar with the percentage of completion and after that the result of the copying and the time spent on the process in the Elapsed time column.

10. At any time of copying, you can cancell the process by clicking the red square Abort copying button.

Conclusion

When you need to quickly transfer MySQL databases between two different server instances, the Copy Databases tool is the right choice for this! It has a simple and clear interface and it's very handy to use. The tool copes with cloning multiple MySQL databases in a few simple steps.