MySQL's federated engine implements class Oracle's Dblink

As the complexity of the business continues to increase, the database is constantly segmented, in the distributed system often one or several databases do not meet our needs, so often need more than one at a time, and in peacetime need to use other servers in the database to obtain information is the most convenient data link, The more common Dblink in Oracle, the ones that use Oracle's dblink-database, know that data can be queried across server databases. In the MySQL5.0 after the use of the federated engine can also be implemented similar to Dblink in Oracle, the default installation at MySQL5.5 Start is not enabled, as with the dblink of Oracle MySQL use federated engine to implement the external link recommendation is on the same LAN , this can reduce the delay between each other, but there is a big difference between the two, under MySQL using the Federated engine to implement the external link needs to build a virtual table in the local database to connect to the remote database, Here is a simple explanation of MySQL federated engine implementation of out-of-database links.

First of all, there are a few things to note:

1, the establishment of external links in the local area network environment to achieve optimal;

2, using the Federated Engine's external link table, is a virtual table locally, so the table after the display does not need the primary key, index, self-increment fields. Similarly, the structural modification of a local virtual table does not modify the structure of the remote table;

3, TRUNCATE (TRUNCATE table) will clear the remote table data, drop will only delete the local virtual table;

4. Alter is not supported on virtual tables

Whether there is a federated engine in the database can be viewed as follows:

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      &nbSp;      | yes     | myisam storage  engine                                            | NO            | no   | no         | |  BLACKHOLE          | YES      | /dev/null storage engine  (anything you write to it  Disappears)  | NO           | NO    | no         | |  memory         &Nbsp;   | 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        | |  ARCHIVE            | YES      | Archive storage engine                                           | NO            | NO   | NO          | |  PERFORMANCE_SCHEMA | YES     | Performance Schema                                                | NO            | no   | no         | |  FEDERATED          | NO       | federated mysql storage engine                                   | null         | null |  null       |+--------------------+---------+------------------------- ---------------------------------------+--------------+------+------------+9 rows in set  ( 0.01 SEC)

Generally this is the case that there is federated engine but not enabled, enabling is very simple, in the my.cnf or My.ini in the [MYSQLD] segment modified Add, as follows:

[mysqld]......federated ...

Restart MySQL, and then look at the federated engine enabled

At this point, the local database is built to link the remote database, its table structure can be viewed in the remote database through the show CREATE table to view the DDL, but note that the primary key, index, self-increment fields, but the local virtual table name can be different, such as remote database: 192.168.2.25 under the My_test library there is a book_price table, the DDL on the remote database:

CREATE TABLE ' book_price ' (' book_name ' varchar (255) NOT NULL default ' ", ' Book_price ' decimal (11,3) NOT null default ' 0 . ') Engine=innodb DEFAULT Charset=utf8

The DDL in the local database is as follows:

CREATE TABLE ' book_price_local ' (' book_name ' varchar (255) NOT null DEFAULT "', ' Book_price ' decimal (11,3) NOT null DEFA ULT ' 0.000 ') engine=federated DEFAULT charset=utf8 connection= ' mysql://root:password#@192.168.2.25:3306/my_test/ Book_price ' #源端表DDL engine=federated connection= ' mysql://User: Password @ip address: Port/Library name/table name ';

It is important to note that after the connection is the remote database connection information, it is important to avoid the use of the password with ' @ ' to avoid confusion, the effect is as follows:

MySQL's federated engine implements class Oracle's Dblink