MySQL - Clone Tables

MySQL - Cloning Tables

In database management, it’s frequently necessary to create a copy of an existing table. This could be for backup purposes, creating a test environment, or generating summary tables. Instead of manually writing a CREATE TABLE statement to mirror the original, MySQL provides efficient ways to clone tables.

Cloning creates a new table that is completely independent of the original. Any modifications made to the clone (inserting, updating, or deleting data, or altering its structure) will not affect the source table. This isolation is essential for safe testing and development.

Strategies for Cloning Tables

There are three primary strategies for cloning a table in MySQL, each with a specific use case:

• Shallow Cloning (Structure Only): Creates a new, empty table with the exact same structure as the original, including columns, data types, indexes, and partitions. This is ideal for creating a new table that will hold similar data.
• Partial Cloning (Structure and Data, Basic): Creates a new table and copies all data from the original. However, this method does not copy indexes, triggers, or constraints like PRIMARY KEY or FOREIGN KEY.
• Deep Cloning (Structure and Data, Complete): A two-step process that first creates a shallow clone (copying the full structure) and then populates it with data from the original table. This results in a true, complete replica.

Shallow Cloning: CREATE TABLE ... LIKE

The CREATE TABLE ... LIKE statement is the standard way to create an empty table based on the definition of another table.

Syntax

CREATE TABLE new_table LIKE original_table;

Example

First, let’s set up a sample PRODUCTS table.

CREATE TABLE PRODUCTS (
    ID INT AUTO_INCREMENT,
    SKU VARCHAR(20) NOT NULL,
    NAME VARCHAR(100) NOT NULL,
    PRICE DECIMAL(10, 2),
    PRIMARY KEY(ID),
    UNIQUE KEY `uk_sku` (SKU)
) ENGINE=InnoDB;

INSERT INTO PRODUCTS(SKU, NAME, PRICE) VALUES
('TS-01', 'Modern T-Shirt', 25.50),
('MUG-01', 'Developer Mug', 15.00);

Now, let’s create a shallow clone named PRODUCTS_ARCHIVE.

CREATE TABLE PRODUCTS_ARCHIVE LIKE PRODUCTS;

Verification

If we query the new table, it will be empty. But checking its structure shows it’s identical to the original.

-- Check the data (will be empty)
SELECT * FROM PRODUCTS_ARCHIVE;
-- Result: Empty set

-- Check the structure
SHOW CREATE TABLE PRODUCTS_ARCHIVE;

The output of SHOW CREATE TABLE will confirm that PRODUCTS_ARCHIVE has the same columns, PRIMARY KEY, and UNIQUE KEY as PRODUCTS.

Partial Cloning: CREATE TABLE ... AS SELECT

This command creates a new table and immediately populates it with the result of a SELECT statement. It’s a quick way to copy data, but it has significant limitations regarding structure.

Syntax

CREATE TABLE new_table AS SELECT * FROM original_table;

Example

Let’s create a partial clone of our PRODUCTS table.

CREATE TABLE PRODUCTS_QUICK_COPY AS SELECT * FROM PRODUCTS;

Verification

The new table will contain the data, but critical parts of the structure will be missing.

-- Check the data (will contain records)
SELECT * FROM PRODUCTS_QUICK_COPY;

-- Check the structure
SHOW CREATE TABLE PRODUCTS_QUICK_COPY;

When you inspect the structure, you will notice that the AUTO_INCREMENT property, the PRIMARY KEY, and the UNIQUE KEY were not copied. This method is best for creating temporary or summary tables, not for creating true backups.

Deep Cloning: The Best of Both Worlds

To create a perfect clone with both structure and data, we combine the previous two methods. This ensures all indexes, constraints, and data are preserved.

Syntax

-- Step 1: Create an exact structural copy
CREATE TABLE new_table LIKE original_table;

-- Step 2: Copy all the data into the new table
INSERT INTO new_table SELECT * FROM original_table;

Example

Let’s create a deep clone of the PRODUCTS table called PRODUCTS_BACKUP.

-- Step 1: Shallow clone the structure
CREATE TABLE PRODUCTS_BACKUP LIKE PRODUCTS;

-- Step 2: Populate with data
INSERT INTO PRODUCTS_BACKUP SELECT * FROM PRODUCTS;

Verification

The PRODUCTS_BACKUP table is now a complete and independent copy of the original.

SELECT * FROM PRODUCTS_BACKUP;

ID	SKU	NAME	PRICE
1	TS-01	Modern T-Shirt	25.50
2	MUG-01	Developer Mug	15.00

Best Practices and Performance

On large tables, cloning operations can be resource-intensive and may lock the source table, impacting application performance. For production environments, consider these best practices:

• Use Transactions: When performing a deep clone, wrap the CREATE and INSERT statements in a transaction to ensure data consistency, although CREATE TABLE implicitly commits.
• Zero-Downtime Tools: For very large tables in live systems, use external tools like Percona’s pt-online-schema-change or GitHub’s gh-ost. These tools perform schema changes and data copies with minimal impact on the production database.
• Backup Utilities: For creating full backups, dedicated tools like mysqldump are often a better choice. They produce a logical backup script that can be used to restore the table or entire database.

Cloning a Table Using a Client Program

Automating table cloning is a common task in deployment scripts or testing frameworks. Here’s how to perform a deep clone using a modern Node.js application.

Example: Node.js with mysql2/promise

NodeJS




const mysql = require('mysql2/promise');

// --- Configuration ---
const dbConfig = {
    host: '127.0.0.1',
    user: 'root',
    password: 'password',
    database: 'TUTORIALS'
};

const sourceTable = 'PRODUCTS';
const newTable = 'PRODUCTS_DEEP_CLONE';

// --- Main Logic with async/await ---
async function cloneTable() {
    let connection;
    try {
        connection = await mysql.createConnection(dbConfig);
        console.log('Successfully connected to the database.');

        console.log(`Starting deep clone of '${sourceTable}' to '${newTable}'...`);

        // Use backticks for table names to avoid issues with reserved words
        const createLikeQuery = `CREATE TABLE IF NOT EXISTS \`${newTable}\` LIKE \`${sourceTable}\`;`;
        const [createResult] = await connection.execute(createLikeQuery);
        console.log(`1. Structure for '${newTable}' created successfully.`);

        // Truncate the table in case it already existed with data
        await connection.execute(`TRUNCATE TABLE \`${newTable}\`;`);

        const insertSelectQuery = `INSERT INTO \`${newTable}\` SELECT * FROM \`${sourceTable}\`;`;
        const [insertResult] = await connection.execute(insertSelectQuery);
        console.log(`2. Data copied successfully. ${insertResult.affectedRows} rows affected.`);

        console.log('Deep clone complete.');

    } catch (error) {
        console.error('An error occurred during the cloning process:', error);
    } finally {
        if (connection) {
            await connection.end();
            console.log('Database connection closed.');
        }
    }
}

cloneTable();


Output
The expected output would be:

Successfully connected to the database.
Starting deep clone of 'PRODUCTS' to 'PRODUCTS_DEEP_CLONE'...
1. Structure for 'PRODUCTS_DEEP_CLONE' created successfully.
2. Data copied successfully. 2 rows affected.
Deep clone complete.
Database connection closed.