Yesterday at work I had the need to create a federated table in MySQL. I read about the federated engine and thought I had it sussed. I noted:
Beginning with MySQL 5.1.26, the
FEDERATEDstorage engine is not enabled by default in the running server; to enableFEDERATED, you must start the MySQL server binary using the--federatedoption.
Turns out it’s also possible simply to add the line federated in the [mysqld] section of /etc/my.cnf
The version of MySQL currently installed on my CentOS box was an older one (5.0.45) but I added this line anyway. The server refused to start. It quickly became clear that the MySQL binary packaged with CentOS was not compiled with the federated engine.
Fedora is currently packaging MySQL 5.1.37 but it seems that this too is lacking the federated engine. That’s annoying – I had wanted to install a version of MySQL from some yum repo or other, so I don’t have to keep upgrading the package every time a new version is released.
Perhaps the lack of federated support is a Red Hat (and derivatives) issue. I downloaded the rpm from MySQL directly, and installed it. Guess what – no federated engine compiled in.
So I downloaded the source tarball. I explicitly configured it with the federated engine, like so:
./configure --with-plugins=federated
And then I built and installed it. Nothing worked properly out of the box, and I was annoyed to find that the make install command doesn’t do half of the things I would normally expect it to do. I found this information and followed the steps to get it working. I had to steal and tweak the /etc/init.d/mysqld script from a different box which was running the bog-standard CentOS package.
Woohoo! The federated engine was finally available.
mysql> show engines; +------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +------------+---------+----------------------------------------------------------------+--------------+------+------------+ | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO | +------------+---------+----------------------------------------------------------------+--------------+------+------------+ 8 rows in set (0.00 sec)
But I can’t understand why none of the binary builds of MySQL include it. Fair enough that isn’t enabled by default in the running server – it’s no problem to add a line to my.cnf on a standard CentOS box. But it is a nuisance to have to build from source. It doesn’t break anything to have it enabled in a build, even if unused.
Of course CentOS won’t change the way they build their packages until Red Hat does. So I’m doing what I can, and I have filed a feature request with Fedora in the hope that in the next major release, there will be a version of MySQL built with the federated engine.
Jonathan Gazeley 
After a few hours of research trying to get a FEDERATED engine working on my fedora server, I landed at your site, now my head is clear on the issue. Thanks for the information
LikeLike
As I mentioned above, I filed a feature request with Fedora. They closed my ticket, saying that the FEDERATED engine is already available as a plugin and has to be enabled. I don’t know how to do this, though,
LikeLike
Fedora do have support for Federated databases since it was available in the core MySQL build.
To enable it, edit /etc/my.cnf and ensure something like the following is there:
[mysqld]
federated=ON
Restart your MySQL server and do the following as the ‘root’ user of the DB:
mysql -u root -p database_name -e “INSTALL PLUGIN federated SONAME ‘ha_federated.so'”
Some versions of MySQL server shipped with Fedora (notabilly 5.1.47 on Fedora 11) had the Federated support but it was not working due to some linkage problem, as described here: http://bugs.mysql.com/bug.php?id=40942
On newer versions of MySQL shipped with Fedora, as 14, the procedure above will work.
LikeLike
Thanks, this is good advice 🙂
LikeLike
I have MySQL 5.1.47 on RHEL server. How do I enable Federated support in it..??I have tried adding “federated” in my.cnf file but the server stopped working. Any help would do..
LikeLike
You need to add it in the [mysqld] section. Try something like
[mysqld]federated=ON
LikeLike
Still didnt worked…However i resolved the issue by first installing the federated plugin manually. Followed the following steps–
enabling federated option in MySQL in linux.**********
SELECT * FROM information_schema.PLUGINSG — (will list the plugins and details)
[cdcuXYZuatdb etc]# cd /
[cdcuXYZuatdb /]# find / -name plugin — (will list the paths where plugins is present)
[cdcuXYZuatdb]# cd /usr/lib/mysql/plugin — (this is the path shown above for plugins)
[cdcuXYZuatdb plugin]# ls -ltr
……….
………….
…………….total 156
…………………list of engines……..
[cdcuXYZuatdb plugin]# mysql
…………..
……………………….service version 501.52 source distribution…..etc etc……..
…………………………………………………..
mysql> INSTALL PLUGIN federated SONAME ‘ha_federated.so.0’;
mysql> SHOW ENGINES;
Now it will show the Federated engine option in the engine list but it will be disabled. To enable the federated engine follow the steps:
1. Stop the mysqld services in the database server
2. Go to the my.cnf file(mysql config file) present in path /etc/my.cnf
3. Add a word “federated” (without quotes) below [mysqld] in the configuration file.
4. Save the file and close the editor.
5. Start the mysqld services in the database server.
6. shoot command — show engines;
This will list the engines present in MySQL with federated engine enabled.
LikeLike