Menu:
federated-storage-engine-resources:: FEDERATED Storage Engine Resources
The 'FEDERATED' storage engine lets you access data from a remote MySQL database without using replication or cluster technology. Querying a local 'FEDERATED' table automatically pulls the data from the remote (federated) tables. No data is stored on the local tables.
To include the 'FEDERATED' storage engine if you build MySQL from source, invoke 'CMake' with the '-DWITH_FEDERATED_STORAGE_ENGINE' option.
The 'FEDERATED' storage engine is not enabled by default in the running server; to enable 'FEDERATED', you must start the MySQL server binary using the '--federated' option.
To examine the source for the 'FEDERATED' engine, look in the 'storage/federated' directory of a MySQL source distribution.
File: manual.info.tmp, Node: federated-description, Next: federated-create, Prev: federated-storage-engine, Up: federated-storage-engine
When you create a table using one of the standard storage engines (such as 'MyISAM', 'CSV' or 'InnoDB'), the table consists of the table definition and the associated data. When you create a 'FEDERATED' table, the table definition is the same, but the physical storage of the data is handled on a remote server.
A 'FEDERATED' table consists of two elements:
A remote server with a database table, which in turn consists of the table definition (stored in the '.frm' file) and the associated table. The table type of the remote table may be any type supported by the remote 'mysqld' server, including 'MyISAM' or 'InnoDB'.
A local server with a database table, where the table definition matches that of the corresponding table on the remote server. The table definition is stored within the '.frm' file. However, there is no data file on the local server. Instead, the table definition includes a connection string that points to the remote table.
When executing queries and statements on a 'FEDERATED' table on the local server, the operations that would normally insert, update or delete information from a local data file are instead sent to the remote server for execution, where they update the data file on the remote server or return matching rows from the remote server.
The basic structure of a 'FEDERATED' table setup is shown in *note figure-se-federated-structure::.
FIGURE GOES HERE: FEDERATED Table Structure
When a client issues an SQL statement that refers to a 'FEDERATED' table, the flow of information between the local server (where the SQL statement is executed) and the remote server (where the data is physically stored) is as follows:
The storage engine looks through each column that the 'FEDERATED' table has and constructs an appropriate SQL statement that refers to the remote table.
The statement is sent to the remote server using the MySQL client API.
The remote server processes the statement and the local server retrieves any result that the statement produces (an affected-rows count or a result set).
If the statement produces a result set, each column is converted to internal storage engine format that the 'FEDERATED' engine expects and can use to display the result to the client that issued the original statement.
The local server communicates with the remote server using MySQL client C API functions. It invokes 'mysql_real_query()' (https://dev.mysql.com/doc/c-api/5.7/en/mysql-real-query.html) to send the statement. To read a result set, it uses 'mysql_store_result()' (https://dev.mysql.com/doc/c-api/5.7/en/mysql-store-result.html) and fetches rows one at a time using 'mysql_fetch_row()' (https://dev.mysql.com/doc/c-api/5.7/en/mysql-fetch-row.html).
File: manual.info.tmp, Node: federated-create, Next: federated-usagenotes, Prev: federated-description, Up: federated-storage-engine
Menu:
federated-create-server:: Creating a FEDERATED Table Using CREATE SERVER
To create a 'FEDERATED' table you should follow these steps:
Create the table on the remote server. Alternatively, make a note of the table definition of an existing table, perhaps using the *note 'SHOW CREATE TABLE': show-create-table. statement.
Create the table on the local server with an identical table definition, but adding the connection information that links the local table to the remote table.
For example, you could create the following table on the remote server:
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),
INDEX name (name),
INDEX other_key (other)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1;For creating the local table to be federated to the remote table, there are two options available. You can either create the local table and specify the connection string (containing the server name, login, password) to be used to connect to the remote table using the 'CONNECTION', or you can use an existing connection that you have previously created using the *note 'CREATE SERVER': create-server. statement.
Important:
When you create the local table it must have an identical field definition to the remote table.
Note:
You can improve the performance of a 'FEDERATED' table by adding indexes to the table on the host. The optimization occurs because the query sent to the remote server includes the contents of the 'WHERE' clause, and is sent to the remote server and subsequently executed locally. This reduces the network traffic that would otherwise request the entire table from the server for local processing.
File: manual.info.tmp, Node: federated-create-connection, Next: federated-create-server, Prev: federated-create, Up: federated-create
15.8.2.1 Creating a FEDERATED Table Using CONNECTION ....................................................
To use the first method, you must specify the 'CONNECTION' string after the engine type in a *note 'CREATE TABLE': create-table. statement. For example:
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),
INDEX name (name),
INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';Note:
'CONNECTION' replaces the 'COMMENT' used in some previous versions of MySQL.
The 'CONNECTION' string contains the information required to connect to the remote server containing the table used for physical storage of the data. The connection string specifies the server name, login credentials, port number and database/table information. In the example, the remote table is on the server 'remote_host', using port 9306. The name and port number should match the host name (or IP address) and port number of the remote MySQL server instance you want to use as your remote table.
The format of the connection string is as follows:
SCHEME://USER_NAME[:PASSWORD]@HOST_NAME[:PORT_NUM]/DB_NAME/TBL_NAMEWhere:
SCHEME: A recognized connection protocol. Only 'mysql' is supported as the SCHEME value at this point.
USER_NAME: The user name for the connection. This user must have been created on the remote server, and must have suitable privileges to perform the required actions (note 'SELECT': select, note 'INSERT': insert, *note 'UPDATE': update, and so forth) on the remote table.
PASSWORD: (Optional) The corresponding password for USER_NAME.
HOST_NAME: The host name or IP address of the remote server.
PORT_NUM: (Optional) The port number for the remote server. The default is 3306.
DB_NAME: The name of the database holding the remote table.
TBL_NAME: The name of the remote table. The name of the local and the remote table do not have to match.
Sample connection strings:
CONNECTION='mysql://username:password@hostname:port/database/tablename'
CONNECTION='mysql://username@hostname/database/tablename'
CONNECTION='mysql://username:password@hostname/database/tablename'File: manual.info.tmp, Node: federated-create-server, Prev: federated-create-connection, Up: federated-create
15.8.2.2 Creating a FEDERATED Table Using CREATE SERVER .......................................................
If you are creating a number of 'FEDERATED' tables on the same server, or if you want to simplify the process of creating 'FEDERATED' tables, you can use the *note 'CREATE SERVER': create-server. statement to define the server connection parameters, just as you would with the 'CONNECTION' string.
The format of the *note 'CREATE SERVER': create-server. statement is:
CREATE SERVER
SERVER_NAME
FOREIGN DATA WRAPPER WRAPPER_NAME
OPTIONS (OPTION [, OPTION] ...)The SERVER_NAME is used in the connection string when creating a new 'FEDERATED' table.
For example, to create a server connection identical to the 'CONNECTION' string:
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';You would use the following statement:
CREATE SERVER fedlink
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'fed_user', HOST 'remote_host', PORT 9306, DATABASE 'federated');To create a 'FEDERATED' table that uses this connection, you still use the 'CONNECTION' keyword, but specify the name you used in the *note 'CREATE SERVER': create-server. statement.
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),
INDEX name (name),
INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='fedlink/test_table';The connection name in this example contains the name of the connection ('fedlink') and the name of the table ('test_table') to link to, separated by a slash. If you specify only the connection name without a table name, the table name of the local table is used instead.
For more information on note 'CREATE SERVER': create-server, see note create-server::.
The note 'CREATE SERVER': create-server. statement accepts the same arguments as the 'CONNECTION' string. The note 'CREATE SERVER': create-server. statement updates the rows in the 'mysql.servers' table. See the following table for information on the correspondence between parameters in a connection string, options in the *note 'CREATE SERVER': create-server. statement, and the columns in the 'mysql.servers' table. For reference, the format of the 'CONNECTION' string is as follows:
SCHEME://USER_NAME[:PASSWORD]@HOST_NAME[:PORT_NUM]/DB_NAME/TBL_NAMEDescription 'CONNECTION' *note 'CREATE SERVER': create-server.'mysql.servers' string option column
Connection scheme
SCHEME 'wrapper_name' 'Wrapper'
Remote user
USER_NAME 'USER' 'Username'
Remote password
PASSWORD 'PASSWORD' 'Password'
Remote host
HOST_NAME 'HOST' 'Host'
Remote port
PORT_NUM 'PORT' 'Port'
Remote database
DB_NAME 'DATABASE' 'Db'
File: manual.info.tmp, Node: federated-usagenotes, Next: federated-storage-engine-resources, Prev: federated-create, Up: federated-storage-engine
You should be aware of the following points when using the 'FEDERATED' storage engine:
The following items indicate features that the 'FEDERATED' storage engine does and does not support:
The remote server must be a MySQL server.
The remote table that a 'FEDERATED' table points to must exist before you try to access the table through the 'FEDERATED' table.
It is possible for one 'FEDERATED' table to point to another, but you must be careful not to create a loop.
A 'FEDERATED' table does not support indexes in the usual sense; because access to the table data is handled remotely, it is actually the remote table that makes use of indexes. This means that, for a query that cannot use any indexes and so requires a full table scan, the server fetches all rows from the remote table and filters them locally. This occurs regardless of any 'WHERE' or 'LIMIT' used with this *note 'SELECT': select. statement; these clauses are applied locally to the returned rows.
Queries that fail to use indexes can thus cause poor performance and network overload. In addition, since returned rows must be stored in memory, such a query can also lead to the local server swapping, or even hanging.
Care should be taken when creating a 'FEDERATED' table since the index definition from an equivalent 'MyISAM' or other table may not be supported. For example, creating a 'FEDERATED' table with an index prefix fails for note 'VARCHAR': char, note 'TEXT': blob. or *note 'BLOB': blob. columns. The following definition in 'MyISAM' is valid:
CREATE TABLE `T1`(`A` VARCHAR(100),UNIQUE KEY(`A`(30))) ENGINE=MYISAM;The key prefix in this example is incompatible with the 'FEDERATED' engine, and the equivalent statement fails:
CREATE TABLE `T1`(`A` VARCHAR(100),UNIQUE KEY(`A`(30))) ENGINE=FEDERATED
CONNECTION='MYSQL://127.0.0.1:3306/TEST/T1';If possible, you should try to separate the column and index definition when creating tables on both the remote server and the local server to avoid these index issues.
Internally, the implementation uses note 'SELECT': select, note 'INSERT': insert, note 'UPDATE': update, and note 'DELETE': delete, but not *note 'HANDLER': handler.
The 'FEDERATED' storage engine supports note 'SELECT': select, note 'INSERT': insert, note 'UPDATE': update, note 'DELETE': delete, note 'TRUNCATE TABLE': truncate-table, and indexes. It does not support note 'ALTER TABLE': alter-table, or any Data Definition Language statements that directly affect the structure of the table, other than *note 'DROP TABLE': drop-table. The current implementation does not use prepared statements.
'FEDERATED' accepts *note 'INSERT ... ON DUPLICATE KEY UPDATE': insert-on-duplicate. statements, but if a duplicate-key violation occurs, the statement fails with an error.
Transactions are not supported.
'FEDERATED' performs bulk-insert handling such that multiple rows are sent to the remote table in a batch, which improves performance. Also, if the remote table is transactional, it enables the remote storage engine to perform statement rollback properly should an error occur. This capability has the following limitations:
* The size of the insert cannot exceed the maximum packet size
between servers. If the insert exceeds this size, it is
broken into multiple packets and the rollback problem can
occur.
* Bulk-insert handling does not occur for *note 'INSERT ... ON
DUPLICATE KEY UPDATE': insert-on-duplicate.There is no way for the 'FEDERATED' engine to know if the remote table has changed. The reason for this is that this table must work like a data file that would never be written to by anything other than the database system. The integrity of the data in the local table could be breached if there was any change to the remote database.
When using a 'CONNECTION' string, you cannot use an '@' character in the password. You can get round this limitation by using the *note 'CREATE SERVER': create-server. statement to create a server connection.
The 'insert_id' and 'timestamp' options are not propagated to the data provider.
Any *note 'DROP TABLE': drop-table. statement issued against a 'FEDERATED' table drops only the local table, not the remote table.
'FEDERATED' tables do not work with the query cache.
User-defined partitioning is not supported for 'FEDERATED' tables.
File: manual.info.tmp, Node: federated-storage-engine-resources, Prev: federated-usagenotes, Up: federated-storage-engine
The following additional resources are available for the 'FEDERATED' storage engine:
File: manual.info.tmp, Node: example-storage-engine, Next: storage-engines-other, Prev: federated-storage-engine, Up: storage-engines