I wish to duplicate a really large table, however i don't want copying it row by row. It is possible to method to duplicate it?
For instance, you are able to TRUNCATE w/o removing row/row, so i'm wondering if there's such like to copy entire tables
UPDATE: row by row place is extremely painful (due to 120M rows). Anyway to prevent that?
MySQL no more includes a reliable "copy table" functionality - a lot of reasons with this associated with how information is saved. However, the below does row-by-row insertion but is fairly simple:
CREATE TABLE `new_table` LIKE `old_table`; INSERT INTO `new_table` (SELECT * FROM `old_table`);
You could utilize [cde].
If you are using MyISAM you are able to copy the physical files on disk. Restart the service you'll also find the brand new table with indexes and everything exactly the same.
INSERT INTO ... SELECT
It's nontrivial copying a sizable table ultimately the database will probably have to rebuild it.
In InnoDB the only method is actually to rebuild it, meaning place ... choose or so on, however, with 120M rows as it is going to happen in one transaction, you'll most likely exceed how big the rollback area, that will make the place to fail.
mysqldump then renaming the initial table then rebuilding the dump should work, as mysqldump could cause a commit every plenty of rows. However it will likely be slow.
Create table t as choose * from original_table