Symmetricds

#Installation

##download

download package from

wget https://nchc.dl.sourceforge.net/project/symmetricds/symmetricds/symmetricds-3.10/symmetric-server-3.10.3.zip
    
    
##Creating

###Configuration

Once SymmetricDS has been installed, we will need to populate the database with the configuration and sym tables. To do this, execute the following steps:

####Node Properties

create node properties file in enginnes folder. Each properties file in the engines directory represents a SymmetricDS node.

for pg.properties

```
#
# Licensed to JumpMind Inc under one or more contributor
# license agreements.  See the NOTICE file distributed
# with this work for additional information regarding
# copyright ownership.  JumpMind Inc licenses this file
# to you under the GNU General Public License, version 3.0 (GPLv3)
# (the "License"); you may not use this file except in compliance
# with the License.
#
# You should have received a copy of the GNU General Public License,
# version 3.0 (GPLv3) along with this library; if not, see
# <http://www.gnu.org/licenses/>.
#
# Unless required by applicable law or agreed to in writing,
# software distributed under the License is distributed on an
# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
# KIND, either express or implied.  See the License for the
# specific language governing permissions and limitations
# under the License.
#

# Friendly name to refer to this node from command line
engine.name=pg001

# The class name for the JDBC Driver
#db.driver=com.mysql.jdbc.Driver
#db.driver=oracle.jdbc.driver.OracleDriver
#db.driver=org.postgresql.Driver
#db.driver=org.apache.derby.jdbc.EmbeddedDriver
#db.driver=org.hsqldb.jdbcDriver
#db.driver=net.sourceforge.jtds.jdbc.Driver
#db.driver=com.ibm.db2.jcc.DB2Driver
#db.driver=com.informix.jdbc.IfxDriver
#db.driver=org.firebirdsql.jdbc.FBDriver
#db.driver=interbase.interclient.Driver
#db.driver=org.sqlite.JDBC
#db.driver=com.sybase.jdbc4.jdbc.SybDriver
#db.driver=com.nuodb.jdbc.Driver
db.driver=org.postgresql.Driver

# The JDBC URL used to connect to the database
#db.url=jdbc:mysql://localhost/corp?tinyInt1isBit=false
#db.url=jdbc:oracle:thin:@127.0.0.1:1521:corp
#db.url=jdbc:postgresql://localhost/corp?stringtype=unspecified
#db.url=jdbc:derby:corp;create=true
#db.url=jdbc:hsqldb:file:corp;shutdown=true
#db.url=jdbc:jtds:sqlserver://localhost:1433;useCursors=true;bufferMaxMemory=10240;lobBuffer=5242880
#db.url=jdbc:db2://localhost/corp
#db.url=jdbc:informix-sqli://localhost:9088/corp:INFORMIXSERVER=ol_ids_1150_1
#db.url=jdbc:firebirdsql:localhost:/var/lib/firebird/data/databasename
#db.url=jdbc:interbase://localhost//opt/interbase/data/corp.gdb
#db.url=jdbc:sqlite:corp.sqlite
#db.url=jdbc:sybase:Tds:localhost:5000/databasename
#db.url=jdbc:com.nuodb://localhost/database?schema=database
db.url=jdbc:postgresql://10.28.200.72:21769/pgdata

# The database user that SymmetricDS should use.
db.user=pgdata

# The database password
db.password=Mingjueinfo2015@Postgresql

# This node will contact the root node's sync.url to register itself.
# Leave blank to indicate this is the root node.
registration.url=

# Sync URL where other nodes can contact this node to push/pull data or register.
sync.url=http://localhost:31415/sync/pg001

# Node group this node belongs to, which defines what it will sync with who.
# Must match the sym_node_group configuration in database.
group.id=pg

# External ID for this node, which is any unique identifier you want to use.
external.id=000

# How often to run purge job,
job.purge.period.time.ms=7200000

# How to run routing (in millis), which puts changes into batches.
job.routing.period.time.ms=5000

# How often to run push (in millis), which sends changes to other nodes.
job.push.period.time.ms=10000

# How often to run pull (in millis), which receives changes from other nodes.
job.pull.period.time.ms=10000

# Automatically register new nodes when they request it.
# If this is false, accept the registration requests using "symadmin open-registration" command.
auto.registration=true

# When this node sends an initial load of data to another node, first send table create scripts.
initial.load.create.first=true
```

for mysql.properties

# Friendly name to refer to this node from command line
engine.name=mysql001

# The JDBC URL used to connect to the database
#db.url=jdbc:mysql://localhost/store001?tinyInt1isBit=false
#db.url=jdbc:oracle:thin:@127.0.0.1:1521:store001
#db.url=jdbc:postgresql://localhost/store001?stringtype=unspecified
#db.url=jdbc:derby:store001;create=true
#db.url=jdbc:hsqldb:file:store001;shutdown=true
#db.url=jdbc:jtds:sqlserver://localhost:1433/store001;useCursors=true;bufferMaxMemory=10240;lobBuffer=5242880
#db.url=jdbc:db2://localhost/store001
#db.url=jdbc:informix-sqli://localhost:9088/store001:INFORMIXSERVER=ol_ids_1150_1
#db.url=jdbc:firebirdsql:localhost:/var/lib/firebird/data/databasename
#db.url=jdbc:interbase://localhost//opt/interbase/data/store001.gdb
#db.url=jdbc:sqlite:store001.sqlite
#db.url=jdbc:sybase:Tds:localhost:5000/databasename
#db.url=jdbc:com.nuodb://localhost/database?schema=database
db.url=jdbc:mysql://ip:20769/new_carzone?tinyInt1isBit=false&useSSL=false

# The database user that SymmetricDS should use.
db.user=user

# The database password
db.password=password

# This node will contact the root node's sync.url to register itself.
registration.url=http://localhost:31415/sync/pg001

# Node group this node belongs to, which defines what it will sync with who.
# Must match the sym_node_group configuration in database.
group.id=mysql

# External ID for this node, which is any unique identifier you want to use.
external.id=001

# How to run routing (in millis), which puts changes into batches.
job.routing.period.time.ms=5000

# How often to run push (in millis), which sends changes to other nodes.
job.push.period.time.ms=10000

# How often to run pull (in millis), which receives changes from other nodes.
job.pull.period.time.ms=10000

```

####Init SymmetricDS tables

Create the SymmetricDS tables.

../bin/symadmin --engine pg001 create-sym-tables
    
####Sync Configuration

for config.sql

```
------------------------------------------------------------------------------
-- Clear and load SymmetricDS Configuration
------------------------------------------------------------------------------

delete from sym_trigger_router;
delete from sym_trigger;
delete from sym_router;
delete from sym_channel where channel_id in ('pg2mysql');
delete from sym_node_group_link;
delete from sym_node_group;
delete from sym_node_host;
delete from sym_node_identity;
delete from sym_node_security;
delete from sym_node;

------------------------------------------------------------------------------
-- Channels
------------------------------------------------------------------------------

insert into sym_channel 
(channel_id, processing_order, max_batch_size, enabled, description)
values('pg2mysql', 1, 100000, 1, 'all table from pg to mysql');

------------------------------------------------------------------------------
-- Node Groups
------------------------------------------------------------------------------

insert into sym_node_group (node_group_id) values ('pg');
insert into sym_node_group (node_group_id) values ('mysql');

------------------------------------------------------------------------------
-- Node Group Links
------------------------------------------------------------------------------

insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action) values ('pg', 'mysql', 'W');
insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action) values ('mysql', 'pg', 'P');

------------------------------------------------------------------------------
-- Triggers
------------------------------------------------------------------------------

-- Triggers for tables on "item" channel
insert into sym_trigger 
(trigger_id,source_table_name,channel_id,last_update_time,create_time)
values('table01','table01','pg2mysql',current_timestamp,current_timestamp);

------------------------------------------------------------------------------
-- Routers
------------------------------------------------------------------------------

insert into sym_router 
(router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time)
values('pg2mysql', 'pg', 'mysql', 'default',current_timestamp, current_timestamp);

------------------------------------------------------------------------------
-- Trigger Routers
------------------------------------------------------------------------------

insert into sym_trigger_router 
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('table01','pg2mysql', 100, current_timestamp, current_timestamp);

```

Load the SymmetricDS configuration into the root node database.

../bin/dbimport --engine pg001 config.sql

##Start

tart the SymmetricDS instance. All three nodes run in the same instance. From the command prompt, navigate to the SymmetricDS home directory and run the following command.

bin/sym

#Operation

##Initial Load

An initial load is the process of seeding tables at a target node with data from a source node. Instead of capturing data, data is selected from the source table using a SQL statement and then it is streamed to the client.

The store nodes were pre-configured to do an initial load after registration. However, it is worth noting that Initial Loads can be sent using the following commands:

bin/symadmin --engine corp-000 reload-node 001
    
or reload special table

./symadmin  --engine pg001 reload-table -n 001 table01
    
delete before reload

./dbsql --engine=pg001
    
    insert into SYM_TABLE_RELOAD_REQUEST (target_node_id, source_node_id, trigger_id, router_id, create_time, delete_first, last_update_time)
     values ('000', '001', 'ic_attr_name_category_ref', 'mysql2pg', current_timestamp, 1, current_timestamp);

Load data and create target tables

insert into SYM_TABLE_RELOAD_REQUEST (target_node_id, source_node_id, trigger_id, router_id, create_time, create_table, last_update_time)
     values ('corp-000', 'store-001', 'ALL', 'ALL', current_timestamp, 1, current_timestamp);
     
Load data for a specific table with partial data

insert into SYM_TABLE_RELOAD_REQUEST (target_node_id, source_node_id, trigger_id, router_id, create_time, reload_select, last_update_time)
     values ('store-001', 'corp-000', 'item_selling_price', 'corp_2_store', current_timestamp, 'store_id=$(externalId)', current_timestamp);

##Sync Triggers

Run the sync triggers process to create database triggers that are missing or
re-create database triggers that have a configuration change.  If a filename is
specified, the SQL statements are also written to file.  If triggers should not
be applied automatically then set the auto.sync.triggers property to false.

Usage: symadmin sync-triggers [<tablename> ...]
    
##Uninstall

Uninstall all SymmetricDS objects from the database, including the SYM tables,
sequences, functions, stored procedures, and triggers.

Usage: symadmin uninstall

Navigation

Recent Posts

Friend Links