Best way to copy a MySQL table?

Portal Home > Knowledgebase > Articles Database > Best way to copy a MySQL table?

Posted by Red Squirrel, 12-27-2010, 09:23 PM
IF I want to take a copy of a MySQL table, what is the best approach? This should work: But wondering if there is a better way, like maybe a copy table command or something.
Posted by TonyB, 12-27-2010, 11:20 PM
Well that won't work if you want structure as well. Here this should do the trick:
Posted by winlinuxadmins, 12-28-2010, 06:50 AM
If I say cd /var/lib/muysql/dababase_abc and copied all the certain table files that (MYI MYD and FRM ) with required new table name then ? like cp -p abc_table1.MYI abc_table2.MYI cp -p abc_table1.MYD abc_table2.MYD cp -p abc_table1.FRM abc_table2.FRM didn't tried it yet but what you guys say will it work ? sql statement is simple way but still
Posted by xuid, 12-28-2010, 10:10 AM
Yes it will work but for same mysql server version only. However since you do it on the same machine obviously it is the same mysql server
Posted by kevinmeaney, 12-29-2010, 04:37 AM
Thanks for the code I was also a little confused in the copying commmand.
Posted by Red Squirrel, 12-29-2010, 06:39 PM
Yeah I found that out the hard way. I thought maybe there was a more efficient way, but this seems to be pretty fast, so think that will do. I suppose the file copy method could work but pretty sure you'd have to stop mysql to do that, not to mention you can't really do that from an app that does not have access to the whole server.
Posted by DxSEO, 12-29-2010, 11:36 PM
Why not to create sql script for the same and paste it in phpmyadmin when you need it or make an php page and just execute it when you need
Posted by cvefa, 01-28-2011, 06:17 PM
copy myi,frm file only use myisam table type and same server work. Best way is SQL script. myisam or innodb
Posted by coderiser, 02-03-2011, 01:10 PM
you could always just export the table structure and modify the create table name if you wish to change the table. Then you can restore it wherever you wish.
Posted by Red Squirrel, 02-03-2011, 01:34 PM
This has to be done programatically. It happens every 30 seconds. But this seems to work fine and is fast:
Posted by nobie, 02-04-2011, 06:13 AM
just user option ''import''
Posted by menchester03, 02-04-2011, 10:16 AM
thanks for sharing