Capítulo 2. Instalación

Este capítulo es un tutorial práctico que demuestra como sincronizar la base de datos de ejemplo entre dos instancias en ejecución de SymmetricDS. Este ejemplo modeliza un negocio de venta al por menor que tiene una base de datos en la oficina central (llamada "raíz"), y múltiples base de datos de tiendas (llamadas "clientes"). La base de datos raíz envía cambiios a los clientes para datos de artículos, tales como número de artículo, descripción y precio. Las base de datos cliente envían cambios a la raíz para datos de transacciones de venta, tales como la fecha de la venta y los artículos vendidos. La configuración de ejemplo especifica que la sincronización se lleve a cabo mediante el método pull para que los clientes reciban datos de la raíz, y por el método push para que la raíz reciba datos de los clientes.

Para comenzar, creamos bases de datos distintas para la raíz y para los clientes, en las cuales se crearán tablas de ejemplo. Utilizamos un fichero de configuración para ejecutar una instancia de SymmetricDS, llamada un nodo, en cada base de datos. Para enlazar los nodos entre sí, registramos el nodo cliente en el nodo raíz. Para este tutorial, la base de datos raíz es precargada con datos, mientras que la base de datos cliente permanece vacía. Para cargar los datos en la base de datos cliente, solicitamos al nodo raíz que envíe una "recarga" al nodo cliente. Una vez sincronizadas ambas bases de datos, realizamos cambios a los datos de las tablas y observamos cómo se sincronizan dichos cambios.

2.1. Instalar SymmetricDS

Instale  el software SymmetricDS y configúrelo con la información de conexión a su base de datos:

  1. Descarge el fichero symmetric-ds-1.6.X.zip del sitio http://www.symmetricds.org/

  2. Descomprima el fichero en cualquier directorio de su elección. Esto creará el subdirectorio symmetric-ds-1.6.X, que corresponde a la versión que usted ha descargado.

  3. Edite las propiedades de la base de datos en los ficheros properties para el nodo raíz y para los nodos cliente:

    • samples/root.properties

    • samples/client.properties

     

  4. Establezca las siguientes propiedades en ambos ficheros para indicar cómo conectarse a la base de datos:

    # El nombre de clase del Driver JDBC db.driver=com.mysql.jdbc.Driver
    # La URL JDBC utilizada para conectarse a la base de datos db.url=jdbc:mysql://localhost/sample
    # El usuario con el que conectarse (con permisos para crear y actualizar tablas) db.user=symmetric
    # La clave correspondiente a dicho usuario db.password=secret
  5. Establezca la siguiente propiedad en el fichero client.properties para especificar dónde puede ser contactado el nodo raíz:

    # The HTTP URL of the root node to contact for registration registration.url=http://localhost:8080/sync

    En este tutorial, la base de datos cliente comienza estando vacía, y el nodo no está registrado. El registro es el proceso mediante el cual el nodo recibe su configuración y la almacena en la base de datos. La configuración describe qué tablas de la base de datos deben sincronizarse y con qué nodos. Cuando un nodo no registrado comienza a ejecutarse, se registra con el nodo especificado en la URL de registro. El nodo de registro controla de modo centralizado otros nodos en la red, permitiendoles registrarse y entregandoles su configuración. En este tutorial, el nodo de registro es el nodo raíz, que también participa en la sincronización con otros nodos. 

2.2. Crear y cargar las Bases de Datos

Importante

En primer lugar, se deben crear las bases de datos para el nodo raíz y los nodos cliente, utilizando las herramientas de administración  proporcionadas por el proveedor del software de base de datos. Hay que asegurarse de que el nombre de las bases de datos que se creen coincida con el nombre utilizado en los ficheros properties de configuración de SymmetricDS.

En el apéndice Notas sobre Bases de Datos se puede comprobar la compatibilidad de una base de datos determinada con SymmetricDS.

Create the sample tables in the root node database, load the sample data, and load the sample configuration.

  1. Open a command prompt and navigate to the samples subdirectory of your SymmetricDS installation.

  2. Create the sample tables in the root database.

    ../bin/sym -p root.properties --run-ddl create_sample.xml

    Note that the warning messages from the command are safe to ignore.

  3. Create the SymmetricDS tables in the root node database. These tables will contain the configuration for synchronization. The following command uses the auto-creation feature to create all the necessary SymmetricDS system tables.

    ../bin/sym -p root.properties --auto-create

  4. Load the sample data and configuration into the root node database.

    ../bin/sym -p root.properties --run-sql insert_sample.sql

Create the sample tables in the client node database to prepare it for receiving data.

  1. Open a command prompt and navigate to the samples subdirectory of your SymmetricDS installation.

  2. Create the sample tables in the client database.

    ../bin/sym -p client.properties --run-ddl create_sample.xml

    Note that the warning messages from the command are safe to ignore.

Verify both databases by logging in and listing the tables.

  1. Find the item tables that sync from root to client: item and item_selling_price.

  2. Find the sales tables that sync from client to root: sale_transaction and sale_return_line_item.

  3. Find the SymmetricDS system tables, which have a prefix of "sym_".

2.3. Starting SymmetricDS

Start the SymmetricDS nodes and observe the logging output.

  1. Open a command prompt and navigate to the samples subdirectory of your SymmetricDS installation.

  2. Start the root node server.

    ../bin/sym -p root.properties --port 8080 --server

    The root node server starts up and creates all the triggers that were configured by the sample configuration. It listens on port 8080 for synchronization and registration requests.

  3. Start the client node server.

    ../bin/sym -p client.properties --port 9090 --server

    The client node server starts up and uses the auto-creation feature to create the SymmetricDS system tables. It begins polling the root node in order to register. Since registration is not yet open, the client node receives an authorization failure (HTTP response of 403).

Tip

If you want to change the port number used by SymmetricDS, you need to also set the my.url runtime property to match. The default value is:

my.url=http://localhost:8080/sync

2.4. Registering a Node

Open registration for the client node using the root node administration feature.

  1. Open a command prompt and navigate to the samples subdirectory of your SymmetricDS installation.

  2. Open registration for the client node server.

    ../bin/sym -p root.properties --open-registration "store,1"

    The registration is opened for a node group called "store" with an external identifier of "1". This information matches the settings in client.properties for the client node. Each node is assigned to a group and is given an external ID that makes sense for the application. In this tutorial, we have retail stores that run SymmetricDS, so we named our group "store" and we used numeric identifiers starting with "1".

  3. Watch the logging output of the client node to see it successfully register with the root node. The client is configured to attempt registration each minute. Once registered, the root and client are enabled for synchronization.

2.5. Sending Initial Load

Send an initial load of data to the client node using the root node administration feature.

  1. Open a command prompt and navigate to the samples subdirectory of your SymmetricDS installation.

  2. Send an initial load of data to the client node server.

    ../bin/sym -p root.properties --reload-node 1

    With this command, the root node queues up an initial load for the client node that will be sent the next time the client performs its pull. The initial load includes data for each table that is configured for synchronization.

  3. Watch the logging output of both nodes to see the data transfer. The client is configured to pull data from the root each minute.

2.6. Pulling Data

Modify data in the root database. The changes are propagated to the client database during pull synchronization.

  1. Open an interactive SQL session with the root database.

  2. Add a new item for sale:

    insert into item_selling_price (price_id, price) values (55, 0.65);

    insert into item (item_id, price_id, name) values (110000055, 55, 'Soft Drink');

    Once the statements are committed, the data change is captured and queued for the client node to pull.

  3. Watch the logging output of both nodes to see the data transfer. The client is configured to pull data from the root each minute.

  4. Verify that the new data arrives in the client database using another interactive SQL session.

2.7. Pushing Data

Modify data in the client database. The changes are propagated to the root database during push synchronization.

  1. Open an interactive SQL session with the client database.

  2. Add a new sale to the client database:

    insert into sale_transaction (tran_id, store, workstation, day, seq) values (1000, '1', '3', '2007-11-01', 100);

    insert into sale_return_line_item (tran_id, item_id, price, quantity) values (1000, 110000055, 0.65, 1);

    Once the statements are committed, the data change is captured and queued for the client node to push.

  3. Watch the logging output of both nodes to see the data transfer. The client is configured to push data to the root each minute.

2.8. Verifying Outgoing Batches

A batch is used for tracking and sending data changes to nodes. The sending node creates a batch and the receiving node acknowledges it. A batch in error is retried during synchronization attempts, but only after data changes in other channels are allowed to be sent. Channels are categories assigned to tables for the purpose of independent synchronization and control. Batches for a channel are not created when a batch in the channel is in error status.

  1. Open an interactive SQL session with either the root or client database.

  2. Verify that the data change was captured:

    select * from sym_data where table_name like 'item%' or table_name like 'sale%';

    Each row represents a row of data that was changed. The event_type is "I" for insert, "U" for update", or "D" for delete. For insert and update, the captured data values are listed in row_data. For update and delete, the primary key values are listed in pk_data.

  3. Verify that the data change was routed to a node, using the data_id from the previous step:

    select * from sym_data_event where data_id = ?;

    When the batched flag is set, the data change is assigned to a batch using a batch_id that is used to track and synchronize the data. Batches are created and assigned during a push or pull synchronization.

  4. Verify that the data change was batched, sent, and acknowledged, using the batch_id from the previous step:

    select * from sym_outgoing_batch where batch_id = ?;

    A batch represents a collection of changes to be sent to a node. The batch is created during a push or pull synchronization, when the status is set to "NE" for new. The receiving node acknowledges the batch with a status of "OK" for success or "ER" for error.

  5. Verify that the batch history was recorded, using the batch_id from the previous step:

    select * from sym_outgoing_batch_hist where batch_id = ?;

    Work performed on the batch is recorded in the history table. A new batch with status of "NE" records the number of data changes it contains in the data_event_count field. The status of "SE" shows that a batch is being sent to a node. The acknowledgement status from the receiving node is also recorded. If the status is error, the failed_data_id indicates which row in sym_data caused the error.

2.9. Verifying Incoming Batches

The receiving node keeps track of the batches it acknowledges and records statistics about loading the data. Duplicate batches are skipped by default, but this behavior can be changed with the incoming.batches.skip.duplicates runtime property.

  1. Open an interactive SQL session with either the root or client database.

  2. Verify that the batch was acknowledged, using a batch_id from the previous section:

    select * from sym_incoming_batch where batch_id = ?;

    A batch represents a collection of changes loaded by the node. The sending node that created the batch is recorded. The status is either "OK" for success or "ER" for error.

  3. Verify that the batch history was recorded, using the batch_id from the previous step:

    select * from sym_incoming_batch_hist where batch_id = ?;

    Work performed on the batch is recorded in the history table. If a duplicate batch was skipped, the status is recorded as "SK". Otherwise, the status is either "OK" for success or "ER" for error. The statement_count is the number of rows loaded and the byte_count is the size of the batch in bytes. The database_millis is the amount of time in milliseconds spent loading data in the database.