Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
How do you have installed your mysql server. –  Abdul Manaf Dec 14 '11 at 9:46
    
question has been edited –  Alaa Dec 14 '11 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 '11 at 19:16
    
Hwllo Dark, check launchpad.net –  Alaa Dec 21 '11 at 7:10
    
Be careful. Federated is disabled by default since has some unsolved bugs. Why do you need it? –  Massimo Dec 22 '11 at 17:13

4 Answers 4

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

share|improve this answer
    
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 '13 at 23:57
    
Just Add a line federated in Your my.cnf and restart the server. –  Abdul Manaf Mar 4 '13 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 '13 at 1:05
up vote 2 down vote accepted

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

share|improve this answer
    
Is this applicable for mysql installed from binary distribution rather than source ??? –  Uday Mar 2 '13 at 23:58

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.

share|improve this answer

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

share|improve this answer
1  
Could you disclose what ML is? I can't decide whether it has relevance here or not :) –  dezso Nov 21 '12 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 –  M.Mugge Dec 27 '12 at 20:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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