How to clone a table in MySQL or MariaDB

Cloning a table in MySQL or MariaDB gives you a disposable copy for query testing, schema rehearsal, or one-off bulk changes without touching the original rows.

The reliable clone workflow is a two-step SQL sequence. CREATE TABLE … LIKE builds an empty table from the source definition, and INSERT INTO … SELECT loads the rows after you have a chance to inspect the new table.

This is still a partial clone, not a full duplicate of every dependent object. The new table keeps columns and indexes, but it does not inherit foreign keys or triggers, and rerunning the data-copy step appends rows instead of refreshing the clone. Large copies also consume disk space and can generate substantial binary logs or replica lag on busy servers.

1. Open the MySQL or MariaDB client as a user that can read the source table and create the destination table.

   $ mysql -u root -p
   Enter password:
   Welcome to the MySQL monitor. Commands end with ; or \g.
   mysql>

   Typical privileges include SELECT on the source table plus CREATE and INSERT in the destination schema. Recent MariaDB packages may provide the mariadb client instead of mysql, but the SQL steps are the same.

2. Select the database that contains the source table.

   mysql> USE company;
   Database changed

3. Confirm the source table exists in the current database.

   mysql> SHOW TABLES LIKE 'employees';
   +-------------------------------+
   | Tables_in_company (employees) |
   +-------------------------------+
   | employees                     |
   +-------------------------------+
   1 row in set (0.00 sec)

4. Confirm the destination table name is unused.

   mysql> SHOW TABLES LIKE 'employees_clone';
   Empty set (0.00 sec)

   Dropping an existing table to reuse the same name permanently deletes its rows.

5. Create an empty clone from the source table definition.

   mysql> CREATE TABLE employees_clone LIKE employees;
   Query OK, 0 rows affected (0.01 sec)

   Stop here if you only need a structure-only copy.

6. Inspect the cloned definition before copying data.

   mysql> SHOW CREATE TABLE employees_clone\G
   *************************** 1. row ***************************
          Table: employees_clone
   Create Table: CREATE TABLE `employees_clone` (
     `id` int NOT NULL AUTO_INCREMENT,
     `first_name` varchar(100) NOT NULL,
     `last_name` varchar(100) NOT NULL,
     `department_id` int NOT NULL,
     `hired_on` date NOT NULL,
     `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
     PRIMARY KEY (`id`),
     KEY `idx_department_id` (`department_id`)
   ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
   1 row in set (0.00 sec)

   The final ENGINE, charset, and collation line can differ between MySQL and MariaDB, but CREATE TABLE … LIKE keeps columns and indexes. It does not copy foreign keys or triggers, so recreate those separately when the clone must keep the same relationships or trigger-based behavior.

7. Copy the source rows into the cloned table.

   mysql> INSERT INTO employees_clone SELECT * FROM employees;
   Query OK, 3 rows affected (0.00 sec)
   Records: 3  Duplicates: 0  Warnings: 0

   If employees_clone already contains rows, this statement appends another copy instead of refreshing the clone.

   Do not replace this with CREATE TABLE … SELECT when you need a closer table clone. That shortcut copies data, but it does not preserve indexes or AUTO_INCREMENT attributes unless you rebuild them manually.

8. Compare the source and clone row counts.

   mysql> SELECT (SELECT COUNT(*) FROM employees) AS src_rows,
       ->        (SELECT COUNT(*) FROM employees_clone) AS clone_rows;
   +----------+------------+
   | src_rows | clone_rows |
   +----------+------------+
   |        3 |          3 |
   +----------+------------+
   1 row in set (0.00 sec)

   Cross-schema clones on the same server use fully qualified names, for example CREATE TABLE archive.employees_clone LIKE company.employees; followed by INSERT INTO archive.employees_clone SELECT * FROM company.employees;

9. Preview rows from the cloned table.

   mysql> SELECT * FROM employees_clone ORDER BY id LIMIT 3;
   +----+------------+-----------+---------------+------------+---------------------+
   | id | first_name | last_name | department_id | hired_on   | created_at          |
   +----+------------+-----------+---------------+------------+---------------------+
   |  1 | Taylor     | Nguyen    |             1 | 2022-03-15 | 2026-04-09 22:04:21 |
   |  2 | Casey      | Morgan    |             2 | 2023-07-20 | 2026-04-09 22:04:21 |
   |  3 | Riley      | Singh     |             1 | 2021-11-02 | 2026-04-09 22:04:21 |
   +----+------------+-----------+---------------+------------+---------------------+
   3 rows in set (0.00 sec)