Duplicating table in MYSQL without copying one row at a time

Question

I want to duplicate a very large table, but I do not want to copy it row by row. Is there a way to duplicate it? For example, you can TRUNCATE w/o deleting row/row, so i was wondering if there is something similar for copying entire tables UPDATE: row by row insert is very painful (because of 120M rows). Anyway to avoid that?

Accepted Answer

MySQL no longer has a reliable "copy table" functionality - many reasons for this related to how data is stored. However, the below does row-by-row insertion but is pretty simple:

```
CREATE TABLE `new_table` LIKE `old_table`;
INSERT INTO `new_table` (SELECT * FROM `old_table`);
```

Duplicating table in MYSQL without copying one row at a time

Solution

Problem