CODEWITHSUNDEEP
mysqldatabase
Mike L.
Mike L.

Reputation: 1966

Is it possible to merge two MySQL databases into one?

Lets say I have two MySQL databases with some complex table structures. Neither database has the same table name. Lets say these tables contain no rows (they do but I could truncate the tables, the data is not important right now, just testing stuff). Lets say I need these 2 databases merged into one. For instance:

DB1:

cities states

DB2:

index subindex posts

I want to end up with a single DB that contains:

cities states index subindex posts

Is this possible?

Upvotes: 1

Views: 1248

Answers (4)

Hoàng Long
Hoàng Long

Reputation: 10848

Yes, I think that's possible. All you need to do is export 2 tables to sql script, replace one table name by the other; append the scripts and run it one time

Upvotes: 0

Gats
Gats

Reputation: 3462

If you have mySQL administrator or even phpmyadmin this should be pretty straight forward.

Take any apps using the databases offline (to ensure nothing changes), Script out the database using one of the many export options and making sure you script data, schema and any other properties, but not any DATABASE CREATE statements.

Run the script on the second database.

NOTE: This option should ONLY be run if you are 100% sure that there is no schema overlap on both databases!!!!

Upvotes: 0

gap
gap

Reputation: 2796

Which storage engine are you using?

If using myISAM, have you tried copying the 3 files representing the table (*.frm, *.myd, *.myi) from one DB dir (under mysql/data/db1) to the other (under mysql/data/db2)?

Upvotes: 0

Orbling
Orbling

Reputation: 20612

You can use RENAME TABLE on all the tables from one of the databases.

See: http://dev.mysql.com/doc/refman/5.0/en/rename-table.html

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

Providing there are no duplicates.

Upvotes: 2

Related Questions