How to clone a MySQL or MariaDB table

Cloning a table in MySQL or MariaDB is a common task for database administrators and developers. This can be useful for testing purposes, backup, or transferring data from one table to another.

Cloning or copying a table requires creating a new table with the same structure as the source table and copying the data from the source table to the new table.

You can use SQL statements to clone a table in MySQL or MariaDB. The CREATE TABLE statement enables you to create a new table based on the structure of an existing table. The INSERT INTO statement allows you to copy the data from one table to another. You can quickly and easily clone a table in MySQL or MariaDB by combining these statements.

1. Log in to MySQL or MariaDB server using mysql client as administrative user or user with appropriate access.

   $ 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.
   
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
   mysql> 

2. Change the default database to the one that contains the table you want to clone.

   mysql> USE employees;
   Reading table information for completion of table and column names
   You can turn off this feature to get a quicker startup with -A
    
   Database changed

3. Check existing, source table to confirm.

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

4. Create a table with same structure as the source table.

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

   This will just create a new, empty table and does not copy the followings;

   • Foreign key definitions
   • DATA DIRECTORY
   • INDEX DIRECTORY
   • Data

   The target table could be within the same database or in a different database. If different, specify the database name in the query as the following:

   database_name.table_name

   Make sure you have appropriate permissions on the related databases and tables.

5. Verify the cloned table.

   mysql> DESCRIBE employees_new;

   This command will show the structure of the new table, allowing you to confirm its resemblance to the original table.

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

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

7. Check copied records to verify that the operation was successful.

   mysql> SELECT * FROM employees_new;
   +--------+------------+------------+-----------+--------+------------+
   | 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 |
   ##### snipped
   | 499997 | 1961-08-03 | Berhard    | Lenart    | M      | 1986-04-21 |
   | 499998 | 1956-09-05 | Patricia   | Breugel   | M      | 1993-10-13 |
   | 499999 | 1958-05-01 | Sachin     | Tsukuda   | M      | 1997-11-30 |
   +--------+------------+------------+-----------+--------+------------+
   300024 rows in set (0.10 sec)