MySQL Bugs: #7957: Some way to make a copy of a table.

Bug #7957	Some way to make a copy of a table.
Submitted:	17 Jan 2005 14:56	Modified:	25 Jun 2013 19:13
Reporter:	Hans-Henrik Stærfeldt	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	4.1.5-gamma-20041010-log	OS:	Other (IRIX64 genome 6.5 04070317 IP35)
Assigned to:		CPU Architecture:	Any

Description:
I could really use a feature like this :-) ;

BACKUP [TABLE] db1.table1 TO [TABLE] db2.table2;

Function; Make a read lock on table1 and create a binary
copy (using 'cp') if the .MYD, .frm _and_ .MYI files
to table2. (given correct privileges, fails if db2.table2
exists and all that).

This helps all those (like me) that are often update-bound
and need to update a database while having the older
version online, and then do a quick swap once the newer 
version is ready. This way we dont need to rebuild all the 
indexes (which may take a _days_!).

The syntax matters litte to me, but the ability to copy a 
table binary would help alot.

How to repeat:

This is how things work with many databases here;
Rebuilding the table using INSERT .. SELECT takes _way_ to long, if all you 
want is a copy of production.table. We could do it with a root enabled script,
but the server should be able to support this as well.

LOOP: 
 CREATE TABLE updating.table LIKE production.table;
 INSERT INTO updating.table SELECT * FROM production.table;
 DELETE FROM updating.table1 WHERE id='6524362';
 ....
 LOAD DATA LOCAL INFILE 'updates.tab' REPLACE INTO TABLE updating.table1;
 ALTER TABLE production.table RENAME updating.table_old;
 ALTER TABLE upating.table RENAME production.table;
 DROP TABLE updating.table_old;

Suggested fix:

LOOP:
 BACKUP TABLE production.table TO TABLE updating.table;
 DELETE FROM updating.table1 WHERE id='6524362';
 ....
 LOAD DATA LOCAL INFILE 'updates.tab' REPLACE INTO TABLE updating.table1;
 ALTER TABLE production.table RENAME updating.table_old;
 ALTER TABLE upating.table RENAME production.table;
 DROP TABLE updating.table_old;

Try :
create table t1 (Number int);
insert into t1 values(1),(2),(3),(4),(5),(6);
select * from t1;
create table t2 select * from t1;
select * from t2;
Voila - a copy of table t1. (no Keys and Indices, but  a copy is a copy ;-) )
Hope this helps.

Thanks Frank, but as im saying, I need a copy _with_ the indexes, otherwise I could use the 
BACKUP command. Rebuilding a table with all the data and indexes would take me more than a 
day since I have more than 100million records and about 10GB of data. Copying the files raw
takes about 10 minutes (using 'cp').

Thank you for the reasonable feature request.