Example of using Federated tables for MySQL Databases

The Federated table function of MySQL is similar to the Link function of Access. It can be used to read or operate tables with the same table structure in a remote database.

The Federated table function of MySQL is similar to the Link function of Access. It can be used to read or operate tables with the same table structure in a remote database.

In the database system, the Union table (chain table) is an interesting application and provides a simple method.
To share the data of the remote database, but it does not need to be as complex and troublesome as configuring the cluster or copying. If your database
If you do not have high performance requirements or need to temporarily share a remote data, consider using
MySQL Federated engine.

The Federated table function of MySQL is similar to the Link function of Access.
Operate on data in tables with the same table structure in a remote database. Only MySQL Union tables can be linked to MySQL
Server, unlike the Acccess database chain table, in addition to the database type that can be linked to Microsoft, you can also
Use ODBC or other connection methods to link to other types of databases.

Official reference:

Federated-Union table:

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.

FEDERATED storage engine allows you to directly access data in a remote MySQL database without using
Replication or cluster technology. Querying a local FEDERATED table automatically extracts data from a remote (union) table.
No data is stored in the local table.

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 it, you must start the MySQL server binary using the -- federated option.

The-DWITH_FEDERATED_STORAGE_ENGINE parameter must be included during installation and compilation, and the -- federated option must be included during startup.

When you create a FEDERATED table, the table definition is the same as MyISAM,
CSV or InnoDB, but the physical storage of the data is handled on a remote server.


A federated table consists of two parts:

* Remote servers and their database tables, including the table (structure) Definitions stored in the. frm file and related tables.
The remote table type can be supported by any remote mysqld server, including MyISAM or InnoDB.

* The local server and database table. The table definition matches the corresponding table on the remote server. Table definition is stored in the. frm file,
However, there is no data file on the local server, but the table definition contains a connection string pointing to a remote table.

When performing queries and (SQL) Statements on a local joint table, operations on local data files will be performed as usual,
For example, insert, update, or delete will be sent to the remote server for execution.
Update the data file or return matched rows from the remote server.

___________________________
|
| LocalServer | RemoteServer |
| _____________ | ______________ |
|
|
V
___________________
|
| Federated | Remote |
| Table | <--- Queries/Data ---> | Table |
| ___________ | ________ |
| ________________
|
V
______________________________
| ()
|. Frm File |. frm File | (Data)
| ___________ | (________)


When the client issues an SQL statement to the FEDERATED table,
The information flow between the local server (executed SQL statement) and remote server (where data is physically stored) is as follows:

1. The storage engine browses each column of the FEDERATED table and constructs the corresponding SQL statement pointing to the remote table.
2. the SQL statement is sent to a remote server using the MySQL client API.
3. When the remote server processes an SQL statement, the local server collects any result (the number of affected rows or result sets) generated by the statement ).
4. if this statement generates a result set, each column is converted to the internal storage engine format that the FEDERATED engine expects,
This format can be used to display the results to clients that issue the original statement.

The local server uses the c api function of the MySQL client to communicate with the remote server. It calls mysql_real_query ()
To send SQL statements. To read a result set, use the mysql_store_result () function.
The mysql_fetch_row () function is used for a row of records.


To create a Federated table, follow these steps:

1. CREATE a TABLE on the remote server and use show create table to display the result;
2. Create a table on the local server and use a consistent table structure, but add the connection information linking the local table to the remote table;

For example, create the following data table on the remote server:

Create table fed_tb (
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;


To create a local table to be joined to a remote table, you can select either of the following options:

* Directly use the specified string (including the server, login name, and password) to connect to the remote table to create the Union table;
* Use the create server statement to establish a shared connection, which simplifies the process of creating a Union table;


Important: when creating a local table, you must use a field definition that is exactly the same as that of the remote table;

Note: You can add indexes to the local table to improve the performance, because the query will be
Add the WHERE condition and execute it locally on the remote server. This will reduce network traffic, otherwise it will
Request the entire table and send it to the local device for processing.

,