Solved

joining two tables from different mysql servers

Posted on 2015-02-10
4
200 Views
Last Modified: 2015-02-12
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
0
Comment
Question by:Kinderly Wade
  • 2
  • 2
4 Comments
 
LVL 13

Expert Comment

by:Phil Phillips
ID: 40602126
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
0
 

Author Comment

by:Kinderly Wade
ID: 40606639
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.
0
 
LVL 13

Accepted Solution

by:
Phil Phillips earned 500 total points
ID: 40606962
I haven't yet had the chance to use large FEDERATED tables over higher latency links, so I'm not 100% sure on what you would need to configure.

Though, maybe you can try playing around with higher values for 'net_read_timeout', 'net_write_timeout', and 'max_allowed_packet'.
0
 

Author Comment

by:Kinderly Wade
ID: 40607010
perfect that will do. Thanks Phil
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question