How to copy a MySQL or MariaDB database

Copying a MySQL or MariaDB database makes it possible to reproduce production-like data for testing, create a staging environment, or take a snapshot before risky schema changes. A clean clone keeps the original database intact while providing a safe place to validate migrations, fixes, and performance changes.

A database copy is typically performed as a logical export followed by a logical import. mysqldump reads schema objects and table rows from the source database, writing portable SQL that the mysql client can replay into a different database name on the same server or on another server.

Live database dumps have a few sharp edges. Consistency depends on storage engine and dump options (InnoDB works well with –single-transaction), dump files can be large enough to fill disks, and logical copies do not include server-level objects like users and grants. SQL dumps frequently contain sensitive data, so file permissions and storage location matter as much as the commands.

1. Read the source database default character set and collation.

   $ mysql --user=root --password
   Enter password: ********
   mysql> SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
       -> FROM information_schema.SCHEMATA
       -> WHERE SCHEMA_NAME = 'original_database';
   +-------------------+----------------------------+------------------------+
   | SCHEMA_NAME       | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
   +-------------------+----------------------------+------------------------+
   | original_database | utf8mb4                    | utf8mb4_unicode_ci     |
   +-------------------+----------------------------+------------------------+
   1 row in set (0.00 sec)
   mysql> EXIT
   Bye

   Replace original_database with the database name being copied.

2. Create an empty destination database using the recorded character set and collation.

   $ mysql --user=root --password
   Enter password: ********
   mysql> CREATE DATABASE cloned_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
   Query OK, 1 row affected (0.01 sec)
   mysql> EXIT
   Bye

   Importing into an existing database can drop or overwrite tables depending on dump contents.

3. Dump the source database to a SQL file with mysqldump.

   $ mysqldump --user=root --password --single-transaction --quick --routines --events --triggers original_database > ~/original_database.sql
   Enter password: ********

   –single-transaction is designed for InnoDB; non-transactional tables may not be copied from a perfectly consistent point in time.

4. Confirm the dump file exists and is non-empty.

   $ ls -lh ~/original_database.sql
   -rw-r--r-- 1 user user 28M Dec 12 10:42 /home/user/original_database.sql

5. Restrict access to the dump file.

   $ chmod 600 ~/original_database.sql
   $ ls -l ~/original_database.sql
   -rw------- 1 user user 29360128 Dec 12 10:42 /home/user/original_database.sql

   World-readable SQL dumps can expose credentials, emails, tokens, or other sensitive application data.

6. Import the dump into the destination database.

   $ mysql --user=root --password cloned_database < ~/original_database.sql
   Enter password: ********

   Remote targets can be reached by adding –host and –port to both mysqldump and mysql.

7. Verify tables, row counts, and database objects in the cloned database.

   $ mysql --user=root --password cloned_database
   Enter password: ********
   mysql> SHOW TABLES;
   +-----------------------------+
   | Tables_in_cloned_database   |
   +-----------------------------+
   | customers                   |
   | orders                      |
   | products                    |
   +-----------------------------+
   3 rows in set (0.00 sec)
   
   mysql> SELECT COUNT(*) AS customers FROM customers;
   +-----------+
   | customers |
   +-----------+
   |      1243 |
   +-----------+
   1 row in set (0.02 sec)
   
   mysql> SELECT ROUTINE_TYPE, COUNT(*) AS count
       -> FROM information_schema.routines
       -> WHERE ROUTINE_SCHEMA = DATABASE()
       -> GROUP BY ROUTINE_TYPE;
   +-------------+-------+
   | ROUTINE_TYPE | count |
   +-------------+-------+
   | PROCEDURE    |     5 |
   | FUNCTION     |     2 |
   +-------------+-------+
   2 rows in set (0.00 sec)
   
   mysql> SELECT COUNT(*) AS triggers FROM information_schema.triggers WHERE TRIGGER_SCHEMA = DATABASE();
   +----------+
   | triggers |
   +----------+
   |        3 |
   +----------+
   1 row in set (0.00 sec)
   
   mysql> SELECT COUNT(*) AS events FROM information_schema.events WHERE EVENT_SCHEMA = DATABASE();
   +--------+
   | events |
   +--------+
   |      1 |
   +--------+
   1 row in set (0.00 sec)
   mysql> EXIT
   Bye

8. Optionally copy schema only by dumping without data.

   $ mysqldump --user=root --password --no-data --routines --events --triggers original_database > ~/original_database.schema.sql
   Enter password: ********

   Importing original_database.schema.sql into an empty destination database creates tables, views, routines, triggers, and events without copying rows.