MySQL is one of the most popular Database Management Systems (RDBMS) and is used in a wide range of industries ranging from Business Intelligence (BI) applications to e-commerce websites.
Whatever the application, creating a copy of the database instance is a common activity that is carried out by data engineers on a regular basis. And it can be a daunting task – if you don’t have the right tool, that is.
In this article, we will discuss three popular approaches for copying MySQL databases. First, we will look into using dbForge Studio for MySQL, a comprehensive tool for controlling and running MySQL databases. We will also discuss how phpMyAdmin, a free open-source web-based tool, can be used for the task. Lastly, we will talk about how a simple command-line tool, mysqldump, enables you to replicate MySQL databases.
Way 1: Using dbForge Studio for MySQL
dbForge Studio is a powerful IDE for managing, developing, and administrating MySQL databases. Its no-code environment and powerful features allow database developers to perform everything from querying data to synchronizing databases and performing automating backups.
In addition to its compatibility with MySQL, dbForge also works with Microsoft Azure, Google Cloud, and other renowned database engines.
Let’s see how you can create a MySQL database clone in dbForge Studio for MySQL.
- Start by first selecting the “Copy Databases” command from the “Database” drop-down menu inside dbForge Studio for MySQL.
- Next, select the source and target connections on the “Copy Databases” screen.
- Once the source and destination connections are selected, their databases will be displayed. Select any database to copy its schema from the source to the destination.
To copy the data as well, you can check the ‘Include Data’ option.
You can also select the “Drop if exists on target” checkbox to cancel the copying process if the database already exists at the destination.
- To customize the settings of the copying process, click the gear icon on the top right corner of the window.
- Now, click the green arrow between the source and destination and the process to copy MySQL tables to another database begins.
- You can also rename the target database’s name by double-clicking its name and typing in the new one.
- When the process completes, you’ll see the “Complete” message in the Progress column.
Common Issues
As you get started with the copying process, you might face some issues. Below, we have listed some of the common ones along with their solutions.
- Connection Issues: Interrupted or broken connections disrupt the cloning process. To resolve this issue, check the network connection and ensure that the MySQL server is up and running.
- Database Corruption: Check the original database for corruption before beginning as it will lead to a corrupted clone.
Method 2: Using phpMyAdmin
PhpMyAdmin is an open-source, PHP-based software developed to administer and manage MariaDB and MySQL databases on the web. It comes preinstalled in many web hosting servers and has become a highly-used tool because of its user-friendliness and versatile browser compatibility.
With phpMyAdmin, you can perform everything from creating to cloning to deleting databases.
Follow the below steps to clone MySQL database to another database.
- Log into your phpMyAdmin, click the database you want to copy.
- Next, select the “Operations” tab from the toolbar.
- In the “Operations” tab, you will find the option to copy the database. Specify the name of the new database, set the options as per your requirements, and click Go.
- Once the process is completed, your new database will appear in the left menu.
Common Issues when working with phpMyAdmin
- Server Connection Issues: Make sure your internet connection is stable and the database server is working correctly. A bad connection may cause your MySQL database clone process to fail.
- Compatibility Issues: Databases on incompatible engines cannot be cloned. Therefore, ensure that your source and target engine are compatible.
- Exceeding Time Limit: Sometimes, large copying time periods can result in the failure of the cloning process. You can increase the time limit by editing the set_time_limit() function in the PHP script.
Method 3: Using the mysqldump Command
mysqldump is a command line tool for duplicating MySQL databases. It saves the entire MySQL database as a file to create a database later and allows component cloning as well. The general syntax of the command is as follows:
| > mysqldump -u [user_name] -p [database_name] > [dumpfilename.sql] |
Let’s consider a hypothetical scenario in which you have to copy a MySQL database ‘dbmodels’ to a new database named ‘dbmodels_backup’.
- First, use the following command to log into your MySQL server.
| > mysql -u root -p Enter password: ********** |
- Now create a new database by using CREATE_DATABASE command.
| > CREATE DATABASE dbmodels_backup; |
- To verify that the database has been created, run the SHOW DATABASE command.
- Now, use the mysqldump command to move database objects and data to an SQL file.
Let’s assume that you want to create a backup in ‘E:’ drive on a Windows machine. In this case, the statement will be:
| > mysqldump -u root -p dbmodels > e:\db\dbmodels.sql Enter password: ********** |
The above statement directs the mysqldump command to sign into the root account and export the components of the database ‘dbmodels’ database to e:\db\dbmodels.sql.
- Next, import the dbmodels.sql file to dbmodels_backup database.
| > mysql -u root -p dbmodels_backup < e:\db\dbmodels.sql Enter password: ********** |
- x
x
| > SHOW TABLES FROM dbmodels_backup; |
Common Issues
- Components Locks: Remove table locks and other access restrictions before cloning. Otherwise, the cloning process may fail.
- Disk space: You need sufficient space on the destination instance for cloning. In case of insufficient space, the clone will be incomplete or fail completely.
- Incorrect Syntax: Since mysqldump is a command-line utility, ensure that the syntax is correct. Also, make sure to specify the essential arguments and parameters to create a successful clone.
Conclusion
To wrap things up, creating a MySQL database clone is a common task database managers perform to prevent unrecoverable damage to their data.
In this article, we looked at three common ways to duplicate a MySQL database. These included GUI-based tools, dbForge Studio for MySQL, and phpMyAdmin as well as a command-line utility, mysqldump.
