Multi-master-one-slave Database Synchronization solution based on multidimensional ricds

Recently, the team has a Data Synchronization requirement. Generally, two or more database (MariaDB) servers use the VPN channel to synchronize data with one database. First, they do not care about the VPN channel communication problem, this kind of multi-master and One-Slave Data Synchronization requirement has also been achieved through the replication function configuration of MySQL itself, but it does not exist either in MySQL or MariaDB.

Recently, the team has a Data Synchronization requirement. Generally, two or more database (MariaDB) servers use the VPN channel to synchronize data with one database. First, they do not care about the VPN channel communication problem, this kind of multi-master and One-Slave Data Synchronization requirement has also been achieved through the replication function configuration of MySQL itself, but it does not exist either in MySQL or MariaDB.

Recently, the team has a Data Synchronization requirement. Generally, two or more database (MariaDB) servers use the VPN channel to synchronize data with one database. First, they do not care about the VPN channel communication problem, for this kind of multi-master and One-Slave Data Synchronization requirement, we also tried to implement it through the replication function configuration of MySQL itself, however, it seems that neither MySQL nor MariaDB has yet implemented a multi-master and One-slave replication solution (officially speaking, MariaDB has been supported since 10.0 and will be appreciated by the flowers ).

Two solutions are preliminarily determined:

1. Implement Service Code by coding, regularly capture data from several master databases and integrate the data into the slave database.

2. through third-party open-source solutions (in fact, it is also a service code, but the encapsulation implementation is better), although the configuration is cumbersome, but the synchronization efficiency is much higher.

Next, we will record the use process of a third-party synchronization solution called SymmetricDS (hereinafter referred to as S) tested. There are few Chinese materials and there are some version differences, as a result, some steps cannot be passed or errors are reported at all. I simplified some operations and did not follow the official instructions. In view of the limited level of English, many statements are based on my own understanding, if you have different opinions, please give a comment and correct or communicate with others.

There are roughly three reasons why you choose metrics ricds:

1. The platform is independent. Independent from other component packages, the feature is implemented after independent configuration.

2. Business independence. You do not need to care about the business in the database. Directly configure the synchronized table name and other parameters.

3. Timely synchronization. Trigger-based data synchronization ensures the timeliness of data transmission.

Official Address:Http://www.symmetricds.org

Version: symmetric-3.5.19-server.zip

The following is an introduction to some open-source charts in China. Reference:

Deployment. The dual-arrow header indicates two-way synchronization, instead of simple one-way replication.

Configuration data model: For each server, whether it is a host or a slave, the S service needs to be configured to listen for or execute actions, and is configured as a node representing the server, as for the real-time performance of a trigger, a database of one party changes, the trigger is first monitored by the local S service, and a synchronous request is initiated to the associated node. The associated node receives the request and responds to the request.

Data Model during running: when each node monitors data events (that is, data changes) on the local machine, the changed data is sent to the associated nodes through the trigger.

The topology of the test solution is as follows. Sun1 and sun2 perform one-way data synchronization to sun.

Due to actual hardware test environment restrictions, three new local databases are created to simulate sun, sun1, and sun2 respectively.

Each node needs to configure an s Service, and the S service code is extracted to three directories respectively:

D: \ develop \ symmetric \ sun \ symmetric

D: \ develop \ mongoric \ sun1 \ mongoric

D: \ develop \ symmetric \ sun2 \ symmetric

1. Configure the S Service of the slave sun.Go to the D: \ develop \ mongoric \ sun \ mongoric directory, copy the corp-000.properties files under the samples directory to the engines directory, samples directory is an example provided by the official website, engines is the directory where you configure your own Synchronization Service, the corp-000.properties can be said to be a template that configures the parameters of the slave node, So copy it out to make changes, the file name can be customized to modify, modify to the sunserver-000.properties.

Modify the template as follows:

Engine. name engine name, which can be understood as the service name of the current node

Jdbc driver name of the db. driver Database

Db. url database connection string

Db. user database username

Db. password Database password

The registration address of the upper-level node in registration. url. If it is a top-level node, it is set to null.

Sync. url registration address of the Local Machine http: // localhost: 8080/sync/sunserver-000

Where 8080 is the port to be occupied after the current node service starts, the sunserver-000 is the value of the first parameter engine. name

The id of the group. ID node group. The current top-level node group is named sunserver, meaning the server

External. id the number of nodes in the current node group. The group. id and external. id above can be used to locate the unique node.

The following parameters are used to configure the parameters for running the S service. Keep the default value.

2. Configure the child node sun1.Configure the service of the top-level node slave server sun. Next, configure the sub-node data host sun1 and sun2. Configure sun1. and enter D: \ develop \ mongoric \ sun1 \ mongoric directory, copy the store-001.properties file under the samples directory to the engines directory, the store-001.properties here can be said to be a template to configure the parameters of the Data host node, the configuration is similar to sun, which is renamed sunclient-001.properties here

The configuration is basically similar to that of the sun node, some of which need to be concerned:

Registration. url: Enter the registration address of the parent node. The sync. url parameter in sun configuration is displayed.

The sync. url here does not need to be configured because the node ends here. If there are node branches under sun1, You need to configure this parameter and configure this value in the registry. url of the next node. Note that I currently simulate the running of Three Node services on a single machine, so the startup ports of each node service are different to avoid conflicts. The Start port of sun1 is 7070.

Group. id the group ID here is sunclient, which means client.

3. Configure the sun2 node.Go to the D: \ develop \ mongoric \ sun2 \ mongoric directory. The subsequent steps are the same as those in the previous step.

4. Create a synchronization table.Next, you need to import data to the synchronized database. on the official website, you can run commands to enter the pivoric directory and execute SQL scripts to create tables and data. However, I tried it several times, none of them succeeded. In some cases, the reason is that the XML file format on the official website is incorrect. Simply manually create tables and data on your own.

You must create the table to be synchronized in the database. If the table structure already exists in the database, you can skip this step.

The created table is

Region table and power station table.

The specific table structure is also clear:

All node databases sun, sun1, and sun2 must be manually created and added, and the database structure should be consistent theoretically.

5. initialize the system table structure of the top-level node sun.Here, the system table refers to the synchronization service table of Ric itself, and these system tables are required in all node databases (this is what I feel sad about, there are only 20 or 30 system tables ). Fortunately, we only need to create these tables on top-level nodes. After the service is started, all child nodes are automatically created based on top-level nodes.

Run the following command to enter the D: \ develop \ mongoric \ sun \ mongoric \ engines directory:

Run

[Plain]View plaincopy

1. .. \ Bin \ symadmin -- engine sunserver-000 create-sym-tables

The system table is initialized successfully. Now you can check the database and find many more tables.

All tables with the sym prefix are not complete. :-(

6. initialize the system table data of sun on the top-level node.The previous step is to initialize the table structure. Next we will initialize the data. In fact, we still don't know how to control the synchronization logic of the secondary ricds, such as what tables need to be synchronized and which node sends the synchronization data to which node, these are all reflected in the system table data.

As for how to initialize system table data, I have referred to the insert_sample. SQL script file in the samples directory, the initialization script of the system table in the official example, and also referred to the description in Baidu Library.

(1) configure a node group

[SQL]View plaincopy

1. Insert into sym_node_group (node_group_id, description)
2. Values ('sunserver', 'power station data Center ');
3. Insert into sym_node_group (node_group_id, description)
4. Values ('sunclient', 'subpower station data Server ');

(2) configure the data synchronization mode between nodes. There are two types: push and wait (wait | pull), which are determined by data_event_action. w (wait for pull) indicates pull, and p indicates push ).

[SQL]View plaincopy

1. Insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action)
2. Values ('sunclient', 'sunserver', 'P ');
3. Insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action)
4. Values ('sunserver', 'sunclient', 'w ');

(3) configure the node information. Here, you only need to configure sun node information. After sun1 and sun2 of other subnodes are registered, the system table is automatically generated and data is inserted.

[SQL]View plaincopy

1. Insert into sym_node (node_id, node_group_id, external_id, sync_enabled)
2. Values ('000', 'sunserver', '000', 1 );
3. Insert into sym_node_security (node_id, node_password, registration_enabled, registration_time, initial_load_enabled, initial_load_time, initial_load_id, primary, rev_initial_load_id, primary, and secondary)
4. Values ('000', '100', 0, current_timestamp, 0, current_timestamp, null, null, 0, null, null, '000 ');
5. Insert into sym_node_identity values ('000 ');

(4) configure the data channel table. Tables associated with foreign keys must be defined in the same channel for association synchronization.

[SQL]View plaincopy

1. Insert into sym_channel
2. (Channel_id, processing_order, max_batch_size, enabled, description)
3. Values ('bus _ info', 1, 100000, 1, 'power Station Information Synchronization channel ');

(5) define a trigger. Define tables in the database to be synchronized. Note that the tables associated with foreign keys must have the same channel value. The excluded_column_names field in sym_trigger. You can set the table fields you do not want to synchronize.

[SQL]View plaincopy

1. Insert into sym_trigger
2. (Trigger_id, source_table_name, channel_id, last_update_time, create_time)
3. Values ('Primary Ric _ test_area ', 'Primary Ric _ test_area', 'bus _ info', current_timestamp, current_timestamp );
4. Insert into sym_trigger
5. (Trigger_id, source_table_name, channel_id, last_update_time, create_time)
6. Values ('Primary Ric _ test_bus ', 'Primary Ric _ test_bus', 'bus _ info', current_timestamp, current_timestamp );

(6) configure data routing. Configure the data direction during data synchronization, that is, from which node to which node. ROUTER_TYPE = 'column 'indicates that the data flow direction is determined by the value of a column in the table to be synchronized. The specific condition is determined by the expression: ROUTER_EXPRESSION = 'org _ code =: EXTERNAL_ID '. Default ROUTER_TYPE = 'default.

[SQL]View plaincopy

1. Insert into sym_router
2. (Router_id, source_node_group_id, target_node_group_id, router_type, create_time, last_update_time)
3. Values ('client _ 2_server ', 'sunclient', 'sunserver', 'default', current_timestamp, current_timestamp );

(7) establish the association between the trigger and the route. The trigger defined in metrics ricds will automatically generate a trigger for this table only after it is associated with the route.

[SQL]View plaincopy

1. Insert into sym_trigger_router
2. (Trigger_id, router_id, initial_load_order, last_update_time, create_time)
3. Values ('ric _ test_area ', 'client _ 2_server', 200, current_timestamp, current_timestamp );
4. Insert into sym_trigger_router
5. (Trigger_id, router_id, initial_load_order, last_update_time, create_time)
6. Values ('ric _ test_bus ', 'client _ 2_server', 200, current_timestamp, current_timestamp );

7. Start javasricds

(1) register all subnodes and run the command line to enter the directory of top-level nodes in D: \ develop \ symmetric \ sun \ symmetric \ engines.

Run the registration node commands respectively.

[Plain]View plaincopy

1. .. \ Bin \ symadmin -- engine sunserver-000 open-registration sunclient 001
2. .. \ Bin \ symadmin -- engine sunserver-000 open-registration sunclient 002

Run the injection node commands separately.

[Plain]View plaincopy

1. .. \ Bin \ symadmin -- engine sunserver-000 reload-node 001
2. .. \ Bin \ symadmin -- engine sunserver-000 reload-node 002

(2) start the service.

Start the services of the three nodes and execute the sub-nodes first.

Run the command line to enter D: \ develop \ mongoric \ sun1 \ mongoric \ engines.

Run

[Plain]View plaincopy

1. .. \ Bin \ sym -- port 7070

Run the command line to enter D: \ develop \ mongoric \ sun2 \ mongoric \ engines.

Run.

[Plain]View plaincopy

1. .. \ Bin \ sym -- port 9090

Run the command line to enter D: \ develop \ symmetric \ sun \ symmetric \ engines.

Run

[Plain]View plaincopy

1. .. \ Bin \ sym -- port 8080

Pay attention to the port number, that is, the port number of the registered address in the configuration file.

All three services are successfully enabled. You can perform the test below ..

Add a data entry in the mongoric_test_area table of the sun1 database 4 -- seattle

Add a data entry in the mongoric_test_area table of the sun2 database 5 -- tokyo

Finally, you can directly view the sun database and find:

Success!

Summary:

The functions of the secondary ricds are very complex. Here we only temporarily implement the configuration of Multi-master and One-slave structure. We still need to know a lot about the specific synchronization details.

Advantages: Real-Time Synchronization and Synchronization Based on the push-pull mechanism ensure the timeliness and integrity of data. The concurrency and thread management of events have been encapsulated. Table Structure Changes have little impact on the synchronization of configured data fields, but if you also need to synchronize new fields, you need to re-register the node and restart the service.

Disadvantage: Service Code must be configured for each node, which is complex. 41 synchronization system tables are generated in each node database. Business control is reflected in table data. Suitable for IDC environments with fixed or long-term stable network channels. If it is integrated into products for external promotion, complicated configuration and post-maintenance are also troublesome, suitable for internal server applications of the company.

Supplement: In fact, I think the biggest advantage is that for the Data Synchronization requirement, replica ricds only needs to configure which database and which table to be synchronized, regardless of the specific business of each database. If you are writing service code to regularly capture data and then insert data, you may also need to consider the business in the database, such as which table is associated with which table, the data insertion sequence, and cascade issues, for replica ricds, you only need to join the table in a synchronization channel.