Copy MySQL / MariaDB database table data to a new table

Table of Contents

• Method 1: Simple data copy (fast, data only)
• Method 2: Full copy with structure (complete backup)
• Frequently Asked Questions

There are two methods for copying a database table in MySQL or MariaDB. The first method creates a quick snapshot of the data only, while the second method creates a complete replica including all structural elements.

Method 1: Simple data copy (fast, data only)

This method is fastest and works well for quick data snapshots. However, it will not copy the AUTO_INCREMENT value, foreign key constraints, indexes, or triggers:

CREATE TABLE my_table_copy AS SELECT * FROM my_table;

Use this when: You need a quick backup for testing, data analysis, or temporary storage, and you don't need the table structure preserved.

Method 2: Full copy with structure (complete backup)

This method creates an exact replica of the table including indexes, triggers, and other structural elements:

CREATE TABLE my_table_copy LIKE my_table; INSERT INTO my_table_copy SELECT * FROM my_table;

Use this when: You need an exact copy for production backups, testing environments that mirror production, or when preserving table structure is important.

Frequently Asked Questions

Which method should I use?

Use Method 1 (CREATE TABLE AS SELECT) when you need a quick data snapshot for analysis or testing and don't care about indexes or constraints. Use Method 2 (CREATE TABLE LIKE + INSERT INTO SELECT) when you need an exact replica for production backups or when preserving the table structure is important. Method 2 is safer for most use cases as it maintains data integrity features like indexes and foreign keys.

Will this copy triggers and indexes?

Method 1 (CREATE TABLE AS SELECT) does NOT copy triggers, indexes, or AUTO_INCREMENT settings. Method 2 (CREATE TABLE LIKE + INSERT INTO SELECT) DOES copy indexes and the AUTO_INCREMENT setting, but triggers are NOT copied by either method. If you need to copy triggers, you must recreate them manually using SHOW CREATE TRIGGER and then CREATE TRIGGER statements.

Can I copy to a different database?

Yes, both methods support copying to a different database. Simply prefix the table name with the database name. For example: CREATE TABLE new_db.my_table_copy LIKE old_db.my_table; followed by INSERT INTO new_db.my_table_copy SELECT * FROM old_db.my_table;. Make sure you have appropriate permissions on both databases and that the target database exists before attempting the copy.