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:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 156
   Server version: 8.0.44 MySQL Community Server - GPL
   ##### snipped #####
   mysql> SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
       -> FROM information_schema.SCHEMATA
       -> WHERE SCHEMA_NAME = 'appdb';
   +-------------+----------------------------+------------------------+
   | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
   +-------------+----------------------------+------------------------+
   | appdb       | utf8mb4                    | utf8mb4_0900_ai_ci     |
   +-------------+----------------------------+------------------------+
   1 row in set (0.00 sec)
   mysql> EXIT
   Bye

   Replace appdb 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:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 157
   Server version: 8.0.44 MySQL Community Server - GPL
   ##### snipped #####
   mysql> CREATE DATABASE appdb_clone CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_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 appdb > /root/sg-work/backups/mysql/appdb-clone.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 /root/sg-work/backups/mysql/appdb-clone.sql
   -rw-r--r-- 1 root root 6.7K Dec 25 00:05 /root/sg-work/backups/mysql/appdb-clone.sql

5. Restrict access to the dump file.

   $ chmod 600 /root/sg-work/backups/mysql/appdb-clone.sql
   $ ls -l /root/sg-work/backups/mysql/appdb-clone.sql
   -rw------- 1 root root 6830 Dec 25 00:05 /root/sg-work/backups/mysql/appdb-clone.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 appdb_clone < /root/sg-work/backups/mysql/appdb-clone.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 appdb_clone
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 160
   Server version: 8.0.44 MySQL Community Server - GPL
   ##### snipped #####
   mysql> SHOW TABLES;
   +-----------------------+
   | Tables_in_appdb_clone |
   +-----------------------+
   | audit_log             |
   | customers             |
   | order_items           |
   | orders                |
   | users                 |
   +-----------------------+
   5 rows in set (0.00 sec)
   
   mysql> SELECT COUNT(*) AS customers FROM customers;
   +-----------+
   | customers |
   +-----------+
   |         3 |
   +-----------+
   1 row in set (0.00 sec)
   
   mysql> SELECT ROUTINE_TYPE, COUNT(*) AS count
       -> FROM information_schema.routines
       -> WHERE ROUTINE_SCHEMA = DATABASE()
       -> GROUP BY ROUTINE_TYPE;
   Empty set (0.00 sec)
   
   mysql> SELECT COUNT(*) AS triggers FROM information_schema.triggers WHERE TRIGGER_SCHEMA = DATABASE();
   +----------+
   | triggers |
   +----------+
   |        0 |
   +----------+
   1 row in set (0.00 sec)
   
   mysql> SELECT COUNT(*) AS events FROM information_schema.events WHERE EVENT_SCHEMA = DATABASE();
   +--------+
   | events |
   +--------+
   |      0 |
   +--------+
   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 appdb > /root/sg-work/backups/mysql/appdb.schema.sql
   Enter password:

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