Mysql之跨库跨主机查询（FEDERATED ）

Mysql存储引擎

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.

FEDERATED存储引擎允许您访问远程MySQL数据库中的数据，而无需使用复制或集群技术。查询本地FEDERATED表会自动从远程（联邦）表中提取数据。本地表中未存储任何数据。
如果从源代码构建MySQL，则要包含FEDERATED存储引擎，请使用-DWITH_FEDERATED_storage_engine选项调用CMake。
FEDERATED存储引擎在运行的服务器中默认不启用；要启用FEDERATED，必须使用–FEDERATED选项启动MySQL服务器二进制文件。
要检查FEDERATED引擎的源代码，请查看MySQL源代码分发的storage/federation目录。

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 Figure 15.2, “FEDERATED Table Structure”.

Figure 15.2 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:

1. The storage engine looks through each column that the FEDERATED table has and constructs an appropriate SQL statement that refers to the remote table.
2. The statement is sent to the remote server using the MySQL client API.
3. 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).
4. 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() to send the statement. To read a result set, it uses mysql_store_result() and fetches rows one at a time using mysql_fetch_row().

当您使用标准存储引擎之一（如MyISAM、CSV或InnoDB）创建表时，该表由表定义和相关数据组成。创建FEDERATED表时，表定义是相同的，但数据的物理存储是在远程服务器上处理的。
FEDERATED表格由两个元素组成：
具有数据库表的远程服务器，该数据库表又由表定义（存储在.frm文件中）和相关联的表组成。远程表的表类型可以是远程mysqld服务器支持的任何类型，包括MyISAM或InnoDB。
具有数据库表的本地服务器，其中表定义与远程服务器上相应表的定义相匹配。表定义存储在.frm文件中。但是，本地服务器上没有数据文件。相反，表定义包含一个指向远程表的连接字符串。
当在本地服务器上的FEDERATED表上执行查询和语句时，通常会从本地数据文件中插入、更新或删除信息的操作被发送到远程服务器执行，在那里它们更新远程服务器上的数据文件或从远程服务器返回匹配行。
联邦表格设置的基本结构如图15.2“联邦表格结构”所示。

内容在周围的文字中描述。
当客户端发布引用FEDERATED表的SQL语句时，本地服务器（执行SQL语句的地方）和远程服务器（物理存储数据的地方）之间的信息流如下：
存储引擎查看FEDERATED表的每一列，并构造一个引用远程表的适当SQL语句。
该语句使用MySQL客户端API发送到远程服务器。
远程服务器处理该语句，本地服务器检索该语句产生的任何结果（受影响的行数或结果集）。
如果该语句生成一个结果集，则每一列都将转换为FEDERATED引擎所期望的内部存储引擎格式，并可用于向发出原始语句的客户端显示结果。
本地服务器使用MySQL客户端C API函数与远程服务器通信。它调用mysql_real_query（）来发送该语句。为了读取结果集，它使用mysql_store_result（），并使用mysql_fetch_row（）一次获取一行。

mysql FEDERATED

MySQL的FEDERATED存储引擎允许你使用一个MySQL服务器的表作为对另一个MySQL服务器信息的代理。这是一种非常有趣的方式，可以让你在不同服务器间共享数据。

但是，需要注意的是，FEDERATED存储引擎已经从MySQL 5.7.15和8.0.0中移除了。

以下是一个创建FEDERATED表的例子：

CREATETABLE federated_table (    id INT,    data VARCHAR(30))ENGINE=FEDERATEDCONNECTION='mysql://user@remote_host:port/database_name/table_name';

在这个例子中，federated_table是你在本地创建的表。当你查询这个表时，MySQL会连接到远程服务器，并从指定的数据库和表中获取数据。

需要注意的是，CONNECTION参数中的user@remote_host:port部分应该替换为实际的用户名、远程主机和端口。database_name/table_name应该替换为实际的远程数据库名和表名。

如果你正在使用的MySQL版本是5.7.15或者更高，或者你正在使用MySQL 8.0或者更高的版本，你将不能使用FEDERATED存储引擎。在这种情况下，你需要寻找其他方式来共享数据，例如使用MySQL的复制功能，或者使用其他数据库技术。

To create a FEDERATED table you should follow these steps:

1. Create the table on the remote server. Alternatively, make a note of the table definition of an existing table, perhaps using the SHOW CREATE TABLE statement.
2. 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 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.

To use the first method, you must specify the CONNECTION string after the engine type in a 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_name

Where:

• 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 (SELECT, INSERT, 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'

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 CREATE SERVER statement to define the server connection parameters, just as you would with the CONNECTION string.

The format of the 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 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 CREATE SERVER, see Section 13.1.17, “CREATE SERVER Statement”.

The CREATE SERVER statement accepts the same arguments as the CONNECTION string. The 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 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_name

Description	CONNECTION string	CREATE SERVER option	mysql.servers 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

FEDERATED Storage Engine Notes and Tips

You should be aware of the following points when using the FEDERATED storage engine:

• FEDERATED tables may be replicated to other replicas, but you must ensure that the replica servers are able to use the user/password combination that is defined in the CONNECTION string (or the row in the mysql.servers table) to connect to the remote server.

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 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 VARCHAR, TEXT or 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 SELECT, INSERT, UPDATE, and DELETE, but not HANDLER.
• The FEDERATED storage engine supports SELECT, INSERT, UPDATE, DELETE, TRUNCATE TABLE, and indexes. It does not support ALTER TABLE, or any Data Definition Language statements that directly affect the structure of the table, other than DROP TABLE. The current implementation does not use prepared statements.
• FEDERATED accepts INSERT ... ON DUPLICATE KEY UPDATE 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 INSERT ... ON DUPLICATE KEY UPDATE.
• 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 CREATE SERVER statement to create a server connection.
• The insert_id and timestamp options are not propagated to the data provider.
• Any 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.