How to create Relationship between multiple databases?

Question

I am working with multiple databases which have the same structure and are on the same server. All of them are MySQL databases and I'm using MySQL Workbench (community version!) to connect to them.

I need to retrieve data from all (or some) of them and compare the results. sometimes I need to store the retrieved data from those databases in another database.

How can I manage that?

Answer 1

Because all databases (schemas) on same server You can operate with data easy - just add schema name before table, like:

SELECT t1.* FROM database1.table1 t1 LEFT JOIN database2.table1 t2 ON t1.id = t2.id

same for compare and save result, like

INSERT INTO database2.table3
SELECT * FROM database1.table1 t1 WHERE NOT EXISTS (SELECT id FROM database2.table1 t2 WHERE t2.id = t1.id)

and etc

MySQL workbench can help You with compare schema

You can also use - mysqldbcompare

• Afrooz: I tested it and it works all right! How about when one of my databases (schemas) is not in the same server as the others?
• a_vlad: for compare, You can use many kind of tools (including mysqldbcompare), but in code - not. You can test federated engine - dev.mysql.com/doc/refman/5.7/en/federated-storage-engine.html, but keep in mind it very sensitive for the connection and have it own restrictions - dev.mysql.com/doc/refman/5.7/en/federated-usagenotes.html ... all depends from real business needs, and sometime more easy correct business needs rather than realise all - I want.
• Afrooz: My database is full of procedures. I want to use the procedures and save the results in another table, but I can't! I get an error when I write ( select call "procedure_name"). One more thing, my procedure has only IN parameters , it fills a temporary table that is created inside the procedure and then select * from that temporary table and shows the table as the result. How can I use aggregate functions on the result table columns of a procedure? for example I have a column "total" and the result table has 12 records. I need sum(total) from that procedure (1 record) and on all of my databases
• Ali Rn: do you know about performance differences when using ' join ' and when using something like this with ' where ' ?