7

I have Mysql 5.5.18
and upon show engines:


show engines;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                         | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                      | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+

as you see federated engine is not one of the list. i need to enable it!! any idea of why BLACKHOLE, FEDERATED, and ARCHIVE engines are missing from the list? and how can i add/enable them?
EDIT
I have ubuntu 11.10 and installed mysql from ppa ... i have added the following to /etc/apt/sources.list

deb http://ppa.launchpad.net/nathan-renniewaldock/ppa/ubuntu oneiric main
deb-src http://ppa.launchpad.net/nathan-renniewaldock/ppa/ubuntu oneiric main

Thanks for your help

6
  • How do you have installed your mysql server. Dec 14, 2011 at 9:46
  • question has been edited
    – Alaa
    Dec 14, 2011 at 11:49
  • How were you able to find a PPA with 5.5? I have been looking for almost a day now
    – Dark Star1
    Dec 20, 2011 at 19:16
  • Hwllo Dark, check launchpad.net
    – Alaa
    Dec 21, 2011 at 7:10
  • Be careful. Federated is disabled by default since has some unsolved bugs. Why do you need it?
    – Massimo
    Dec 22, 2011 at 17:13

5 Answers 5

5

I have managed to find a solution...
there is a plugin already available at
/usr/lib/mysql/plugin/ha_federated.so
i just logged in to mysql as root and run the following query mysql>install plugin federated soname 'ha_federated.so';
then i added federated to my.cnf and restarted mysql

1
  • 1
    Is this applicable for mysql installed from binary distribution rather than source ???
    – Uday
    Mar 2, 2013 at 23:58
3

If Default installation/binary installation

In My opinion the BLACKHOLE , ARCHIVE are installed and enabled by default when you install the Mysql server.

but the FEDERATED is not enabled by default.For that do like this

You can enable it manually you have edit your my.cnf file.edit my.cnf and in the [mysqld] section, add the line:

federated

Now restart the Mysql server.

For adding Archive go to link http://timanovsky.wordpress.com/2010/10/09/enabling-archive-storage-engine-in-ius-mysql-5-1/

If you have installed from source then you was supposed to do like this:

For Blackhole: invoke configure with the --with-blackhole-storage-engine option.

For Archieve: invoke configure with the --with-archive-storage-engine option

For Federated: invoke configure with the --with-federated-storage-engine option

3
  • Abdul, If i installed mysql from compiled binaries, then how should i get federated engine active... I think i should resintall from source or is there any other option....
    – Uday
    Mar 2, 2013 at 23:57
  • Just Add a line federated in Your my.cnf and restart the server. Mar 4, 2013 at 9:22
  • Manaf, I missed to inform one more detail. When I hit "SHOW ENGINES", I saw "NO" for federated engine which means the binary is not compiled with the support for that engine. How to proceed in this scenario...
    – Uday
    Mar 9, 2013 at 1:05
2

I know the post is a little old, but it seems that many people are having issues with federated engines.

When the mysql binaries are installed via yum, you already have the HA (High Availability) plugins. You simply need to load the plugins within the mysql CLI.

Here is the basic process:

Start mysqld if it is not already started. Make sure 'federated' is NOT in /etc/my.cnf at this point.

EX: At this time, /etc/my.cnf will look like this from a standard YUM install....

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Log into the mysql CLI with root (or another account with sufficient privilege).

Type: show engines;

You should see no FEDERATED engine at this point, like this:

mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)

To enable the federate engine, type the following:

install plugin federated soname 'ha_federated.so'

NOW, when you 'show engines' you will see the FEDERATED Engine, but turned off...

It will look like this:

mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| FEDERATED  | NO      | Federated MySQL storage engine                             | NULL         | NULL | NULL       |
| CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
6 rows in set (0.00 sec)

You can now safely add the line 'federated' to the /etc/my.cnf file like this:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
federated

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Restart mysqld (service mysqld restart, etc...)

After the restart, go back in to the mysql CLI.

Type show engines;

You should now see the FEDERATED Engine available and with SUPPORT as YES.

mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| FEDERATED  | YES     | Federated MySQL storage engine                             | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
6 rows in set (0.00 sec)

And you are done...go forth and create federate tables...

Good luck!

Email me with any questions...happy to help.

0

On ML with the MySQL 5.5 (current) all I needed to do was:

  • Copy one of the existing files from /usr/local/mysql/support-files/ to /etc/my.cnf
  • Add 'federated'(without quotes) to the [mysqld] section (not the end of the my.cnf file)
  • Restart MySQL

This might also be useful for those that want an automated way to declare tables: http://sylnsr.blogspot.com/2012/11/auto-constructing-ddl-for-mysql.html

2
  • 1
    Could you disclose what ML is? I can't decide whether it has relevance here or not :)
    – dezso
    Nov 21, 2012 at 19:06
  • OS X Mountain Lion. I believe this has relevance since there are many people that use MySQL on OS X Mountain Lions. When searching for "mysql on ml" => google.com/search?q=mysql+on+ml
    – StartupGuy
    Dec 27, 2012 at 20:52
0

I'm using Mysql 8.0 in ubuntu 20.04, All the previous responses tackle the issue pretty well, i ran install plugin inside mysql, then i added federated to the /etc/mysql/my.cnf file, but when i try to get into the mysql shell, i get the following error :

mysql: [ERROR] unknown option '--federated'.

If this happens to you, it probably means you added federated in the wrong configuration file, this is the contents of my /etc/mysql/my.cnf :

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

in my case, the correct location to add the federated option was /etc/mysql/mysql.conf.d/mysqld.cnf , this is what it looks like once the federated flag is added :

[mysqld]
federated
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
datadir     = /var/lib/mysql
log-error   = /var/log/mysql/error.log

If you can't locate the files i mentioned or the mysql tree is different in your system, make sure you're adding federated under [mysqld], not [mysql]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.