Copy an existing MySQL table to a new table

This is a great set of two commands that allow the creation and population of a new table with the structure and data of an existing table. This provides a quick means of making a point-in-time copy of a table and is a safe, easy way to make a quick copy of a table for testing an application in development on live data without risking a production environment.

To make a copy of the table mytable which is in a different database called production into a new table called mytable_new in the currently selected database, use these two commands:

CREATE TABLE mytable_new LIKE production.mytable;
INSERT mytable_new SELECT * FROM production.mytable;

The first command creates the new table mytable_new by duplicating the structure of the existing table. The second command copies the data from old to new.

The nomenclature production.mytable is a means of specifying the database and table in the same way that a file can be specified by its directory path. It is optional. If production was left off, MySQL would assume that the mytable table was also in the currently selected database.