MySQL uses the Federate engine to map local tables to remote operation tables.

Source: Internet
Author: User
In this scenario, multiple agent Systems (each with its own database) will trigger complex operations on the stored procedures of their respective databases.

In this scenario, multiple agent Systems (each with its own database) will trigger complex operations on the stored procedures of their respective databases.

In such a scenario, after multiple agent Systems (each with their own database) perform their own operations, complex operations will be triggered for the stored procedures of their respective databases, after each operation is complete, you need to insert a message to the database table of a remote Master machine to notify the operation. There are two preliminary ideas for implementation.

  • In the agent system, the stored procedure is called through java code until the execution of the stored procedure is complete, and then the execution completion information is written into the database of the Master System.
  • By creating a remote ing table for each database in the agent system, the agent system writes information to the local ing table after the stored procedure is completed, mysql automatically synchronizes data to the Master. (This also highlights the short board of mysql. The local stored procedure cannot call the stored procedure of the remote database, nor can it operate the table of the remote database. It seems that Oracle, sqlserver, and so on can all be used, otherwise, you don't have to worry so much about failover)
  • In actual projects, the first method will affect the performance of the agent system because the storage process runs for too long (half an hour or longer, the second solution was used by rogue attackers.

    First, make sure that the federated engine is enabled. You can run the show engines command to check whether the federated engine is installed. The specific steps are as follows:

    First, create the projects table on the Master machine. We only need to create the projects ing table projects in each agent database.

    Create table 'project '(
    '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: password@202.59.232.211: 3306/testdb/tb_projects_tests ';

    Pay attention to the setting of the connection statement. The user name, password, and the corresponding information of the ing table of the remote Master database are required. Then, execute an insert Statement on the local projects after each stored procedure is completed.

    PS: This method is not suitable for synchronization of large data volumes.

    Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.