We've encountered a couple use cases where having federated tables prove useful, despite their limitations and caveats.
As best I can tell this must be enabled with a federated declaration in the .cnf upon startup.
Our use cases are limited and certainly don't warrant restarting every instance just to have it. At the same time I'm thinking of adding it as a default to our standard config so it is available on instances as they get bounced during more pressing maintenance.
Is there any reason not to have this option available unless decided absolutely necessary?
For a final clarification: This question is not about the disadvantages of actually using a federated engine; rather it is just about just enabling as an available option. Perhaps to put it another way: Is there good reason it is disabled by default other than making sure you don't shoot yourself in the foot if you understand the consequences?
Best Answer
IMHO it is really about security. If a table uses the federated storage engine, all one needs to do is this :
and you can see the connection information (server IP, database, table name, username, password) of the real MyISAM table on the remote DB server, and all in plain text.
It is too bad that the connection information is not encrypted.
The bottom line is that you can use federated option in my.cnf totally at your own risk.
UPDATE 2012-01-18 11:31 EDT
It was pointed out that you can create a server definition and then define that server definition in the CONNECTION option of the federated table.
I tested this out and it works great.
However, there is still a security flaw. If you can SELECT from the table
mysql.servers
, you can see all definitions. Here is the table's layout:In order to see the server definitions, run this query:
This will expose all remote servers known by the MySQL Instance, including the password in plain text.
To see all the federated tables, run this:
This confirms my original assertion: all one needs to do is this :
and you can see the connection information (server IP, database, table name, username, password) of the real MyISAM table on the remote DB server, and all in plain text. As given by my update, that plain text info is located in
mysql.servers
.