Connection server on MySQL database-Federated Engine

I used SQL Server databases all the time. Recently, my company has a new project to use mysql databases, many of which are cross-Server access. SQL Server uses linked servers for cross-Server access, while mysql is implemented through the Federated engine.

To configure the Federated engine, MySQL or later is required. The specific configuration method is as follows:

1. Check whether the federated engine is installed.

Enter the command: show engines;

The result is as follows:

Engine Support Comment Transactions XA Savepoints
Memory yes Hash based, stored in memory, useful for temporary tables NO
Federated no Federated MySQL storage engine
MyISAM YES Default engine as of MySQL 3.23 with great performance NO
Blackhole yes/dev/null storage engine (anything you write to it disappears) NO
MRG_MYISAM YES Collection of identical MyISAM tables NO
Csv yes csv storage engine NO
Archive yes Archive storage engine NO
InnoDB DEFAULT Supports transactions, row-level locking, and foreign keys YES

The federated engine is not enabled.

2. Enable the federated Engine

In windows, add federated to my. ini to enable federated;

In linux, you must add the option during compilation and then add federated to my. ini to enable this function.

3. Create a remote data table Link

Assume that there is a database dbtestA on ServerA and database dbtestB on ServerB. You need to create the table tabletestA link to remote_tabletestA on the database dbtestA of ServerA on the database dbtestB of ServerB, the statement is as follows:

Create table remote_tabletestA... engine = federated connection = 'mysql: // root: 123123 @ ServerA: 3306/dbtestA/tabletestA ';

4. Use the remote data table Link

In the preceding example, the table remote_tabletestA is operated directly on the database dbtestB of ServerB, that is, the table tabletestA of the database dbtestA on ServerA.