How can I enable the federated engine in mysql after installation?

Question

I have mysql 5.1.44:

mysql> show engines;
+------------+---------+
| Engine     | Support | 
+------------+---------+
| ndbcluster | NO      | 
| MRG_MYISAM | YES     | 
| BLACKHOLE  | YES     | 
| CSV        | YES     | 
| MEMORY     | YES     | 
| FEDERATED  | NO      | 
| ARCHIVE    | YES     | 
| InnoDB     | YES     | 
| MyISAM     | DEFAULT |

I need to enable federated engine in mysql. How can I do it?

Answer 1

Edit /etc/my.cnf and in the [mysqld] section, add the line:

federated

It's equivalent to specifying --federated on the command line

Answer 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)

--> END PASTE <--

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!

Answer 3

Beginning with MySQL 5.0.64, the FEDERATED storage engine is not enabled by default in the running server; to enable FEDERATED, you must start the MySQL server binary using the --federated option. — MySQL Documentation

To use the --federated option in a configuration file drop the --.

Example

my.cnf

[mysqld]
federated

Answer 4

I was trying Foward Engineer in Workbench and this error appeared to me, so my solution was, Workbench -> Load your Model -> Mysql Model -> click in the table that was showing the error, and click on Edit, after that just change Engine to InnoDB.

MYSQL 8