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?

score 69 · Accepted Answer · edited Sep 09 '19 at 09:28

69

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

federated

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

edited Sep 09 '19 at 09:28

double-beep

5,031
17
33
41

answered Mar 06 '11 at 12:20

Lina

2,090
4
20
23

4

Also on Windows the file to modify is `my.ini` in the installation directory (for example `C:\Program Files\MySQL\MySQL Server 5.1`) – Emmanuel Bourg Dec 10 '12 at 08:23
2

in windows, it also has to be the next thing right after the [mysqld] with a newline, like this-> [mysqld] (newline) federated – Paul Stanley Jul 11 '13 at 07:55
2

Can't get this to work, opened my.ini and added `federated` in a newline after `[mysqld]`, still don't see it enabled. I am on `MySQL 5.6.12 on Windows 8`. Any idea what could be wrong? – Abishek Jul 29 '13 at 06:01
3

In windows it can be under different folder, not under 'Program Files'. Might be '\ProgramData\MySQL\MySQLServer5.6\my.ini' or similar. – Alex M Apr 10 '14 at 12:33
4

If you're using Amazons AWS RDS, the Federated engine is not supported. Just leaving this here to save anyone else the time spent trying it out only to end up frustrated. – Carlos P Jul 21 '15 at 07:58
As per other Q&A under Windows when installed with MSI you should edit the file in `%APPDATA%\MySQL\\MySQL Server 8.0` rather then in `C:\Program Files...\` – Simone Avogadro Jun 12 '19 at 12:39

score 41 · Answer 2 · edited Aug 23 '13 at 10:20

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!

Basically, one can only add next lines to my.cnf (or my.ini in windows): `[mysqld] federated`, which can be added to the end of file. — Alex M, Apr 10 '14 at 12:50
As you say, yum install version of mysql server don't adds "federated" support. Your answer is so good, you saved my day !!! — serfer2, Jun 29 '15 at 09:42

score 7 · Answer 3 · edited Sep 16 '16 at 10:37

7

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

edited Sep 16 '16 at 10:37

Gerard Roche

6,162
4
43
69

answered Mar 06 '11 at 11:56

The Scrum Meister

29,681
8
66
64

This is not worked: PS C:\Program Files\MySQL\MySQL Server 5.7\bin> .\mysql.exe --federated -u root -p mysql: [ERROR] unknown option '--federated' – Don Globian Dec 02 '21 at 13:47

score 0 · Answer 4 · answered Nov 20 '20 at 14:12

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

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

4 Answers4

Example

Linked

Related