How to clone a MySQL or MariaDB database

Cloning a database means creating an exact copy of an existing database, typically to run tests or for backup purposes. Both MySQL and MariaDB, while being separate database systems, originated from the same codebase and offer similar functionalities. This similarity extends to the process of cloning databases as well.

Many developers and administrators rely on database cloning for various reasons, from development testing to backup strategies. Regardless of the motivation, having a reliable and efficient cloning process is crucial for these systems.

With MySQL and MariaDB, you can use simple command-line utilities or structured query language (SQL) statements. This guide will cover how to clone a database using both the command line and SQL-based methods.

1. Log in to the MySQL or MariaDB server using the mysql client.

   $ mysql -u root -p
     - Enter password:

   You need the appropriate privileges to access both the source and destination databases.

2. Create a backup of the source database using mysqldump tool.

   $ mysqldump -u root -p source_database > source_database.sql
   Enter password:

3. Create the destination database within the MySQL or MariaDB client.

   mysql> CREATE DATABASE destination_database;

4. Exit the MySQL or MariaDB client.

   mysql> exit

5. Restore the source database backup to the destination database.

   $ mysql -u root -p destination_database < source_database.sql
   Enter password:

   This step will clone all data and structures from the source to the destination database.

6. Verify the cloning process by logging back into the MySQL or MariaDB client and checking the data in the destination database.

   $ mysql -u root -p
   Enter password:
   mysql> USE destination_database;
   mysql> SHOW TABLES;

   This will list all tables in the destination database. Cross-check them against the source database to ensure completeness of cloning.

7. Once verified, you can safely delete the source_database.sql file.

   $ rm source_database.sql

   Always make sure you have backup copies of important data before deleting any files.

Your database should now be successfully cloned from the source to the destination. Regularly backup your databases and consider automating the cloning process if it's a recurring task for you.