3

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?

JustBaron
  • 2,319
  • 7
  • 25
  • 37

1 Answers1

3

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';
JustBaron
  • 2,319
  • 7
  • 25
  • 37
  • So to be extra clear by "*only the local server where the federated table is stored*", this means it needs enabled on the server which you create the federated table using the `ENGINE=FEDERATED`.... if **federated** is not in the mysql ini config file under the `[mysqld]` section, then you'll see an error about the MySQL not supporting the federated file system type or something to that effect. – Bitcoin Murderous Maniac Jan 26 '20 at 01:15