Consider a scenario where there are multiple agent systems (each with its own database are) done after each operation, will trigger the respective database stored procedures for complex operations are required after each completed a Master of the machine to the remote database table rubbed into a message to notice the operation is completed. Initial idea there are two options to achieve.
In the agent inside the system through java code to call stored procedures, stored procedure until after the execution is completed, then the information is written to the end of the Executive Master database system.
By establishing a remote mapping tables for each agent in the system database, in order to achieve agent system stored procedure execution is completed, then the information through stored procedures written in the local mapping table from mysql automatically synchronize data to the Master. (Here also highlights mysql a short board, locally stored procedure can not call a stored procedure in the remote database, you can not operate the remote database table, looks like Oracle, sqlserver so can, or would not be so troublesome
Because in the actual project, the first approach because of run-time stored procedure is too long (half an hour or even longer), it will cause the system performance agent influential rogue forced to use the second option.
First, make sure the engine is turned on federated, you can show engines command to check whether the installation. Then follow these steps:
First, the establishment of projects in the Master machine table, we only need to establish a mapping table projects each agent database
CREATE TABLE `projects` (
`Project_id` INT (11) NOT NULL AUTO_INCREMENT,
`Project_code` VARCHAR (50) NOT NULL,
`Test_code` VARCHAR (50) NOT NULL,
`Create_time` BIGINT (11) NULL DEFAULT NULL,
`Result` VARCHAR (255) NULL DEFAULT NULL,
`Ts` BIGINT (11) NOT NULL,
PRIMARY KEY ( `id`)
COLLATE = 'utf8_general_ci'
ENGINE = FEDERATED
CONNECTION = 'mysql: // user: firstname.lastname@example.org: 3306 / testdb / tb_projects_tests';
Note that the set connection statement, the corresponding information needs remote Master database user name, password and the mapping table, and then after the completion of each stored procedure execution can execute an insert statement for local projects.
PS: It should be noted that this approach is not suitable for large amounts of data synchronization.