How to copy or clone tables in MySQL or MariaDB

Cloning a table in MySQL or MariaDB provides a quick way to test schema changes, validate queries against real data, or stage bulk updates without touching the original rows.

A practical clone is usually a two-step SQL workflow: CREATE TABLE … LIKE copies the table definition into a new, empty table, and INSERT INTO … SELECT populates it by reading from the source table.

Clones created with these statements are not perfect replicas of every related database object. FOREIGN KEY constraints and triggers are not copied by CREATE TABLE … LIKE, and large INSERT … SELECT operations can be slow, consume disk space, and generate large binary logs on replicated servers.

1. Open the MySQL or MariaDB client as a user with permission to create tables.

   $ mysql -u root -p
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 9
   Server version: 8.0.32-0ubuntu0.22.10.2 (Ubuntu)
   Copyright (c) 2000, 2023, Oracle and/or its affiliates.
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   mysql>

   Required privileges typically include SELECT on the source table and CREATE, INSERT on the destination table.

2. Select the database containing the source table.

   mysql> USE employees;
   Reading table information for completion of table and column names
   Database changed

3. Verify the existence of the source table.

   mysql> SHOW TABLES;
   +----------------------+
   | Tables_in_employees  |
   +----------------------+
   | current_dept_emp     |
   | departments          |
   | dept_emp             |
   | dept_manager         |
   | employees            |
   | salaries             |
   | titles               |
   +----------------------+
   8 rows in set (0.01 sec)

4. Record the source table row count for verification.

   mysql> SELECT COUNT(*) AS src_rows FROM employees;
   +----------+
   | src_rows |
   +----------+
   |   300024 |
   +----------+
   1 row in set (0.02 sec)

5. Confirm the destination table name is unused.

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

   Dropping an existing table to reuse a name permanently deletes its data.

6. Create a new table by copying the source table structure.

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

   CREATE TABLE … LIKE copies columns and indexes, but does not copy FOREIGN KEY constraints, triggers, DATA DIRECTORY, INDEX DIRECTORY, or data.

7. Verify the new table structure.

   mysql> DESCRIBE employees_new;
   +------------+-------------+------+-----+---------+-------+
   | Field      | Type        | Null | Key | Default | Extra |
   +------------+-------------+------+-----+---------+-------+
   | emp_no     | int(11)     | NO   | PRI | NULL    |       |
   | birth_date | date        | NO   |     | NULL    |       |
   | first_name | varchar(14) | NO   |     | NULL    |       |
   | last_name  | varchar(16) | NO   |     | NULL    |       |
   | gender     | char(1)     | NO   |     | NULL    |       |
   | hire_date  | date        | NO   |     | NULL    |       |
   +------------+-------------+------+-----+---------+-------+
   6 rows in set (0.02 sec)

   Index details are visible with

   mysql> SHOW INDEX FROM employees_new;

8. Copy the data from the original table to the new table.

   mysql> INSERT INTO employees_new SELECT * FROM employees;
   Query OK, 300024 rows affected (0.80 sec)
   Records: 300024  Duplicates: 0  Warnings: 0

   On large tables, the insert can run for a long time, fill disks, and create large binary logs on replicated servers.

9. Verify that the row counts match between source and destination.

   mysql> SELECT (SELECT COUNT(*) FROM employees) AS src_rows,
       ->        (SELECT COUNT(*) FROM employees_new) AS dst_rows;
   +----------+----------+
   | src_rows | dst_rows |
   +----------+----------+
   |   300024 |   300024 |
   +----------+----------+
   1 row in set (0.04 sec)

   Cross-schema clones on the same server use fully qualified names, e.g.,

   mysql> CREATE TABLE archive.employees_copy LIKE employees.employees;

10. Preview a few rows from the cloned table.

    mysql> SELECT * FROM employees_new LIMIT 5;
    +--------+------------+------------+-----------+--------+------------+
    | emp_no | birth_date | first_name | last_name | gender | hire_date  |
    +--------+------------+------------+-----------+--------+------------+
    |  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
    |  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
    |  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
    |  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
    |  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
    +--------+------------+------------+-----------+--------+------------+
    5 rows in set (0.01 sec)