Example of using MySQL FEDERATED engine, similar to Oracle DBLINK.

1 engine description

The local MySQL database must use the FEDERATED storage engine to access the data in the table of the remote MySQL database. it is similar to the Database Link (DBLINK) in Oracle ). to allow this storage engine, use -- with-federated-storage-engine to configure when building MySQL.

When creating a FEDERATED table, the server creates a table definition file in the database directory. The file starts with the table name and has a. frm extension.

No other files are created because the actual data is stored in a remote database. the steps for using the FEDERATED table are very simple. generally, two servers are running either on the same host or on different hosts.

First, you must have a table on the remote server that you want to access using the FEDERATED table. Suppose that the remote table is in the DB_FED database and is defined as follows:

Create table test_table (

Id int (20) not null auto_increment,

Name varchar (32) not null default '',

Other int (20) not null default '0 ',

Primary key (id ),

KEY name (name ),

KEY other_key (other)

) ENGINE = MyISAM;

Then, create a FEDERATED table for accessing the remote table on the local server:

Create table federated_table (

Id int (20) not null auto_increment,

Name varchar (32) not null default '',

Other int (20) not null default '0 ',

Primary key (id ),

KEY name (name ),

KEY other_key (other)

) ENGINE = federated connection = 'mysql: // remote_user @ remote_host: 9306/db_fed/test_table ';

Except that the ENGINE option should be FEDERATED, and the CONNECTION table option is to indicate to FEDERATED how to connect to the CONNECTION string on the remote server,

The table must have the same structure as the remote table.

The remote host information indicates the remote server to which the local server is connected. The database and table information indicates which remote table is to be used as a data file. In this example.

The remote server is specified to run as a remote host on port 9306. Therefore, you must start the server and have it listen to port 9306.

The CONNECTION string in the CONNECTION option is generally in the following format (the password and port number are optional ):

Mysql: // user_name [: password] @ host_name [: port_num]/db_name/tbl_name

Here are some examples of connection strings:

CONNECTION = 'mysql: // username: password @ hostname: port/database/tablename'

CONNECTION = 'mysql: // username @ hostname/database/tablename'

CONNECTION = 'mysql: // username: password @ hostname/database/tablename'

Because any password used as plain text is included in the connection string, it can be used by any user who uses show create table or show table status for the federated table, you can also query the TABLES Table in the INFORMATION_SCHEMA database.

FEDERATED supports and does not support the following:

· The remote server must be a MySQL server. FEDERATED's support for other database engines may be added in the future.

· The remote table pointed to by the FEDERATED table must exist before it is accessed through the FEDERATED table.

· It is possible to direct a FEDERATED table to another FEDERATED table, but be careful not to create a loop.

· No support for transactions.

· If the remote table has changed, there is no way to know for the FEDERATED engine. This is because the table must work like a data file,

Nothing except the database will be written. If there is any change to the remote database, the data integrity in the local table may be damaged.

· FEDERATED storage engine supports SELECT, INSERT, UPDATE, DELETE, and index. It does not support alter table, drop table, or any

Other data definition language statements. The current implementation does not use prepared statements.

· Execute SELECT, INSERT, UPDATE, and DELETE, but do not use HANDLER.

· FEDERATED tables cannot play a role in the query cache.

Some of these restrictions may be removed in future versions of FEDERATED processors.

1 2 Next Page