MySql Remotely access database

Question

I have a two DB server say Server-1 & Server-2, I have installed MySql yog into it, and the servers are able to connect with each other as I have already provided Grant privileges for the servers.

But, I need to build a query which can extract data from more than one servers like -

Select * from Server1.db.Table1, Server2.db.Table2

Is this possible in mysql, if yes, can you please help me to achieve the same.

Thanks

Use federated engine: http://winashwin.wordpress.com/2012/08/22/mysql-federated-table/ — Ashwin A, Jun 17 '14 at 13:57

score 0 · Accepted Answer · edited May 23 '17 at 12:05

federated storage engine should help to full fill your requirement.

Follow link to enable Federated storage engine in your server from where you are accessing remote table.

--In Server1:

CREATE DATABASE fed_remote_db
CREATE USER 'fed_remote_user'@'%' IDENTIFIED BY 'fed_remote_password';
GRANT ALL ON fed_remote_db.* TO 'fed_remote_user'@'%';
CREATE TABLE fed_remote_db.fed_table(id INT,NAME VARCHAR(50));

--In Server2:

CREATE TABLE fed_remote_db.Server1_fed_table (
    id     INT NOT NULL AUTO_INCREMENT,
    NAME   VARCHAR(50) NOT NULL,
    PRIMARY KEY  (id)
) ENGINE=FEDERATED     CONNECTION='mysql://fed_remote_user:fed_remote_password@Server1_IP:3306/fed_remote_db/fed_table';

SELECT * FROM fed_remote_db.Server1_fed_table a /* Remember Server1_fed_table table referring to remote server /, fed_remote_db.server2_fed_table b / server2_fed_table table referring to local server */ WHERE a.id=b.id;

I believe you can convert above example for your requirements.

MySql Remotely access database

1 Answers1