I am attempting to run a single MySQL query, joining two databases on two servers. I am aware of the potential poor performance, but would like to test regardless.
The purpose of this test, I am working on a Windows domain, with a development (local) server and a live (remote) server.
The local server has FEDERATED engine enabled and the remote server, which stores the actual data but FEDERATED engine is disabled.
Both tables (live and federated) have the same definition/schema, and the federated table on the local server has been defined:
ENGINE=FEDERATED
DEFAULT CHARSET=utf8
CONNECTION='mysql://remote_user@remote_server/remote_database/remote_table';
The local server table creates fine, and although the remote_user and remote_user@'MY-PC-NAME' has the correct GRANTS, I am getting an error:
ERROR 1429 (HY000): Unable to connect to foreign data source: Access denied for user 'remote_user'@'MY-PC-NAME' (using password
My question is do both the local and remote servers require the FEDERATED engine to be enabled?
If not, is there anything else I need to do to get the federated table to work?
FEDERATED engine option is not required to be enabled on both servers, in this case, only the local server where the federated table is stored, requires to be enabled.
It turns out that the remote_user user requires the PASSWORD option.
I used this to get the connection to work:
CONNECTION='mysql://remote_user:password@remote_server/remote_database/remote_table';