How to copy a MySQL or MariaDB database

Cloning a MySQL or MariaDB database is a useful process when you need to duplicate a database for testing, development, or migration purposes. Cloning can be done using a variety of methods, such as exporting and importing a database with mysqldump, or copying database files directly if both instances are on the same server. The most common and flexible method is using mysqldump, which creates a logical backup of the database that can be imported into a new database.

When cloning, the original database is copied to a new database with a different name. This ensures that both databases are separate, but have identical data structures and contents. This approach is especially useful when duplicating production databases for testing or troubleshooting.

This article provides a step-by-step guide to clone a MySQL or MariaDB database using mysqldump, ensuring that the clone database is an exact copy of the original database.

1. Create a new, empty database where the clone will be stored.

   $ mysql -u root -p
   Enter password: ********
   mysql> CREATE DATABASE cloned_database;
   Query OK, 1 row affected (0.01 sec)

   Replace “cloned_database” with the name of the new database where the data will be cloned.

2. Use mysqldump to export the original database to a SQL file.

   $ mysqldump -u root -p original_database > /path/to/backup/original_database.sql
   Enter password: ********

   Replace “original_database” with the name of the database you want to clone, and adjust the file path as needed.

3. Import the SQL file into the new cloned database.

   $ mysql -u root -p cloned_database < /path/to/backup/original_database.sql
   Enter password: ********

   This step restores the SQL dump into the new cloned database.

4. Verify that the clone has been created successfully by listing the tables in the new database.

   $ mysql -u root -p
   Enter password: ********
   mysql> USE cloned_database;
   Database changed
   mysql> SHOW TABLES;
   +-------------------+
   | Tables_in_cloned  |
   +-------------------+
   | table1            |
   | table2            |
   | table3            |
   +-------------------+
   3 rows in set (0.00 sec)

   Ensure that all tables from the original database have been copied to the cloned database.

5. Optionally, if the databases are on the same server, you can copy the database directly by creating a new database and using the original database as a template.

   mysql> CREATE DATABASE cloned_database LIKE original_database;

   This method creates an empty cloned database with the same structure as the original database but without data.