Data copying and migration are essential database tasks that arise frequently. Fortunately, many tools are available to assist database users in performing these tasks, with dbForge Studio for MySQL standing out as one of the most versatile and reliable solutions on the market.
This article explores the data migration options offered by dbForge Studio for MySQL, highlighting how they allow users to achieve their goals quickly and efficiently.
Contents
Schema & Data Compare
The first option we’ll explore is comparison and synchronization of database schemas and table data. These two operations are standard in data management, allowing you to identify differences between relational databases and synchronize them. Most frequently, this functionality is needed in the following scenarios:
- Deploying changes from development to staging and production environments
- Maintaining schema consistency across various databases
- Migrating data from one platform to another
- Troubleshooting database problems
In practice, synchronization of databases can work as copying data from one database into another one.
What is Schema & Data Compare?
In dbForge Studio for MySQL, schema and data comparison operations are handled by two corresponding integrated tools: Schema Compare and Data Compare. With a user-friendly interface at your service, setting up a comparison requires just a few clicks, and the results are displayed in an easily readable way, with all the differences conveniently highlighted.
After you complete a comparison, the results show actions needed to align the target object with the source one. You can then synchronize these differences in several ways — individually, as a group, or all at once. Synchronization can be done either manually or automatically using the automatically generated scripts.
Suppose you need to transfer a table and its data between databases. In that case, Schema Compare lets you migrate the table structure along with all its elements, while Data Compare handles data migration.
Let’s explore how to migrate data by comparing and synchronizing databases in dbForge Studio for MySQL.
How to use Schema & Data Compare in dbForge Studio for MySQL
In our test case, we need to copy several tables with their data from one database into another one. To illustrate the process, we’ll use the popular sakila test database and its copy called sakila_temp, which does not have some particular tables. We are going to apply both Schema Compare to copy the table structures and then Data Compare to migrate the actual data, and both these tools are included in dbForge Studio for MySQL.
Open dbForge Studio for MySQL and navigate to Comparison > New Schema Comparison.
In the New Schema Comparison window, specify the databases to compare their structures. Set the original database as Source (it is sakila in our case) and the database to migrate data to as Target (it is sakila_temp). Click Compare.
The comparison results show that the target sakila_temp database misses some tables.
Click the green arrow on the top to start the schema synchronization process that will copy the sakila schema objects into the sakila_temp database. When the task is complete, refresh the schema comparison results window. You can see that the schemas are identical, and the sakila_temp database includes the three tables it initially missed.
The next step after synchronizing the schemas is to synchronize the data. Click Comparison > New Data Comparison.
Similarly to the Schema Compare task configuration, set the Source and Target for comparison and click Compare. You can see the comparison results showing empty tables in the sakila_temp database.
Click the green arrow to copy data from sakila to sakila_temp. As soon as the task is complete, you can see the same data in the sakila_temp database.
In practice, configuring these tasks takes only several clicks. Flexible settings allow you to configure the comparison most precisely.
Benefits and use cases for Schema & Data Compare tools
Schema & Data Compare features in dbForge Studio for MySQL allow you to synchronize databases and validate changes. Overall, these tools provide the following benefits:
- Comparison and synchronization of live databases, backups, and script folders
- Efficient handling of large-scale database comparisons and synchronizations
- Flexible settings for precise data sync, down to individual rows
- Generation of reusable synchronization scripts
- Filtering and sorting of comparison results
- Production of comprehensive comparison reports
- Automation of tasks through the command line
- Deployment of changes to supported on-premise and cloud platforms
While copying data is one of the essential tasks, the primary focus is on managing different versions of databases across various environments — often as part of DevOps workflows.
Create Scripts Folder
Another option for data copying available in dbForge Studio for MySQL is via scripts folders. Let us examine it thoroughly.
Understanding the Create Scripts Folder feature
A scripts folder contains scripts designed to create objects representing a database schema and, optionally, data. Each object has its own script, organized into subfolders by object type. Additionally, these scripts may include various SQL commands for tasks like data manipulation, schema updates, data updates, and backups.
Database administrators and developers commonly use scripts folders for a range of purposes, specifically for version-controlling databases and comparing database schemas. Additionally, scripts folders can help copy and migrate data.
Using the feature in dbForge Studio for MySQL
The dedicated feature in dbForge Studio for MySQL helps create scripts folders quickly and effortlessly.
Open the Studio and navigate to Database > Tasks > Create Scripts Folder.
Specify the database you want to create the scripts folder for and define the path to store that folder. Make sure to specify an empty folder for storing the scripts or create a new folder for that task. If you want to create static data too, select the Include data checkbox.
To make sure that the structure is correct, click Scripts Folder Structure.
Click Create.
When the task is completed, you will have the collection of necessary scripts to create both the schema objects and data.
You can execute the scripts one by one using the preferred query execution tool thus recreating the database. dbForge Studio for MySQL offers the functionality to manage even very large scripts efficiently. However, this method is not the most convenient, especially when dealing with a large database containing hundreds and thousands of objects. Here, dbForge Schema & Data Compare can resolve this for you.
Both Schema and Data Comparison tools support script folders for the comparison and synchronization tasks. Therefore, you can use the script folder as Source with a desired database where you need to copy data to as Target.
In the New Schema Comparison wizard, select Script Folder as Source.
Browse to the necessary folder, specify additional parameters, and proceed as described earlier in the section dedicated to these tools’ work. After the synchronization, you will get the desired objects copied into the target database.
Repeat the same process with Data Comparison to transfer table data.
Advantages and best practices
In MySQL, scripts folders can be particularly useful for managing data migrations, backups, and synchronization processes. Here’s what this feature offers:
- Data migration between databases
- Backups with scripts that include SQL commands for creating dumps of entire databases or selected tables
- Synchronization of databases with scripts that align data across systems for consistency and accuracy
- Version control with a history of changes and the ability to revert to previous states
- Generation of database documentation, where scripts folders can serve as references for database configuration
Copy Database
dbForge Studio for MySQL provides the Copy Database feature that allows duplicating an entire database in a much faster and more straightforward manner, copying the entire schema and all its data.
Introduction to the Copy Database feature
Copy Database allows creating a full copy of a database under the same of different name, in the same or a different server, with one click. This functionality is exceptionally helpful for testing purposes, database development, backing up, and other tasks where it is essential to have an exact database copy at hand.
How to use Copy Database in dbForge Studio for MySQL
Open the Studio. Click Database > Tasks > Copy Database.
Define the Source and Target servers and choose the required database to copy. The names for the copied databases are set automatically by default, but they are editable. Define the desired name for the copied database in the Target section.
If you want to copy the entire database with all schema objects and table data, select Include Data.
Finally, click the green arrow on the top to start the copying process.
As soon as the database is copied, you will see it on the list of databases on the specified server, under the previously specified name.
Benefits and practical applications
The Copy Database feature is a highly efficient and reliable option for duplicating databases and their data. Compared to a combination of Schema Compare and Data Compare, it offers several essential advantages:
- Simultaneous transfer of both database schemas and data
- Recreation of databases from scratch, ensuring consistency and completeness
- Bulk copying, efficient handling of multiple databases
- Automated execution through CLI for streamlined workflows
With these benefits, Copy Database is an ideal solution whenever complete database duplication is required.
Conclusion
Data migration, often accomplished through data copying, is one of the most common tasks database administrators handle daily. Fortunately, there are several reliable methods to perform this operation efficiently. This article explores practical approaches involving dbForge Studio for MySQL. With its intuitive GUI, users can configure tasks visually, going from quick setup to smooth execution in a matter of moments.
dbForge Studio for MySQL is a tool designed to handle all database management, and administration aspects in MySQL and MariaDB environments. Its comprehensive functionality supports everything from writing high-quality SQL code to version control. You can explore its full functionality with a 30-day free trial. Install it today and see how it performs under real-world workloads!
