Federated. It is a storage engine that is available since MySQL 5.0.3. allowing access to data in remote database tables instead of local tables.
Summary
[ hide ]
- 1 Description
- 2 How to use the tables
- 1 Engine MyISAM
- 2 ENGINE FEDERATED
- 3 Limitations
- 4 Source
Description
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 more files are created, as the actual data is in the remote database. This differs from how engines work with local tables.
For local database tables, the data files are local. For example, if you create a MyISAM table called users, the MyISAM handler creates a data file called users.MYD. A handler for local tables reads, inserts, deletes and updates data in local data files, and the records are saved in a particular format of the handler. To read records, the handler must parse the data in columns. To write records, the column values must be converted to the record format used by the handler and written to the local data file.
With the MySQL FEDERATED engine there are no local data files for a table (for example, there is no .MYD file). Instead, a remote database stores the data that would normally be in the table. This requires the use of the MySQL client API to read, delete, update and insert data. Data retrieval is started with a SELECT * FROM tbl_name command. To read the result, the records are processed one by one using the mysql_fetch_row () API function and then converted from the columns of the SELECT result set to the format that the FEDERATED handler expects.
The basic flow is as follows: 1. SQL calls made locally. 2. MySQL handler API (data in handler format). 3. MySQL client API (data converted to SQL calls ). 4. Remote database ( MySQL client API ). 5. Convert the result set to the handler’s format. 6. Handler’s API (result records or count of records affected to local).
How to use the tables
The procedure for using FEDERATED tables is simple. Typically, you have two servers running on the same machine or on different machines.
- You must first create a table on the remote server that you want to access with the FEDERATED table.
Example:
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
DEFAULT CHARSET = latin1
- Then create a FEDERATED table on the local server to access the remote table:
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
Default Charset latin1 The structure of this table must be exactly the same as that of the remote table, except that the ENGINE table option must be FEDERATED and the COMMENT table option is a connection string that tells the FEDERATED engine how to connect to the server remote. The FEDERATED engine creates only the file test_table.frm in the federated database. The remote computer information indicates the remote server to which the local server connects, and the database and table information indicates the remote table to use as the data file. In this example, the remote server is indicated to be remote_host running on port 9306.
The general form of the connection string in the COMMENT option is as follows: scheme: // user_name [: password] @host_name [: port_num] / db_name / tbl_name Only mysql is supported as scheme at this point; password and port number are optional.
Limitations
- In the first version, the remote server must be a MySQLserver . FEDERATED support for other database engines will be added in the future.
- The remote table pointed to by a FEDERATED table must exist before trying to access it.
- It is possible for a FEDERATED table to point to another, but be careful not to create a loop.
- There is no support for transactions.
- There is no way for the FEDERATED engine to know if the remote table has changed. The reason is that the table must function as a data file that will never be written to do anything other than the database. The integrity of the data in the local table should be checked for any changes to the remote database.
- The FEDERATED engine supports SELECT, INSERT, UPDATE, DELETE, and indexes. ALTER TABLE, DROP TABLE, or any other Data Definition Language command is not supported. The current implementation does not use prepared commands.
- The implementation uses SELECT, INSERT, UPDATE, and DELETE, but not HANDLER.
- FEDERATED tables do not work with query cache.
