How to enable the Federated engine in Mysql

MySQL provides different storage engines for different functional requirements. The so-called storage engine is the specific implementation of specific interfaces in MySQL.

FEDERATED is a specific implementation of remote databases. Generally, when creating a table in a local database, the corresponding table definition file is generated in the database directory, and the corresponding data file is generated at the same time.
However, tables created using the FEDERATED engine only have a table definition file locally, and data files exist in remote databases (this is important ).

This engine can be used to implement remote data access functions similar to DBLINK in Oracle.

Run the show engines command to check whether the database supports the FEDERATED engine:

Support has the following values:

YES	Support and enable
DEFAULT	Support and enableAnd is the default Engine
NO	Not Supported
DISABLED	Supported, but not enabled

MyISAM is the default engine.
The statements for using FEDERATED to create a table are as follows:
Create table (......) ENGINE = federated connection = 'mysql: // [name]: [pass] @ [location]: [port]/[db-name]/[table-name]'
After the table is created, you can query the corresponding remote table locally.

Notes:

1. The local table structure must be the same as the remote table structure.
2. Currently, the remote database is only available for MySQL.
3. transactions are not supported.
4. Table Structure Modification is not supported

The following is a supplement:

Refer to the solution in windows and add a line in my. cnf.

Copy codeCode: federated

After the mysql service is restarted,

Mysql> show engines;

The Federated storage engine allows you to access data in a remote database in a local database. Queries to federated storage engine tables are sent to the table in a remote database for execution, and no data is stored locally.
After a brief introduction, do you find that it is very similar to the database link of Oracle? The functions it implements are similar to those of db link. You need to find a database link alternative under MySQL, federated storage engine is the best choice.

1. view currently supported storage engines

SQL> show engines;

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

It is found that MySQL is not compiled during installation and can only be installed now.

2. Install the federated storage engine

Because federated is not selected during compilation, you plan to INSTALL it by installing PLUGIN. Under normal circumstances, federated supports dynamic installation:
=== Federated Storage Engine ===
Plugin Name: federated
Description: Connects to tables on remote MySQL servers
Supports build: static and dynamic
Deployments: max, max-no-ndb

However, an error is reported when you execute the following command:

SQL> install plugin federated soname 'Ha _ federated. so ';
ERROR 1126 (HY000): Can't open shared library '/usr/local/mysql/lib/mysql/plugin/ha_federated.so' (errno: 2 undefined symbol: dynstr_append_mem)
I searched for the problem and found it to be an old problem, which was not solved yet. This shows the efficiency and management confusion of the MySQL team. Http://bugs.mysql.com/bug.php? Id = 40942
No way. You only need to re-compile the MySQL source code and add -- with-plugins = federated. Starting from 5.1.26, the federated storage engine is disabled by default in MySQL. Therefore, you must add the federated option to my. cnf or use the -- federated option in the command line to start mysqld. The compiled result is as follows:

SQL> show engines;

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

Now, we can use the federated storage engine.