Duplicating a MySQL table, indices, and data

manpreet Best Answer 2 years ago

How do I copy or clone or duplicate the data, structure, and indices of a MySQL table to a new one?

This is what I've found so far.

This will copy the data and the structure, but not the indices:

create table {new_table} select * from {old_table};

This will copy the structure and indices, but not the data:

create table {new_table} like {old_table};

manpreet 2 years ago

I found the same situation and the approach which I took was as follows: 1. Execute SHOW CREATE TABLE : This will give you the Create Table syntax for the table which you want to clone 2. Run the CREATE TABLE query by changing the table name to clone the table.

This will create exact replica of the table which you want to clone along with Indexes. The only thing which you then need is to rename the indexes (if required).