Copying a table from one schema to another

Is it possible to copy a table from one schema to another schema by just
copying the frm file to the directory for the new schema?

What is the best way to create a new database with all the tables in it
automatically?

I was hoping to have the tables (the frm files) included in a subdirectory
and when required, just create a new schema then copy all the frm files into
it.

Any help greatly appreciated.

Tks
John

Re: Copying a table from one schema to another

"zMisc" wrote in message
news:_NgDf.230793$V7.27067@news-server.bigpond.net.au...
> Is it possible to copy a table from one schema to another schema by just
> copying the frm file to the directory for the new schema?
>
> What is the best way to create a new database with all the tables in it
> automatically?
>
> I was hoping to have the tables (the frm files) included in a subdirectory
> and when required, just create a new schema then copy all the frm files
into
> it.
>
> Any help greatly appreciated.

I am going to *cautiously* suggest that you can simply create your new,
replicate, database by copying the entire database directory recursively to
a new directory in the mysql "data" directory. You want to replicate
*everything* - not just .frm files and you want to make sure MySQL retains
ownership of the new directory and all it's files.

In linux you can chdir to the mysql data directory and (as root!) issue the
cp (copy) command.
cp -p -R {OriginalDB} {ReplicateDB}

The [-p] switch preserves mysql file ownership and [-R] replicates the
*entire* database directory with all the files it contains.

This works neat as a pin in 4.0x and 4.1x versions of MySQL. I don't *know*
about version 5.0 or about MS Windows.

Thomas Bartkus

Re: Copying a table from one schema to another

"zMisc" wrote in message
news:_NgDf.230793$V7.27067@news-server.bigpond.net.au...
> Is it possible to copy a table from one schema to another schema by just
> copying the frm file to the directory for the new schema?

I never try to do database operations by manipulating the files at the
filesystem level.

I would do what you describe using mysqldump piped through mysql.

You can make mysqldump output skip the "create database" statement, and then
run the mysql tool with respect to a new, empty database (or any other
existing database). You can also make mysqldump back up selected tables
instead of a whole database. You can also make mysqldump back up table
schema only, without the contents of the tables if that is what you want.

There is enough flexibility to do what you want using proper tools, without
resorting to manipulating the myd and frm files.

Regards,
Bill K.

Re: Copying a table from one schema to another

Hi Bill,

I can only see .frm files - there are no .myd files what are the myd files?

I am using MySQL 5.

Tks


"Bill Karwin" wrote in message
news:drljkl01o7d@enews1.newsguy.com...
> "zMisc" wrote in message
> news:_NgDf.230793$V7.27067@news-server.bigpond.net.au...
>> Is it possible to copy a table from one schema to another schema by just
>> copying the frm file to the directory for the new schema?
>
> I never try to do database operations by manipulating the files at the
> filesystem level.
>
> I would do what you describe using mysqldump piped through mysql.
>
> You can make mysqldump output skip the "create database" statement, and
> then run the mysql tool with respect to a new, empty database (or any
> other existing database). You can also make mysqldump back up selected
> tables instead of a whole database. You can also make mysqldump back up
> table schema only, without the contents of the tables if that is what you
> want.
>
> There is enough flexibility to do what you want using proper tools,
> without resorting to manipulating the myd and frm files.
>
> Regards,
> Bill K.
>

Re: Copying a table from one schema to another

>I can only see .frm files - there are no .myd files what are the myd files?

Files containing MyISAM table data.

>I am using MySQL 5.

And probably InnoDB tables, so you won't have .MYD files.

Gordon L. Burditt