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:
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;
CREATE TABLE new_test LIKE test; INSERT INTO new_test SELECT * FROM test;
Query OK, 3316 rows affected (0.27 sec) Records: 3316 Duplicates: 0 Warnings: 0
This may not work with Replicated MySQL instances or members of a Group Replication.
CREATE TABLE new_table AS SELECT * FROM original_table;
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.
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.