Copy Tables From One DB to Another 2

[Rock6431]

Newby to MySQL.

I have a MySQL InnoDB setup with various tables. I am working to split out these tables into related databases. For Example, I have a segment of books that I need to seperate out of the Development DB and want to place into a seperate DB called Books. I created the DB for Books and have a Tables section, just wonderin how to copy the table(s) and it's information from Main to Books using either MySQL Admin or Control Center?

Table names = books_master, books_main, books_pages

Database1 = Dev
Database2 = Books

TIA
Rock

 

[azzazzello]

Rock,

I don't know what OS you are using, but on a *nix system, you can simply copy the table files to another database's folder and you are done!

For example, your mysql directory is /var/mysql. Given the information you provided, you will have at these 2 directories in there - Dev and Books. When you go onto Dev directory, you will see 3 files for each table, with extensions frm, MYI and MYD. Copy these (9 total) to the Books directory, and you are done. I am sure the same applies to Windows

 

[Rock6431]

So, unlike SQL server 2000. You can simply copy/paste the binary file itself via windows/unix and this does not have to be done from within the database itself. Correct?

 

[aardvark92]

You may have to change the file permissions after copying, but yes, you can simply copy and paste the binary file to copy all the tables from one database to another.

 

[ingresman]

Actually in SQL server you can copy database files around to a new database and attatch them, if I had my book I'd show you the SQL. I think the issue is that SQL server stores more than one table in a physical operating system and also hold catalogue information about the tables and files in system tables. Mysql seems to store this "schema" or meta-data in the table files thmeselfs and each table maps to a single operating system file.
Just some background !