Duplicating table in MYSQL without copying one row at a time

Question

Hi folks, 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?

Answer 1

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`);

Answer 2

You could use INSERT INTO ... SELECT.

Answer 3

INSERT INTO TABLE2 SELECT * FROM TABLE1

Answer 4

If you're using MyISAM you can copy the physical files on disk. Restart the service and you'll have the new table with indexes and everything the same.

Answer 5

It's nontrivial to copy a large table; ultimately the database is likely to need to rebuild it.

In InnoDB the only way is really to rebuild it, which means insert ... select or such like, however, with 120M rows as it's going to happen in a single transaction, you will probably exceed the size of the rollback area, which will cause the insert to fail.

mysqldump followed by renaming the original table then restoring the dump should work, as mysqldump may cause a commit every lots of rows. However it will be slow.

Answer 6

oracle:

Create table t as select * from original_table