How to query databases at multiple locations at once?

Question

My firm has multiple branches in different locations. Each branch has it's own database(MySQL). I need to query those databases in a single query. Any ideas how to get this done?

Thanks for your help!

Answer 1

You can setup FEDERATED tables and query them from one server. This server will query federated server. But remember about restrictions fro FEDERATED tables.

Answer 2

If your tables are on the same server. You can do something like this:

SELECT /*fields*/ FROM database_name.table_name

Answer 3

There is no direct solution except you setup a centralized database server which is a slave for all your branches database

ie.

 branch_a => database name branch_a
 listening port 10000, master

 branch_b => database name branch_b
 listening port 11000, master

 branch_c => database name branch_c
 listening port 12000, master

 centralized server
 slave listening to master database branch_a on port 10000
 slave listening to master database branch_b on port 11000
 slave listening to master database branch_c on port 12000

when you require results for multiple servers, you can directly send query to this centralized server

drawback : network latency can lead delay of replication

Answer 4

Here is a tutorial on how to query multiple databases and the different techniques and options. If the databases are not on the same server, MySQL has no direct support for cross-database queries. Take a look at UnityJDBC. It allows you to write a SQL query that can join and compare data from different databases (MySQL, PostgreSQL, Oracle, Microsoft, etc.)