Best Way to Copy MySQL Databases

The Copy Databases functionality is the fastest way to create multiple copies of MySQL databases in a short period of time.

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.

  • Select a Source and Target servers
  • Specify 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

Select Source and Target servers

1. In dbForge Studio for MySQL, on the Start Page > Administration tab, click Copy Databases.

2. Choose a source server to copy databases from. The list of databases hosted in the MySQL source server will be displayed in 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.

Selecting source and target servers

Include data

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

6. To avoid duplication of databases on the target server, select the corresponding checkboxes in the Drop if exists on target column. Keep in mind that 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. If you delete "_copy" from the database name and the name will be the same as a database name in the Source column, the corresponding Drop if exists on target checkbox will be selected. If you clear that checkbox, "_copy" will be added to the database name.

How to avoid duplicating data in MySQL database

Set up copying

8. In the top-right corner, click the Settings icon to open the Copy Databases Settings dialog, where you can configure the following parameters of copying data from one database table to another:

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

Start copying

9. After having set up the target and source, as well as the copying settings, click the Start copying databases Start copying databases arrow to run the copying process. In the Progress column, you will see a progress bar with the percentage of completion. After that the result of the copying and the time spent on the process will be displayed in the Elapsed time column.

10. At any time of copying, you can cancel the process by clicking the Abort copying databases Abort copying icon.

MySQL database copying

Conclusion

The Copy Databases tool is the right solution when you need to quickly transfer MySQL databases between two different server instances or clone multiple MySQL databases simultaneously. With a simple and clear interface, you can create a test database, as well as easily identify duplicate data and remove it on the target database. Thus, using the tool built into dbForge Studio for MySQL can significantly reduce time and resources on database provisioning.

dbForge Studio for MySQL

All-in-One SQL Server GUI Tool

Availability in the editions of dbForge Studio for MySQL

Feature

Enterprise
Professional
Standard
Express
Copy Databases
Yes
None
None
None