(Symmetricds Quick Start Guide) Chapter 1. Quick Start Guide

This document is the first chapter of the SymmetricDS3.6.14 document, which translates the Quick Start Guide document to help readers quickly build a Symmetricds cluster and popularize some basic conceptual terminology.

This document describes how to synchronize two databases of the same schema between two SYMMETRICDS nodes. The following example builds a distribution business model with a central database (we call it root or corp node) and a database of multiple retail stores (we call it a client or a store node). For this tutorial, we will have only one store (store) node, such as. If you wish, you can extend this example by yourself after the tutorial is finished, and configure the second store.

In this example, we will install two separate Symmetricds to represent two different servers. One represents the store server, and one represents the corp server. Each SYMMETRICDS installation copy will be responsible for a database, i.e. a SYMMETRICDS is a node in a Symmetricds term. A Symmetricds installation copy is responsible for a database, representing a node, this configuration is most commonly used (we can choose to configure an installation copy responsible for two nodes, which is called multi-homing, will be discussed at the end of the tutorial). You are likely to run two copies of Symmetricds on a machine, we will run two Symmetricds install replicas on two different ports. We will use 8080 to run the corp server, 9090 to run the store server, such as.

The installation copy of the Corp Symmetricds will be responsible for capturing data changes in the store side of item (Item table and Item_selling_price table), like ItemNumber, description and price of the store. The store-side Symmetricds captures changes in sales transactions (sale_transaction tables and Sale_return_line_item tables), such as time and goods sold. Price information is sent only to specific stores associated with the price, thereby reducing the number of pricing information data sent to each store. For example, in other words, the pricing information for a store's 001 item is only sent to the store 001 database, not to the store 002 database.

This simple configuration always causes the client to initiate communication with the root node, which is a fairly common configuration. In this configuration, the client will periodically append data from the database server to the root node, and the client will also push the captured changes to the root node.

The overview is enough, let's start with the following steps:

1. Installation and configuration of two Symmetricds applications

2. Create a Symmetricds configuration file and a database table to store corp data and store data.

3. Create retail data in the corp database

4. Start the SYMMETRICDS server and register the store node with the corp node

5. Send the initial load of data to the store node

6. Perform a data push and data pull operation

7. Verifying the information sent and received by batch

Installing Symmetricds

First, we will install a copy of the two Symmetricds, and then configure its connection information to the database separately:

1. Download the Symmetricds installation file

2. Create two folders on behalf of two machines. A copy of the installation that holds the corp node and another installation copy of the store node. For example, you can name these two folders Sym-corp and sym-store001, which you will assume is named this way. Unzip the downloaded file above into two folders.

3. The property file is used to hold the minimum configuration information required to start Symmetricds. Copy the corp sample properties file to the Corp engine folder, and then copy the store properties file to the store's engine folder. If you are using the suggested folder name above, you will do the following copying:

Samples/corp-000.properties to Sym-corp/symmetric-ds-3.x.x/engines/

和

Samples/store-001.properties to Sym-store001/symmetric-ds-3.x.x/engines/

4. Browse through the two properties files to view the different configurations. For example, the root node given a group with a value of Corp Id,store node given a value of store for the group Id;root node given a value of 000 for the external Id,store node given a value of 001 for the external node.

Here are the configuration properties for the two configuration files in the two-node engine directory, specifying how to connect to a specific database server (the following values are examples only):

# The class name for the JDBC Driver         db.driver=com.mysql.jdbc.driver                 # The JDBC URL used toconnect to the database         db.url=jdbc:mysql://localhost/sample                 # The user to login as Whocan Create and update tables         db.user= Symmetric                 # The password for the Userto login as         Db.password=secret

5. Next, set the following attribute in the Store-001.properties file to specify the location of the root node:

# The HTTP URL of the root node to the registration         registration.url=http://localhost:8080/sync/corp-000

6. Tip, the URL of an engine is the following common format:

Http://{hostname}:{port}/sync/{engine.name}

The engine.name part of the URL comes from the node's configuration file.

1.2. Creating and populating Your Databases

Important

You must first create a database for your root node and client node. The database that you create must be configured in the same name as the top configuration file.

First, using the following steps, create a database table of examples in the root database, load the sample data, and load the sample configuration:

1. Open a command-line prompt and go to the Samples folder of the corp installation copy

2. Create a sample table for Item,price and sale in the root node by executing the following command:

.. /bin/dbimport--engine corp-000--format xmlcreate_sample.xml

Warning messages from the command line are safe and can be ignored.

Another brief description of the properties file. During the startup phase, Symmetricds looks for one or more properties files in the engines folder. Because we have specified a-engine parameter in the command line, Symmetricds will look for a specific named file, Corp-000.properties. In this example, because there is only one property file in the engines folder, Symmetricds will load the configuration in this property file by default. Therefore, in this case, it is also possible to not specify the-engine parameter. With this parameter, running this example can reduce the error caused by starting the wrong installation copy. Symmetricds will complain about the loss of the property profile you specified.

3. Next, create a SYMMETRICDS-specific database table in the Corp node's database. These tables will contain configuration information for the synchronization operation. The following command creates all the necessary Symmetricds system tables using the automatically created attributes:

.. /bin/symadmin--engine Corp-000create-sym-tables

4. The final step, perform the following operations, load the sample commodity and transaction data and Symmetricds configuration information to the root node of the database:

.. /bin/dbimport--engine Corp-000insert_sample.sql

Important

The above operation, for MySQL, requires the execution of the Insert_sample_mysql.sql file. For case-sensitive table and column naming, MySQL replaces double quotation marks with anti-quotes.

We have now worn a Corp database table and populated our Symmetricds configuration information and sample data. Next, we'll create a sample database table in the store node's database, ready to receive the data.

5. Open a command prompt and go to the Samples folder of the Symmetricds installation copy of the store node.

6. Execute the following command to create an empty sample table in the database of the build store node:

.. /bin/dbimport--engine store-001--format xmlcreate_sample.xml

Warning messages from the command line are safe and can be ignored. You can view the Create_sample.xml file to see what it has.

Log in to the database and verify the tables in two databases:

1. Find the Item table that needs to be synchronized from the Corp node to the store node: item and Item_selling_price.

2. Locate the sale table that needs to be from the store node to the Corp node: Sale_transaction and Sale_return_line_item.

3. Locate the Symmetricds system tables, which are prefixed with sym_, such as Sym_channel,sym_trigger,sym_router and Sym_trigger_router.

4. Verify that the Item table for the CORP node has sample data

1.3. Starting Symmetricds

The database setup and configuration in this tutorial is now complete. It's time to start using Symmetricds. Now let's start with two SYMMETRICDS nodes and watch their log output.

1. Open two command-line prompts, respectively, into the samples directory under the root directory of the two installation replicas.

2. Under the Samples directory of Corp, execute the following command to start the corp Symmetricds:

.. /bin/sym--engine corp-000--port 8080

At the first boot, the corp node will create all the trigger in the sample configuration file. Then on port 8080, corp-000 engine listens for synchronization and registration requests.

3. In the Samples directory of the STORE001 node, execute the following command to start the Symmetricds:

.. /bin/sym--engine store-001--port 9090

When this command starts the server for the store node for the first time, the SYMMETRICDS system table is created using the auto-Create attribute. It then tries to register to the Corp node (the address of the Corp node can be known via the registration URL configured earlier). Because the registration is not yet open, the store node will receive an authentication failure (403 HTTP response). We discuss this in the registration section below.

1.4. Registering a Node

When an unregistered node starts, it attempts to register to the node specified by the registration URL (in almost all cases, it is the root node). Manage other nodes on the network in the registration node set by allowing registration and returning configuration information for the registered nodes. In this tutorial, the registration node is the root node, the corp node, which simultaneously participates in synchronous operations with other nodes.

So, next, we need to open the registration for the store node so that the store node can receive the data from the initial load, then it can receive data from Corp, or it can send data to the corp node. There are several ways to accomplish this task. We will use Symmetricds's management function to execute a command on the corp node (because this node is responsible for registration management).

1. The Symmetricds application with the corp node and the store node has been started, and a new command line prompt opens to the Samples directory at the root of the installation copy of the corp node.

Open the Register feature of the store node by executing the following command:

.. /bin/symadmin--engine corp-000 open-registration Store 001

Now Corp's registration function has been opened for group Store,external with a node ID of 001. The group and external ID information corresponds to the configuration in the Store-001.properties configuration file in the store node. In Symmetricds, each node is assigned to a node group, and then a external ID is given to represent the application. In this tutorial, we named the store group as store, and then we use a 001-based numeric identifier as the external ID (000 for the corp node). More information about node groups is described in the next section.

2. Check the log output of the store node to see if it was successfully registered with the Corp node. The store is configured to attempt registration at random intervals of up to 1 minutes. Once the registration is successful, the corp and store nodes will start synchronizing.

1.5. Sending an Initial Load

Next, we will send an initial load of data to the store node and once again use the node management feature on the corp node.

1. Open a command prompt and go to the Samples folder in the root directory of the corp installation copy. (Note that most system commands are usually issued directly on the Corp server side.) For example, all profiles are entered in Corp, and then synced to any client node. ）

2. Execute the following command to send an initial load of data to the store node:

.. /bin/symadmin--engine corp-000 Reload-node 001

After using this command, the Corp node queues each store node to pull data requests. The initial load of data includes the data in each table that is configured for synchronization.

3. Observe the log output of two nodes and view the data transfer. The store is configured to pull data from the corp node once every minute.

1.6 Pulling Data

Next, we will make some changes to the item data of the central database (we will add a new product) and then watch the data being pulled to the store node.

1. Open an interactive SQL Session with the corp database

2. In store001 and store002 (only store001 in this case), add a new commodity:

INSERT INTO "item" ("item_id", "name") VALUES (110000055, ' Soft Drink ') and insert INTO "Item_selling_price" ("item_id", " store_id "," Price ") VALUES (110000055, ' 001 ', 0.65); INSERT INTO "Item_selling_price" ("item_id", "store_id", "Price") VALUES (110000055, ' 002 ', 1.00);

Once statement is committed, the data changes will be captured by Symmetricds and then queued for the store node to pull the data.

4. Observe the log output of the two nodes to see the data transfer status. The store is configured to pull data from the corp node once every minute.

5. In this tutorial, Item_selling_price is configured as a column-matching router, and changes to specific pricing data are sent only to nodes that match store_id and external IDs. Execute the SQL statement on the database of the store node to verify that there is a new data arrival. In this case, the first record will be sent only to store 001.

1.7. Pushing Data

Now, we're going to fake a deal and then watch how Symmetricds pushes the transaction information to the central node. (In this we can find that the pull and push of data are all from the node, the central node is only responsible for management)

1. Open a SQL session to the database of the store node

2. Add a new transaction information to the store node's database.

INSERT INTO "sale_transaction" ("tran_id", "store_id", "workstation", "Day", "seq") VALUES (1000, ' 001 ', ' 3 ', ' 2007-11-01 ') , insert INTO "Sale_return_line_item" ("tran_id", "item_id", "Price", "Quantity") values (1000, 110000055, 0.65, 1);

Once statement is committed, data modifications are captured and queued to the store node for push to the central node.

3. Observe the log output of the two nodes to see the data transfer status. The store node is configured to push data once per minute to the corp node.

1.8 Verifying Outgoing Batches

Earlier we demonstrated that data has been pushed and pulled, and we will describe how you get the batch of data and the related information sent. A batch is used to track and send one or more data changes to a given node. The node where the data resides creates a batch, and the node that receives the data receives the data and responds.

In addition, in Symmetricds, tables are grouped into different data channels, in order to allow different data types to be synchronized in the case of errors in some data types. For example, if the batch that is going to be sent to the given channel has an error, the batch will be tried in every synchronization operation of the channel until the batch is not in error. Only the rest of the batch sent to the channel will be delivered if the batch does not have an error. In this way, the order in which data changes occur at the data source can be guaranteed to be sent to the destination in the same order, and these changes are applied in the same order at the destination. However, a batch without errors on the channel is not blocked by another channel with the wrong batch. In this way, data changes on one channel are not blocked by an error on another channel.

You can view the batch information emitted by using the following steps:

1. Open a session connected to the database server of the corp or store node

2. Confirm the data changes that have been captured:

SELECT * from Sym_data ORDER by data_id Desc;

Each row of data in this table represents a row of data that changes. The data_id is sequentially growing, so the most recent data ID should be associated with your SQL insert. Event_type is I for Insert,u representative of Update,d on behalf of delete. For inserts and updates, the captured data is in the Row_data column, and for updates and deletions, the value of the primary key is in the Pk_data column.

3. Using the data_id obtained in the previous step, confirm that the data changes in a batch:

SELECT * from sym_data_event where data_id =?;

Batch is created based on the necessary purpose node information, called the route Job. As part of the route job, data changes using batch_id are assigned to a batch,batch_id that is used when tracking and synchronizing data. Connection information between batch and data is connected through the Sym_data_event table.

4. Using the batch_id obtained in the previous step, go to the person data change is batch, send to the destination and get response:

SELECT * from sym_outgoing_batch where batch_id =?;

The initial batch, which has a state of NE (new), was created and not yet sent to a node. Once the response from the node is received, the batch status changes to OK, the send fails, and it becomes ER when it is successfully sent. If this batch fails, the batch's Error_flag will be set to 1, and the failed batch may be successful when it tries again, and this state needs to be changed.

Understanding the three tables, and the fourth table we discussed in the next section, is the key to diagnosing the synchronization problems that may be encountered. When you use Symmetricds, you can better understand how SYMMETRICDS works, whether in an experimental or production environment, by taking the time to monitor these tables.

1.9. Verifying Incoming Batches

The node that receives the data maintains the batch information that the node responds to and the statistics related to the loaded data. Duplicate batch defaults are ignored, but can be modified to incoming.batches.skip.duplicates change this behavior.

Use the following steps to observe the received batch information:

1. Open a SQL session that interacts with the database of the corp or store node

2. Use the batch_id obtained in the previous section to confirm the batch information received and responded to:

SELECT * from sym_incoming_batch where batch_id =?;

A batch represents a collection of data changes that the node loads. The nodes that create and send this batch are recorded in the table, and the status of batch is OK when it is successful, and ER when it fails.

1.10. multi-homing

This is the end of our Quick Start Guide. We have successfully created two synchronization nodes and then performed synchronous operations in two databases. Now let's go back and discuss one of the issues discussed in the first step of the guide. That is, in the guide, let you install two copies of the Symmetricds step.

In the example above, we replaced a property file in the engines directory of each SYMMETRICDS installation replica. When Symmetricds is started, Symmetricds is initialized and then a Symmetricds engine is created based on the provided configuration file (again, an engine is a SYMMETRICDS node that is responsible for a particular database).

In fact, a SYMMETRICDS application can start multiple engine at the same time. When Symmetricds starts, it looks for a '. Properties ' file at the end of the engines directory. It will start a Symmetricds engine for each configuration file found. The-engine parameter in the command line prompt overrides the top action, which causes Symmetricds to start only one engine specified on the command line. This is called ' multi-homing ' in the case of a Symmetricds installation replica that launches multiple engine.

For the above example, if we want to change to the multi-homing architecture, just do the following steps:

1. Install a SYMMETRICDS copy instead of the top two installation copy. Does not need a directory to represent two machines.

2. Copy two configuration files from the Samples directory of the installation copy to the engines directory.

3. All running commands above are run again in the Samples directory of this installation replica

4. When you start Symmetricds, you do not need to specify a specific engine, which will start two engine. This command is still running from the Sampls directory, as follows:

.. /bin/sym--port 8080

Note that using the command above, we are not using port 9090. Symmetricds now monitors the data exchange between the Corp and the store engine on port 8080.

5. In addition to the command to start the server, all other commands are executed using the-engine parameter to specify the engine. Because you want to give a specific node (engine) to handle the command, like opening the registration, initiating the initial load from Corp to the store and so on.

(Symmetricds Quick Start Guide) Chapter 1. Quick Start Guide