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.

Copy Databases is an essential feature of Devart's MySQL IDE that helps 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 source and target servers
Specify databases to copy
Select whether to copy the contained data
Drop a database if it already exists on the target server
Configure the parameters of database copying
See the time and progress of copying in percents and abort the copying in case of need

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.

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

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 MySQL Windows client by Devart can significantly reduce time and resources on database provisioning.

Availability in the editions of dbForge Studio for MySQL

Feature

Enterprise

Professional

Standard

Express

Copy Databases