How to copy a MySQL database

February 14th, 2023

MySQL is a powerful tool for storing and organizing data, creating different “folders” (databases), and putting different types of information (tables) in them, similar to a digital filing cabinet. Sometimes you may need to copy database MySQL to back up data, migrate to a new server or hosting environment, or create a replica for load balancing and high availability.

To copy MySQL database is critical and can save time, money, and headaches in the long run. So whether you’re a developer or administrator or need to move a database from one place to another, this guide will give you the skills and knowledge needed.

Let’s get started!

Method 1: Using the mysqldump Command

The mysqldump command is a command-line tool that creates backups of MySQL databases. It contains all the SQL statements necessary to recreate the database, including moving MySQL copy tables to another database, loading all of the data, and creating all of the indexes. The resulting file can be used to restore the database later.

The mysqldump command has many options that can be used to customize the backup process. Some of the most commonly used options include:

username: Specifies the username to use when connecting to the MySQL server.
password: Specifies the password to use when connecting to the MySQL server.
databases: Specifies the names of the databases to backup. If this option is not used, all databases on the MySQL server will be backed up.
tables: Specifies the names of the tables to backup. If this option is not used, all tables in the specified databases will be backed up.
where: Specifies a WHERE clause to select the rows to backup

Here is how to copy using the mysqldump command:

1. Check if mysqldump is installed by running the command:

$ which mysqldump

2. Create an SQL dump file of the existing database using the mysqldump command:

mysqldump –user=username –password=password source_database > dump.sql

3. Create the target database where the dump.sql file will be imported. This can be done by logging in to your MySQL server and running the command:

CREATE DATABASE target_database;

4. Alternatively, you can use the mysqladmin command line tool to create the target database:

mysqladmin –user=username –password=password create target_database

5. Import the dump.sql file into the target database using the mysql command:

mysql –user=username –password=password target_database < dump.sql

6. To copy a database from one MySQL server to another, use the –host option in the mysqldump, mysqladmin, and mysql commands, specifying the IP address or hostname of the destination server

For example, suppose you want to create a database on host 192.168.2.2; this is how you issue the command:

mysqladmin –host=192.168.2.2 \
–user

Troubleshooting tips

Restart the service: Sometimes, a simple restart of the service or application can resolve the issue.
Check for updates: Make sure you are running the latest version of the software or service. Outdated software can cause issues, so check for updates and apply them before you copy the database to another database in MySQL.

Method 2: Using phpMyAdmin

phpMyAdmin is a tool that allows you to manage your MySQL easily through a web-based interface. With it, you create MySQL database clones by selecting the one you want to copy, giving a new name, and selecting if you want to copy the structure only or both structure and data.

To duplicate a MySQL database using phpMyAdmin, follow these steps:

Log in to your cPanel account.
Navigate to the phpMyAdmin page, which can usually be found in the Databases section.
Once in phpMyAdmin, select the database you wish to copy from the left-hand sidebar.
In the top menu, click on the “Operations” tab.
In the “Copy database to” field, type in the desired name for the new database.
Choose whether to copy the structure and data or just the structure by selecting the appropriate option.
Check the boxes for “CREATE DATABASE before copying” and “Add AUTO_INCREMENT value.”
Click “Go,” and phpMyAdmin will create a new database with the specified name and copy the chosen elements from the original database. The new database will then be listed in the left-hand sidebar.

Troubleshooting tips

Check for database size limit: Some hosting providers have a limit on the size of the databases that can be created.
Check for the correct database name: Make sure you’re specifying the correct name for the new database and that it doesn’t already exist.
Check for server connection: Ensure that your server is connected and running properly. If the connection is lost, it could cause issues while copying.

Using dbForge Studio for MySQL

dbForge for MySQL is a powerful MySQL development and management tool that enables developers to easily manage and administer MySQL databases.

Open the “Copy Databases” option from the Database menu, the Administration tab of the Start Page, or the Database Explorer shortcut menu.
Pick the source and target connections. Use the search text box to find the specific database you want to copy.
Optionally, you can click the “settings icon” to change the default copy settings, such as the number of simultaneous copies or including/excluding triggers.
Select the database(s) you want to copy. If you want to include data along with the schema, check the box next to the database name. Do this in the “Source column.”
Optionally, choose to drop existing databases on the target connection or rename the target databases.
Click the copy button to begin the process.
Wait until the progress is complete before closing the window.

Troubleshooting tips

Ensure that the source and target connections are correct: Double-check that you have selected the correct source and target connections before starting the copy process.
Check for database compatibility: Make sure that the source and target databases are compatible and can be copied without any issues.
Verify that the target server has enough space: Ensure there is enough space on the target server to accommodate the copied databases.

Conclusion

Copying a MySQL database can be done in a few different ways. One method uses the mysqldump command, a command-line tool that creates backups of MySQL databases. Another method is using phpMyAdmin, a web-based interface that allows for easy management of MySQL. Another is by using dbForge Studio for MySQL. If you run into issues while copying, consider troubleshooting by restarting the service and checking for updates.