Copy tables from one MySql DB to another MySql DB

[mshavel@optonli]

Hello,

I'm running 3.25.54 on a linux RedHat server.

Here is my scenario and what I'd like to do.

I have two databases

d1
d2

d1 has some tables in it that I want to be in d2 instead of being in d1.
My task is to get some of the tables in d1 into d2 and then remove them
from d1.

Here's what I'm thinking of doing.

1. Shut down my mysql server (or make sure nothing is being written to
the tables in question - I have one perl script writing to this table.
I can disable the perl script to not write anything to it.)
2. Issue a cp -p command in /var/lib/mysql/d1 to copy the selected .frm
.MYI and .MYD files into /var/lib/mysql/d2
3. Bring the mysql server back up and tell my perl script to now write
to the moved tables in d2 and NOT d1.
4. Drop the table I moved out of d1 using the DROP TABLE command.

I just want to know if there are any gotcha's hidden in this method. I'm
particularly concerned with moving the tables using cp -p from one
database to another. Are there any values in the .frm .MYI or .MYD files
that refer to the database they reside in? In other words, does the
table I've moved from d1 into d2, still think it belongs in d1 or does
the simple act of moving it into /var/lib/mysql/d2 now make a table in
d2?

Thanks very much!!

I have the New Riders MYsql book but I can't seem to find the answer to
this question.

Mike

[e-mail]

[Troy]

Unfortunately, I do not know about the raw MySql files, so I can't answer that question. Hopefully another guru will reply with a solid answer about that.

What I can offer you is to tell you that phpMyAdmin (You do have that right?!) has an export feature that can create entire schema building scripts as well as scripts to insert all the data. So you can go into db1, click the Export tab, choose a single table or multiple tables, click the data option, and export out a file that has insert statements for every row in the table. You can then run that file within phpMyAdmin against db2 to insert all the data. If you didn't already have your schema setup in db2, you can use the export feature to create a schema creation script based on db1, then run that on db2 to create your tables.

If your tables are very large, I'd think a route that copies the raw files would be easier---assuming that is possible. Please post back here to let us know what you end up learning. :)

[mshavel@optonli]

Hi Troy,

Thanks for your comments. Actually I don't have phpAdmin. I'm a command line kind of guy for the most part and what I don't do on the command line I use Navicat for.
Anyway, I did a lot of testing before trying the "Raw" file approach and it seems to work fine. The table I wanted to move was quite large which is why I was thinking of this method to begin with.

Thanks very much for you quick reply and suggestions; I really appreciate it.

Mike