-1

We have a two database connection, which is given below,

$link1  = mysqli_connect($host1,$user1,$pass1,$db1);
$link2  = mysqli_connect($host2,$user2,$pass2,$db2);

Here, Host and Database are remote.

$query  = "SELECT db1_tbl1.name as name,db1_tbl1.email as email ,db2_tbl2.address
as address FROM db1.table1 as db1_tbl1 INNER JOIN db2.tble2 as db2_tbl2 ON 
db1_tbl1.std_id = db2_tbl2.std_id WHERE program_id = '$program_id' LIMIT 1,10";

mysqli_query($link1,$query);

If i use $link1 in mysqli_query, the error is showing

SELECT command denied to user 'user_name'@'remote_host_ip' for table 'table_name'
For First Link($link1)

If i use $link2,

SELECT command denied to user 'user_name'@'remote_host_ip' for table 'table_name'
For Second Link($link2)

Normally, separately two remote database connection and retrieving data is ok(it works perfectly)

But the problem is showing when i would like to retrieve data to join two remote host database.

Actually, How can i get data from the query which two connection will work at same time.

How to set the two database connection link in mysqli_query

Pleas any help?

MD. ABDUL Halim
  • 712
  • 3
  • 11
  • 32
  • you cannot do cross server queries like that. check the SO questions [MySQL Cross Server Select Query](http://stackoverflow.com/questions/810349/mysql-cross-server-select-query) and [MySQL — Joins Between Databases ..](http://stackoverflow.com/questions/5832787/mysql-joins-between-databases-on-different-servers-using-python) – bansi Sep 04 '14 at 06:56

2 Answers2

1

You can use FEDERATED storage engine in MySQL.

At First , You have to enable FEDERATED storage engine.

You have to go my.ini file in MySQL and open it.

Comment it the #skip-federated instead of skip-federated.

The word "federated" copy to paste after [mysqld] like below

[mysqld]
federated

A table create in your local server same as your remote server table and add connection in last 

CREATE TABLE federated_table (
....
.........(same as your remote server table)

)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';

since your two table is remote host, you can create two federated table.

I hope that You can do your work perfectly.

You can visit it. http://dev.mysql.com/doc/refman/5.1/en/federated-create-connection.html

MD. ABDUL Halim
  • 712
  • 3
  • 11
  • 32
0

It is possible to do this using the FEDERATED storage engine in MySQL.

The FEDERATED storage engine lets you set up a table on one of the servers that contains no data - it pulls the data from the other server. In your SQL queries, you treat it like a normal table, so you can do joins, select from it, insert, delete, etc.

But the FEDERATED storage engine is not enabled by default.

You would need to be able to restart the Mysql server binary with the --federated flag.

Basically, you would need to have root shell access (ssh) to do this, which means it is probably not a real solution if your databases are on a shared host.

Other options are:

  1. Copy the data from one database to another (eg. a temporary table) and then do the join.
  2. Select all of the data from both tables into the application and then perform the join in the application.
JGDarvell
  • 214
  • 1
  • 5