How to copy or clone tables in MySQL or MariaDB

Cloning a table in MySQL or MariaDB is a routine task for managing data in databases. It involves copying the structure and data of an existing table to create an identical or similar table in the same or different database. This is commonly done for backups, testing, or moving data.

In MySQL or MariaDB, cloning a table requires SQL commands that duplicate the table structure and data. The CREATE TABLE statement is used to copy the table structure, while the INSERT INTO statement transfers the data. Together, these SQL commands provide a straightforward way to clone a table, whether you need to make a copy within the same database or between databases.

This method ensures that the new table mirrors the original table without affecting the original data. However, certain aspects like foreign keys and index directories may not be copied automatically. Knowing how to properly execute these commands will help you efficiently copy or clone tables when needed.

1. Log in to the MySQL or MariaDB server using an administrative user or one with the necessary 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.
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   mysql>

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. Create a new table with the same structure as the source table.

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

   This will only create a new, empty table without copying foreign key definitions, DATA DIRECTORY, INDEX DIRECTORY, or data.

5. 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  | NO   |     | NULL    |       |
   | last_name  | varchar  | NO   |     | NULL    |       |
   | gender     | char(1)  | NO   |     | NULL    |       |
   | hire_date  | date     | NO   |     | NULL    |       |
   +------------+----------+------+-----+---------+-------+
   6 rows in set (0.02 sec)

6. 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

7. Verify that the data has been successfully copied.

   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)

   Ensure that you have the necessary permissions to copy tables within or between databases.