copy a MySQL table which has data in ibdata file

Question

I have a database in MySQL with several tables. Some of the tables only have .frm files. Some have .frm .mdy .mdi I believe data for the .frm tables is in the ibdata file.

There is a lot of data in all the tables and I can see it.

How do I make a duplicate of this database with a different name? I can copy the folder and rename, but the tables with just the .frm files don't have the data.

Is there an easy simple way I can do this?

Miademora · Accepted Answer · 2011-10-07T13:28:30.210

0

CREATE TABLE student2 SELECT * FROM student

edit: into a new database CREATE TABLE newdatabase.student2 SELECT * FROM olddatabase.student

edited Oct 07 '11 at 13:28

answered Oct 07 '11 at 13:05

Miademora

146
3

That will do it in the same database, i want to e able to copy the table into a new database – Beginner Oct 07 '11 at 13:07
CREATE TABLE newdatabase.student2 SELECT * FROM olddatabase.student – Miademora Oct 07 '11 at 13:28
Error Code: 1146Table newdatabase.student2 doesnt exist – Beginner Oct 07 '11 at 13:32
Thats the error i get – Beginner Oct 07 '11 at 13:32
i worked out a way around if i create the table and then do an insert into like you shown instead of create it works – Beginner Oct 07 '11 at 13:34

copy a MySQL table which has data in ibdata file

1 Answers1