asked on

joining two tables from different mysql servers

Dear experts,

I wish to join two database tables with mysql. Here is my scenario.

I have a mysql server at 192.168.55.5 with schema sales and table name order.

I have another mysql server at 192.168.43.44 with schema personnel and table name customer.

I wish to use a join query to see which customer has what orders.

How can I accomplish this in MySQL? Thanks

Phil Phillips

Take a look at the FEDERATED storage engine. More specifically, look at how to create a FEDERATED table.

In a nutshell, the FEDERATED engine allows you to create local tables that basically pull their data from other databases. Things to note for the server pulling the data:

You need to make sure that MySQL is compiled with the -DWITH_FEDERATED_STORAGE_ENGINE option

Server needs to be started with the --federated option

Kinderly Wade

ASKER

Hi Phil,

Sorry for the delay reply. Will there be anything else that I need to configure? For example sometimes when I use FEDERATED ENGINE, I will get some timeout for writing and reading the data. For small updates as in row count, few hundred rows are usually fine. If I am going for something like few thousand or few hundred thousand, I will get ERRORS like 1160 or 1159. Will there be another way to resolve it such as changing the my.cnf settings? Thanks.

ASKER CERTIFIED SOLUTION

Phil Phillips

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kinderly Wade

ASKER

perfect that will do. Thanks Phil