I would like to write a script which copies my current database sitedb1 to sitedb2 on the same mysql database instance. I know I can dump the sitedb1 to a sql script mysqldump -u root -p sitedb1 >~/db_name.sql and then import it to sitedb2. Is there an easier way, without dumping the first database to a sql file?
+5
A:
As the manual says in Copying Datbases you can pipe the dump directly into the mysql client:
mysqldump db_name | mysql new_db_name
If you're using myisam you could copy the files, but I wouldn't recommend it. It's a bit dodgy.
Greg
2009-03-23 21:26:57
That is basically the same thing as dumping it to an external file.
cletus
2009-03-23 21:36:02
Kinda... it skips a lot of disk IO though as you don't have to read/write the data twice
Greg
2009-03-23 21:39:52
If your database is gigabytes in size this probably won't gain you much. I think whta the OP is getting at is they don't want to externalize the copy: can it be done purely within mysql?
cletus
2009-03-23 21:43:29
I'd say the bigger the DB the more it gains you... There's no way to do this within MySQL afaik (except by hand, one table / view at a time)
Greg
2009-03-23 21:50:27
A:
I don't think there is a method to do this. When PHPMyAdmin does this, it dumps the DB then re-inserts it under the new name.
Unkwntech
2009-03-23 21:27:24
A:
You could use (in pseudocode):
FOREACH tbl IN db_a:
CREATE TABLE db_b.tbl LIKE db_a.tbl;
INSERT INTO db_b.tbl SELECT * FROM db_a.tbl;
The reason I'm not using the CREATE TABLE ... SELECT ... syntax is to preserve indices. Of course this only copies tables. Views and procedures are not copied, although it can be done in the same manner.
See CREATE TABLE.
Emil H
2009-03-23 21:28:33