1

I have two databases: data1 and data2 in two different servers (server1 and server2).

I am trying to join two tables of my two databases in MySQL like this:

SELECT left(a.code,9) as fourn,
       SUM(b.price) as total
FROM data1.product as a
JOIN data2.my_items as b ON a.fourn = b.left(p_code)

and this is the error I get when I run the query in server1:

SELECT command denied to user 'slave'@'' for table 'my_items'

  • Can you add to your question some explanation about what problems do you have? or what's the error? – oNare Jul 13 '15 at 12:40
  • @oNare SELECT command denied to user 'slave'@'' for table 'my_items ' – Sarah Jul 13 '15 at 12:43
  • Which server (1 or 2) are you connected to when you run this query? – ypercubeᵀᴹ Jul 13 '15 at 12:49
  • @ypercube i run query in server1. – Sarah Jul 13 '15 at 12:50
  • Did you check if the user slave is with the host empty in table mysql.users? You put ` 'slave'@'' ` and this means that the user slave from host '' is trying to access to table my_items and it doesn't have permissions for SELECTs. I agree with @ypercube. – oNare Jul 13 '15 at 12:58
2

Federated storage engine can help you.

In mysql, you can achieve this by using federated storage engine. It allows you to access tables from different server. Once you reference the table from specific server, you can use it. You can get more details about federated storage engine. Now you can join two tables from different servers. how to use federated engine

ETL tools can help you like Talend data integration

Talend is open source tool which is ETL tool uses in data ware housing. It have nice UI, you can learn it in short duration. Here you can create many Mysql or database connections and use the different connections for different tables, can join whatever as you need.

  • Thank u a lot for ur answer. I installed talend data integration i will learn how i can work with. – Sarah Jul 15 '15 at 10:26
  • So using Talend i can join between two databases on different servers?!! – Sarah Jul 15 '15 at 10:36
  • @Sarah yes, whatever any type of operation you can perform with clicks – Hitesh Mundra Jul 15 '15 at 16:03
  • @Sarah for first time use tmysqlinput for tables, tmap component for joins – Hitesh Mundra Jul 15 '15 at 16:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.