How to clone tables in mysql?

Cloning operation in SQL allows the user to create the exact copy of an existing table along with its definition, that is completely independent from the original table. Thus, if any changes are made to the cloned table, they will not be reflected in the original table.

There are three types of cloning possible using SQL in MySQL RDBMS; they are listed below

1
Simple Cloning: Creates a new table containing same records of existing table but void of any constraints or indexes etc.
2
Shallow Cloning: Creates a new empty table with the same table definition of an existing table.
3
Deep Cloning: Creates a new table and copies the table structure and data of an existing table to the new table.

Simple Cloning means making a new table that contains the same data as an existing one. First, a new table is created using the CREATE TABLE statement. Then, data from selected columns in the existing table is copied into the new table using a SELECT statement.

The Shallow Cloning operation only copies the structure of the existing table into the new table created, but it doesn't copy any of the data. So, we end up with a new empty table that has the same structure as the existing table.

Deep Cloning operation is a combination of simple cloning and shallow cloning. It not only copies the structure of the existing table but also its data into the newly created table. Hence, the new table will have all the attributes of the existing table and also its contents.

Difficulty: 3/10

Topics: CREATE TABLE LIKE, CREATE TABLE AS SELECT, mysqldump

Scenario Questions

0-2 years experience

1
You need to create a backup copy of the users table before running a risky UPDATE. Show me the exact SQL you'd run to copy both structure and data.
2
A teammate used CREATE TABLE new_table LIKE old_table and says the data is missing. Why? What command should they have used instead?
3
You're asked to clone just the schema of orders table for a staging environment. Write the statement and explain what gets copied vs what doesn't.

2-5 years experience

1
We're building a feature that creates per-tenant schema copies on signup. You used CREATE TABLE ... LIKE in a loop but indexes are missing on the new tables. What went wrong and how do you fix it?
2
A nightly job clones the events table for analytics using CREATE TABLE ... AS SELECT. Lately it's causing replication lag on replicas. Walk me through why and two ways to mitigate it.
3
You need to clone a table but exclude the password_hash column for a dev export. How do you do this without creating the table first then ALTER DROP?

5-8 years experience

1
We're doing a zero-downtime migration: rename the live orders table, create new schema, backfill data. How do you clone the 2TB orders table without blocking writes for more than a few seconds? Mention specific tools and tradeoffs.
2
A developer cloned a partitioned table with CREATE TABLE ... LIKE but partitions disappeared. Explain why and how to properly clone partitioned tables including subpartition definitions.
3
Your team wants to implement 'instant rollback' by keeping a cloned copy of any table before DDL changes. Design the cloning strategy that works for tables from 10MB to 5TB, handles FKs, and doesn't double storage costs.

8+ years experience

1
We're decommissioning a legacy sharding layer and need to merge 200 shard tables into one. Each shard has divergent indexes and auto_increment offsets. How do you design a cloning/merging pipeline that validates schema compatibility, handles collisions, and lets us cut over with <30s downtime?
2
A cross-team platform group wants to offer 'table branching' — developers clone any production table to their dev namespace on demand, with data subsetting and PII masking. Architect the control plane: metadata tracking, quota enforcement, audit logging, and how you prevent runaway clones from consuming replica capacity.
3
After an acquisition, you inherit a MySQL 5.6 fleet with 5000 tables using mixed engines (MyISAM, InnoDB, TokuDB). You need to standardize on InnoDB with consistent cloning procedures across teams. What's your migration playbook: tooling, validation, rollback criteria, and how you get buy-in from teams who 'just use mysqldump'?

Follow-up Questions

❓What happens to foreign keys when you clone with LIKE vs AS SELECT?
❓How would you clone a 500GB table without blocking writes?