MySQL question - copying a table.

[notfred]

I want to copy an entire table from one databse to a diufferent database on a different server. Any easy way to do this?

 

[Nothinman]

mysqldump the table then feed the dump file back onto mysql as a standard sql script.

 

[notfred]

that worked great, thanks.

 

[Nothinman]

Another cool thing about that is that since the mysqldump output is standard sql you could theoretically use that to migrate to a totally new database system like PostgreSQL or Oracle.

 

[Barnaby W. Füi]

Couldn't you also copy the .frm/.fyi/whatever the files are called?

 

[Nothinman]

Couldn't you also copy the .frm/.fyi/whatever the files are called?

Possibly, but I wouldn't rely on that working. I know for a fact it doesn't work between architectures with endian differences, and it's not like mysqldump takes very long.

 

[Barnaby W. Füi]

Originally posted by: Nothinman

Couldn't you also copy the .frm/.fyi/whatever the files are called?

Possibly, but I wouldn't rely on that working. I know for a fact it doesn't work between architectures with endian differences, and it's not like mysqldump takes very long.

Was just curious. I find it surprising that endian order makes a difference, I thought that wouldn't matter, since mysql is a "flat file" database.

 

[Nothinman]

Was just curious. I find it surprising that endian order makes a difference, I thought that wouldn't matter, since mysql is a "flat file" database.

It's not like it stores them in XML or CSV or something, it's binary data and as such is affected by architecture.

 

[Barnaby W. Füi]

Originally posted by: Nothinman

Was just curious. I find it surprising that endian order makes a difference, I thought that wouldn't matter, since mysql is a "flat file" database.

It's not like it stores them in XML or CSV or something, it's binary data and as such is affected by architecture.

How do flat file db's differ from...er... "non flat file" db's? I thought flat file db's stored data in ascii or whatever, and (is there a name for them?) "other" db's basically just stored a memory dump on disk.

 

[Nothinman]

How do flat file db's differ from...er... "non flat file" db's? I thought flat file db's stored data in ascii or whatever, and (is there a name for them?) "other" db's basically just stored a memory dump on disk.

I don't know enough about database design to say. But I do know that MySQL's table data is binary, it wouldn't make sense to store it in plain-text because you'd loose all the speed of using a database and might as well just use perl and sleepycat db files. Since MySQL 4 should be out soon (if it's not already) and it'll have many of the 'real' RDBMS features people complain MySQL is missing, maybe people will stop referring to it as a 'flat file' database.

 

[Barnaby W. Füi]

Originally posted by: Nothinman

How do flat file db's differ from...er... "non flat file" db's? I thought flat file db's stored data in ascii or whatever, and (is there a name for them?) "other" db's basically just stored a memory dump on disk.

I don't know enough about database design to say. But I do know that MySQL's table data is binary, it wouldn't make sense to store it in plain-text because you'd loose all the speed of using a database and might as well just use perl and sleepycat db files. Since MySQL 4 should be out soon (if it's not already) and it'll have many of the 'real' RDBMS features people complain MySQL is missing, maybe people will stop referring to it as a 'flat file' database.

I'm almost positive that the term "flat file database" refers to how it stores the data on the disk, and has a huge impact on the performance. I talked to someone about this just lately, but he only explained a tiny bit. I think how much of the database is held in memory is also a part of it; instead of querying the disk and writing to the disk for db queries, it just accesses the db in ram, and then updates the one on disk however often.

 

[Armitage]

Originally posted by: Nothinman
mysqldump the table then feed the dump file back onto mysql as a standard sql script.

What's really cool is, if the permissions are set right, you can just pipe the output from mysqldump straight into the new database ... locally or remotely.

 

[Nothinman]

nstead of querying the disk and writing to the disk for db queries, it just accesses the db in ram, and then updates the one on disk however often.

That's going to happen most of the time anyway just because of the OS file caching.

The only thing I can think of that would label one 'flat file' and another one 'other' would be the fact that Oracle can use a raw partition as db storage, bypassing the filesystem. But that's only an option, and all the Oracle dbs we use where I work use files for storage because using raw partitions makes things more complicated.