MySQL Remote table access settings

MySQL Remote table access settings This blog post is reproduced from http://blog.itpub.net/9406448/viewspace-223946

Information on the----official website

Using the MySQL federated storage Engine, there is no local data file for the table (for example, No. MyD file). Instead, a remote database stores the data that should normally be in the table. This makes it necessary for the MySQL client API to read, delete, update and insert the data into use. Data retrieval is initialized by a select * from tbl_name SQL statement. To read this result, by using the Mysql_fetch_row () C API function, the row is taken one at a time and then converted from the column in the select result package to the desired format of the Federated processor.

The basic flow is as follows:

1. SQL calls are published locally

2. MySQL Processor API (data in processor format)

3. MySQL Client API (data is converted to SQL Call)

4. mysql client API, remote database

5. Convert the result package (if any) to the processor format

6. Local count of result rows or rows affected by the processor API

The steps to use the federated table are very simple. Typically, you run two servers, either on the same host or on a different host. (It is also possible for a federated table to use other tables that are managed by the same server.) Although there are very few points to do so).

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

CREATE TABLE test_table (

ID Int (a) is not NULL auto_increment,

Name varchar (+) not NULL default ' ',

other int (a) not NULL default ' 0 ',

PRIMARY KEY (ID),

KEY name (name),

KEY Other_key (Other)

)

Engine=myisam

DEFAULT charset=latin1;

The engine table option may name any storage engine, and the table needs to be not a MyISAM table.

Next, create a federated table for accessing the remote table on the local server:

CREATE TABLE federated_table (

ID Int (a) is not NULL auto_increment,

Name varchar (+) not NULL default ' ',

other int (a) not NULL default ' 0 ',

PRIMARY KEY (ID),

KEY name (name),

KEY Other_key (Other)

)

engine=federated

DEFAULT charset=latin1

connection= ' Mysql://[email protected]_host:9306/federated/test_table ';

( note : CONNECTION replacement is used in previous versions of MySQL in comment).

In addition to the engine table option should be federated, and the Connection table option is for federated to indicate how to connect to the connection string on the remote server, the table must have the same structure as the remote table.

The federated engine only creates test_table.frm files in the federated database.

Remote host information indicates the remote server to which the local server is connected, and the database and table information indicates which remote table is to be used as a data file. In this example. The remote server is designated to run as a remote host on port 9306, so you have to start the server and let it listen on port 9306.

The general form of the connection string in the connection option is as follows:

scheme://user_name[:password]@host_name[:port_num]/db_name /Tbl_name

Only MySQL at this point is supported for scheme, password and port number when optional.

Here are some examples of connection strings:

Connection= ' Mysql://username:[email protected]:p ort/database/tablename '

connection= ' Mysql://[email protected]/database/tablename '

connection= ' Mysql://username:[email protected]/database/tablename '

Using connection for the specified connection string is optional and may change in the future. Remember this when you use the Federated table, because it means that when that change occurs in the future, it may be required.

Because any used password is present in the connection string as plain text, it can be used by any user who makes the Federated table use Show CREATE table or Show table status, or in Information_ The user who queries the tables table in the schema database is seen.

Federated supports and does not support the following:

· In the first version, the remote server must be a MySQL server. Federated support for other database engines may be added in the future.

· The remote table that the federated table points to must exist before you access it through the federated table.

· It is possible for a federated table to point to another federated table, but you must be careful not to create a loop.

· There is no support for the transaction.

· If the remote table has changed, there is no way for the federated engine to know. The reason for this is that the table must work like a data file, except that nothing else in the database will be written. If there are any changes to the remote database, the integrity of the data in the local table may be corrupted.

· The Federated storage Engine supports Select, INSERT, UPDATE, delete, and index. It does not support ALTER TABLE, DROP table, or any other data definition language statement. The current implementation does not use pre-prepared statements.

· Execution uses SELECT, INSERT, update, and delete, but not handler.

· The federated table cannot have no effect on the query cache.

Some of these limitations may be eliminated in future versions of the Federated processor

MySQL Remote table access settings