MySQL is a strong tool for storing and organizing data, creating various “folders” (databases), and placing various types of information (tables), similar to a digital filing cabinet. To back up data, migrate to a new server or hosting environment, or set up a replica for load balancing and high availability, you may occasionally need to duplicate MySQL database.
It is crucial to because doing so can ultimately save you time, money, and headaches. In order to move a database from one location to another, whether you’re a developer or administrator, this guide will equip you with the necessary skills and knowledge.
Let’s start now!
Way 1. Making use of the mysqldump Command
A command-line tool used to back up MySQL databases is called mysqldump. It includes all the SQL instructions required to rebuild the database, such as moving MySQL copy tables to a different database, loading all the data, and building all the indexes. The database can later be restored using the resulting file.
There are a lot of options available when using the mysqldump command to customize the backup procedure. Several of the most popular choices include:
- specifies the username that should be used to connect to the MySQL server.
- specifies the password that should be used to connect to the MySQL server.
- specifies the names of the databases that need to be backed up. All databases on the MySQL server will be backed up if this option is not selected.
- Names of the tables to backup are specified in the table. In the absence of this option, all tables in the specified databases will be backed up.
- specifying a WHERE clause to pick the rows to backup
How to copy using the mysqldump command is shown below.
- Run the following command to see if mysqldump is set up: $ which mysqldump
- Use the mysqldump command to create a SQL dump file of the current database: mysqldump –user=[username] –password=[password] [source_database] > dump.sql
- Create the target database into which the import of the dump.sql file will go. To do this, log in to your MySQL server and issue the following command: CREATE DATABASE [target_database];
- As an alternative, you can create the target database using the mysqladmin command-line tool: mysqladmin –user=[username] –password=[password] create [target_database]
- Using the mysql command, import the dump.sql file into the target database: mysql –user=[username] –password=[password] [target_database] < dump.sql
- Use the –host option in the mysqldump, mysqladmin, and mysql commands to copy a database from one MySQL server to another, specifying the destination server’s IP address or hostname. For instance, if you wanted to create a database on host 192.168.2.2, you would issue the following command: mysqladmin –host=192.168.1.1 \–user
Tips for troubleshooting
- Restart the service: Depending on the situation, the problem may be solved by simply restarting the service or application.
- For updates, check: Ensure that the software or service you are using is updated. Before you copy the database to another database in MySQL, look for updates and install them. Outdated software can lead to problems.
Way 2. The use of phpMyAdmin
Through a web-based interface, phpMyAdmin is a tool that makes it simple to manage your MySQL database. By choosing the database you want to copy, giving it a new name, and choosing whether you want to copy the structure only or the structure and data, you can use it to create copies of MySQL databases.
Use phpMyAdmin to duplicate a MySQL database by following these steps:
- Open the phpMyAdmin page.
- Select the database you want to copy from the left-hand sidebar in phpMyAdmin.
- Select the “Operations” tab from the top menu.
- Enter the desired name for the new database in the “Copy database to” field.
- By selecting the appropriate option, you can decide whether to copy the structure alone or the structure and data.
- Check the boxes next to “Add AUTO INCREMENT value” and “CREATE DATABASE before copying.”
- By pressing “Go,” phpMyAdmin will copy the selected elements from the original database and create a new database with the given name. The left-hand sidebar will then display a list of the new database.
Tips for troubleshooting
- Check for a database size restriction. The size of the databases that can be created may be limited by some hosting providers.
- Verify the database’s correct name: Make sure the new database doesn’t already exist and that the name you’re using is correct.
- Check the server’s connection to make sure it is active and connected. Issues with copying could arise if the connection is lost.
Using dbForge Studio for MySQL
Developers can manage and administer MySQL databases with ease thanks to the robust dbForge Studio for MySQL development and management GUI tool.
- From the Database menu, the Administration tab of the Start Page, or the Database Explorer shortcut menu, select “Copy Databases.”
- Select the connections for the source and target. To locate the specific database you want to copy, use the search text box.
- The default copy settings, such as the number of simultaneous copies or including/excluding triggers, can optionally be changed by clicking the “settings icon.”
- Pick the database or databases you want to copy. Check the box next to the database name if you want to include data with the schema. In the “Source column,” do this.
- You can optionally decide to rename the target databases or remove any existing databases from the target connection.
- To start the copying process, click the button.
- Prior to closing the window, wait for the progress to be finished.
- Verify the accuracy of the source and target connections: Before beginning the copy process, make sure you have chosen the correct source and target connections.
- Verify that the source and target databases can be copied successfully by checking the databases’ compatibility.
- Make sure the target server has enough space by checking: Make sure the target server has enough room for the copied databases.
You can copy a MySQL database in a few different ways. One technique makes use of the command-line tool mysqldump, which backs up MySQL databases. Using phpMyAdmin, a web-based interface that makes it simple to manage MySQL, is another option. Using dbForge Studio for MySQL is an additional option. Consider restarting the service and checking for updates as a possible troubleshooting measure if you experience copying difficulties.