Menu
I have two databases in MySQL that already have data. they have exactly the same schema. I would like to merge those two databases into one only. I tried:
mysqldump -u root -p --databases database1 database2 database3 > database1_database2_da
However, when I try to open database1_database2_da, I only end up with data from one of the database, but not all of them. I also want to mention that the two databases have their records starting at 1, since they are automatically generated. Do you think there is a way to merge those two databases without losing data from any of them?
389
If you have two database with same schema then you may want to use MySql database with —no-create-info option. Then you can use it to load it into the target database. Also you can try Sql-Hub to merge databases. If you have database with different schema then you can simply use mysqldump and then go ahead with surgical merge.
In MySQL, merge can be performed with UNION, INSERT, or even using JOINS on two or more tables to combine the data values on the basis of UNIQUE key or PRIMARY key. We will create two tables in the database named Products and Products_Info that will contain information of products.
In the Databases section, click the phpMyAdmin icon. Click the SQL tab at the top. You will see where it says, Run SQL query/queries on server "localhost":. Insert the following code in the text box below, but replace DB1 and DB2 with the database names. Also, replace TABLE1 with the table name you are trying to merge. Click the Go button.
From SSH, you need to type the command to access MySQL. Here is the format, but replace MYNAME with your username and PASS with your password. Now type the following code, but replace DB1 and DB2 with the database names. Also, replace TABLE1 with the table name you are trying to merge.
Run mysqldump on each database with the --no-create-info option to avoid writing schema information. Then run once on one database with the --no-data option. If you load all of these files sequentially into the same target database, this should work, barring any differences in schema between the two databases or duplicate primary keys.
mysqldump -u root -p --no-create-info database1 > database1.sql
mysqldump -u root -p --no-create-info database2 > database2.sql
mysqldump -u root -p --no-data database1 > schema.sql
After creating a new database, run
mysql -uroot -p -Ddatabase3 < schema.sql
mysql -uroot -p -Ddatabase3 < database1.sql
mysql -uroot -p -Ddatabase3 < database2.sql
This may also work. Don't have a Windows box to test on ATM
type schema.sql database1.sql database2.sql | mysql -uroot -p -Ddatabase3
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
