To merge 2 MySQL databases into one you can use “Database Synchronization” or simply use “Copy database to different host/database”.
Quote:
Can webyog be used to merge these two databases into one database? i.e DB1 + DB2 = DB3.
To do this first create “DB3” database where you need to copy tables and their datas from DB1 and DB2 into DB3.
A. Using “Database Synchronization” tool from Powertools Menu. (right now we are supporting for single database only at a time):
To perform this first select Source db “DB1” and Target db “DB3” and similarly “DB2” to “DB3” by checking option 'One way synchronization'.
You can select 'Generate script' option from the wizard as shown in image “data sync.JPG” and edit the script in any editor by selecting only UPDATE statements. You may run Data Sync between master asand slave as , any difference between master and slave will result in INSERTs/UPDATEs/DELETEs on slave.
(but note this article is not up-to-date, but principles are the same).
Additionally, a very important point to notice is that the Primary Key on every table must be defined in such a way that the same value for the PK will never be created for different data on different hosts.
B. Another option is to use “Copy database to different host/database”. First select source DB1 and target DB3 and copy the data. Similarly follow same to copy from DB2 to DB3.
To do this first create “DB3” database where you need to copy tables and their datas from DB1 and DB2 into DB3.
A. Using “Database Synchronization” tool from Powertools Menu. (right now we are supporting for single database only at a time):
To perform this first select Source db “DB1” and Target db “DB3” and similarly “DB2” to “DB3” by checking option 'One way synchronization'.
You can select 'Generate script' option from the wizard as shown in image “data sync.JPG” and edit the script in any editor by selecting only UPDATE statements. You may run Data Sync between master asand slave as , any difference between master and slave will result in INSERTs/UPDATEs/DELETEs on slave.
(but note this article is not up-to-date, but principles are the same).
Additionally, a very important point to notice is that the Primary Key on every table must be defined in such a way that the same value for the PK will never be created for different data on different hosts.
B. Another option is to use “Copy database to different host/database”. First select source DB1 and target DB3 and copy the data. Similarly follow same to copy from DB2 to DB3.
You can go through our Help document too.
Regards,
Navya
Navya,
Thanks for detailed information.
In fact I will having the exact scenario as you mentioned above. i.e Same Primary Key(PK) in DB1 and DB2 tables will have the different data.
The below example will give you a clear picture and let me know is any way we can achieve this using SQLyog tool.
Example:
If you observe in below table, the same PK's will have different data in tables DB1:animalTable and DB2:animalTable. So, my expectation from the tool would be the DB3 (DB1 + DB2) will have cumulative data from DB1 and DB2.
Can you explain what you meant by 'cumulative data'? Is this is the case you are expecting your resultset to be:
DB3:animalTable
PK | Name |
1 | frog |
2 | ant |
3 | dog |
4 | fish |
Sorry, if wrong please explain what way you are expecting your data to be?
Thank You in advance.
Navya
Yes. Also, all dependent tables (if any) should be aware of this change to the PK in table. Ex: In DB2:animalTable the PK for Dog and Fish is 1 and 2 . After DB3 creation, it becomes 3 and 4.
FYI: I forget to mention in my last post that I tried the “Database Synchronization” tool from Powertools Menu and seems it is overwriting the table each time when I synchronise DBs during above scenario. I did not try “Copy database to different host/database” yet.
Hope this info will helps you. Also, is any contact number where I can directly call up and have a discussion ?
Yes. Also, all dependent tables (if any) should be aware of this change to the PK in table. Ex: In DB2:animalTable the PK for Dog and Fish is 1 and 2 . After DB3 creation, it becomes 3 and 4.
FYI: I forget to mention in my last post that I tried the “Database Synchronization” tool from Powertools Menu and seems it is overwriting the table each time when I synchronise DBs during above scenario. I did not try “Copy database to different host/database” yet.
Hope this info will helps you. Also, is any contact number where I can directly call up and have a discussion ?
Thanks,
Chandru
One more important point…Please note that all dependent tables of animalTable (references) should be updated accordingly.
Yes. Also, all dependent tables (if any) should be aware of this change to the PK in table. Ex: In DB2:animalTable the PK for Dog and Fish is 1 and 2 . After DB3 creation, it becomes 3 and 4.
You can use 'IMPORT EXTERNAL DATA TOOL' from Powertools menu or simply click CTRL+ALT+O. Follow the steps to achieve your result:
1. You have created 2 databases with tables (DB1 and DB2) and empty database (DB3).
2. Open 'IMPORT EXTERNAL DATA TOOL' from Powertools menu.
3. Create a new MySQL DSN for DB1. See image 'mysqldsn.jpg'. Similarly to Db2.
4. Select the target database DB3 to import data from first database DB1.
5. Now select tables and run the task to import data to DB3.
Now, you have successfully transferred data and their table from DB1 to DB3. Open table from DB3 in TABLE DATA tab to view the data.
6. Now again open 'IMPORT EXTERNAL DATA TOOL' from Powertools menu and select MySQL DSN for DB2.
7. Select target DB3 to import data from DB2.
8. In 'Select Table(s)' –> Map wizard, uncheck PRIMARY COLUMN from wizard. See image 'mapping.jpg'.
9. Open 'Select Table(s)' –> Advanced options wizard. Select 'import into existing' and 'keep target data' options from wizard. See image advancedoptions.jpg'.
10. Run the task.
For output see image 'tabledata.jpg'. You will get the resultset as expected.
But if you have auto_increment Primary keys you should dump from the RESULT tab using a result set without that column. Also with UNIQUE and FOREIGN KEY constraints it may not be so simple.
Chandru
Membernavyashree.r
Memberpeterlaursen
Participant
