MySQL uses the federate engine

1. Check to see if the MySQL database has the Federate engine installed

Federates Support No indicates not turned on.

2. Turn on the Federated engine

Windows in the My.ini to add federated, you can open;

Linux, you need to add a compile-time option, and then add federated in My.ini to open.

　3. Establish a Remote Data link

If: There is a database Dbtesta on the ServerA, there is a database dbtestb on the ServerB, to establish ServerB on the DBTESTB database ServerA the table on the dbtesta of the data sheet link Remote_ Tabletesta, the following statement:

Here is the code snippet:

CREATE TABLE Remote_tabletesta ... engine=federated connection = 'mysql://root:[email Protected]:3306/dbtesta/tabletesta ';

4. Using a remote Data sheet link

As the above example, the table Remote_tabletesta is operated directly on the database Dbtestb of ServerB, that is, the table Dbtesta that operates the database Tabletesta on ServerA.

　5.MySQL using the Federate engine to operate the local map remote operations table

　　

Consider such a scenario, there are multiple agent system (each has its own database) after the completion of their own operations, will trigger the respective database of the stored procedures for complex operations, each completed after each of the remote master machine to the database of the table to erase a message, to notify the completion of the operation. There are two scenarios to be implemented in a preliminary scenario.

1. In the agent system, the stored procedure is called through Java code until the completion of the execution of the stored procedure, and then the end information is written to the Master System database.
2. Through the establishment of a remote mapping table for each agent system, the implementation of the agent system after the completion of the stored procedure, through the stored procedure to write information to the local mapping table, MySQL automatically synchronize the data to master. (here also highlights the MySQL a short board, local stored procedures can not call the remote database stored procedures, can not operate the remote database table, seemingly oracle,sqlserver, etc. can be, otherwise it is not so troublesome ╮(╯▽╰)╭)

Because in the actual project, the first way will be because the stored procedure runs too long (half an hour or more), will cause the performance of the agent system, forced by the rogue to use the second scenario.

First make sure that the federated engine is turned on and that you can see whether it is installed via the show Engines command. Then the steps are as follows:

First build the Projects table on the master machine, we only need to set up the mapping table in each agent database projects

CREATE TABLE ' Projects ' (
' project_id ' INT (one) not NULL auto_increment,
' Project_code ' VARCHAR (not NULL),
' Test_code ' VARCHAR (not NULL),
' Create_time ' BIGINT (one) null DEFAULT null,
' Result ' VARCHAR (255) null DEFAULT NULL,
' ts ' BIGINT (one) not NULL,
PRIMARY KEY (' id ')
)
Collate= ' Utf8_general_ci '
engine=federated
connection= ' Mysql://user:[email protected]:3306/testdb/tb_projects_tests ';

Note the setting of the connection statement requires the user name of the remote master database, the password and corresponding information for the mapping table, and then executes an INSERT statement on the local projects after each stored procedure completes.

PS: It is important to note that this method is not suitable for synchronous operation of large data volumes.

　

MySQL uses the federate engine