Mysql communication b/w two amazon instances

Question

I have client-server application where server need to read data from all client nodes. All my nodes + server are on amazon instances. I could not think of a better way to setup clients to communicate with server. I have two option currently :

a) ssh tunneling

b) mysql federated tables /engine

I have successfully setup ssh tunneling but I am looking for federated engine based solution.

I have enabled federated engine on both sides but when I try the connection fails. The question is how to setup federated engine to communicate with each other and how that is related to mysql ssl. Do we need to enable and setup mysql ssl.

Here is how I go :

Client

create database FED_remote_db;
use FED_remote_db;
create table test_data(id int(11) auto_increment primary key, name varchar(20) , dated timestamp);
create user 'fed_user'@'localhost'   identified by 'somepassword';
grant select on FED_remote_db.*  to  'fed_user'@'localhost' identified by 'somepassword';
flush privileges;

Server

create database FED_server_db;
use FED_server_db;
create server fed_con foreign data wrapper mysql options(user 'fed_user',password 'somepassword',host 'client-ip-address',port 3307,database 'FED_remote_db');

create table test_data(id int(11) auto_increment primary key, name varchar(20) , dated timestamp) 
 ENGINE=FEDERATED 
 connection 'fed_chml/test_data';

Test at server end

use FED_server_db;
select * from  test_data;
ERROR 1429 (HY000): Unable to connect to foreign data source: Can't connect to MySQL server on 'client-ip-address' (110)

*client-ip-address is actual an ip address

Do I need to enable some port or change mysql configuration at server/client end ?

If I have ssh access to clients how that will help federated units communication ?

Answer 1

Most instances when you have a communication problem between two instances in EC2 it is a problem with security groups. I would suggest putting both machines in the same security group and make sure the security group is add to itself.

Additionally I would research to see if the mysql federation requires extra ports other than the well know client port.