MySQL provides different storage engines for different functional requirements. The so-called storage engine is the specific implementation of the specific interface under MySQL.
Federated is one of the implementations specifically for remote databases. In general, building a table in a local database generates the corresponding table definition file in the database directory and generates the corresponding data file at the same time.
However, tables created through the Federated engine only have table definition files locally, and data files exist in the remote database (this is important).
This engine enables remote data access functions similar to Oracle Dblink.
Use the show engines command to see if the database has supported the Federated engine:
Support values are as follows:
YES |
Support and Open |
DEFAULT |
Supported and turned |
NO |
does not support |
DISABLED |
Support, but not open |
You can see that MyISAM is the current default engine.
Use the federated to create a table statement as follows:
CREATE TABLE (...) ENGINE =federated connection= ' mysql://[name]:[pass]@[location]:[port]/[db-name]/[table-name] '
Once the creation is successful, you can query the corresponding remote table directly locally.
A few things to note:
1. The local table structure must be exactly the same as the remote one.
2. Remote database is currently limited to MySQL
3. Do not support transactions
4. Table structure modification is not supported
The following are supplementary:
For a reference to the solution under Windows, add a row to the MY.CNF
Copy Code code as follows:
After restarting the MySQL service,
Mysql> show engines;
The federated storage Engine allows you to access data from the remote database in the local database, and queries against the Federated Storage Engine table are sent to the tables on the remote database, where no data is stored locally.
After a brief introduction, it is found that it is very similar to Oracle's database link, which implements functions similar to DB link, and federated storage Engine is the choice to search for db link alternatives under MySQL.
1. View the currently supported storage engine
Sql>show engines;
Copy Code code as follows:
+------------+---------+------------------------------------------------------------+--------------+------+---- --------+
| Engine | Support | Comment | Transactions | XA | savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+---- --------+
| CSV | YES | CSV Storage Engine | NO | NO | NO |
| Mrg_myisam | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, Row-level locking, and foreign keys | YES | YES | YES |
| MyISAM | YES | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
+------------+---------+------------------------------------------------------------+--------------+------+---- --------+
5 rows in Set (0.00 sec)
found that the installation of MySQL did not compile in, can only be installed now.
2. Install federated Storage Engine
Because no federated is selected at compile time, it is intended to be installed by install plugin, which normally supports dynamic installation:
= = = Federated Storage Engine = = =
Plugin name:federated
Description:connects to tables on remote MySQL servers
Supports build:static and dynamic
Configurations:max, Max-no-ndb
But execute the following order times wrong:
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 sym BOL:DYNSTR_APPEND_MEM)
Search a bit, found to be an old problem, even now have not resolved, can be seen in the MySQL team efficiency and management of the confusion. http://bugs.mysql.com/bug.php?id=40942
There is no way, only recompile MySQL source code, plus--with-plugins=federated. Starting with 5.1.26, the default MySQL does not enable the Federated storage Engine, so you need to add the federated option in MY.CNF or start--federated with the MYSQLD option on the command line. The results of the compilation are as follows:
Sql>show engines;
Copy Code code as follows:
+------------+---------+----------------------------------------------------------------------------+---------- ----+------+------------+
| Engine | Support | Comment | Transactions | XA | savepoints |
+------------+---------+----------------------------------------------------------------------------+---------- ----+------+------------+
| CSV | YES | CSV Storage Engine | NO | NO | NO |
| Mrg_myisam | YES | Collection of identical MyISAM tables | NO | NO | NO |
| Federated | YES | Federated MySQL Storage Engine | NO | NO | NO |
| MyISAM | YES | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
| InnoDB | DEFAULT | PERCONA-XTRADB, 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 |
+------------+---------+----------------------------------------------------------------------------+---------- ----+------+------------+
6 rows in Set (0.00 sec)
At this point, we can already use the federated storage Engine.
