How to copy a table in MySQL

Last Updated:

There are two methods (or probably more) to clone or copy MySQL tables. The second method may not work if Group Replication is active.

Using separate commands

First, log in to your MySQL instance:

mysql -u root -p 

Then change the database to where the tables exist:

USE database_name; 

Create a new empty table that is exactly like the original table:

CREATE TABLE new_table LIKE original_table; 

Then, copy all the data from the original table to the new table:

INSERT INTO new_table SELECT * FROM original_table;

Example:

CREATE TABLE new_test LIKE test; 

INSERT INTO new_test SELECT * FROM test;

Output:

Query OK, 3316 rows affected (0.27 sec)
Records: 3316  Duplicates: 0  Warnings: 0

Single Command

This may not work with Replicated MySQL instances or members of a Group Replication.

CREATE TABLE new_table AS SELECT * FROM original_table;

Example:

CREATE TABLE new_test2 AS SELECT * FROM test;

If you’re using Group Replication which uses GTID based transaction, this command may result in an error similar to:

ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

Conclusion

It’s really easy to copy or clone a table. The safest way to do this is to first copy the table structure and copy the table contents afterward.

RECENT POSTS

Get Ops Pro Tips in Your Inbox!