symmetricDS 셋팅

예전 프로젝트에서 실시간 DB 마이그레이션을 위한 트리거방식의 툴을 찾던중 셋팅을 하다 실패하였지만 혹시 몰라서 기록해 두려고 한다.

 

symmetricDS 셋팅

1. engines\server.properties 셋팅

#Updated by SymmetricDS Pro
#Tue Mar 19 10:06:35 KST 2019
external.id=server
engine.name=server
auto.config.registration.svr.sql.script=/symmetric-profile-standard-2-tier-config.sql
sync.url=http\://DESKTOP-V5H1B3L\:31415/sync/server
group.id=server
db.url=jdbc\:postgresql\://localhost/VCDB?protocolVersion\=3&stringtype\=unspecified&socketTimeout\=300&tcpKeepAlive\=true
db.driver=org.postgresql.Driver
db.user=vc
registration.url=
db.validation.query=select 1
db.init.sql=
db.password=root
cluster.lock.enabled=false
db.connection.properties=

2. engines\nclient-redlime.properties

#Updated by SymmetricDS Pro
#Tue Mar 19 10:07:44 KST 2019
external.id=redlime
engine.name=client-redlime
sync.url=http\://DESKTOP-V5H1B3L\:31415/sync/client-redlime
group.id=client
db.url=jdbc\:mysql\://localhost/vcdb?tinyInt1isBit\=false&zeroDateTimeBehavior\=convertToNull&useSSL\=false
db.driver=com.mysql.jdbc.Driver
db.user=root
registration.url=http\://DESKTOP-V5H1B3L\:31415/sync/server
db.validation.query=select 1
db.init.sql=
db.password=root
cluster.lock.enabled=false
db.connection.properties=

3. engines\server_insert.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 ('server', 'client');
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('server', 1, 100000, 1, 'server data');

insert into sym_channel 
(channel_id, processing_order, max_batch_size, enabled, description)
values('client', 1, 100000, 1, 'client data');

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

insert into sym_node_group (node_group_id) values ('server');
insert into sym_node_group (node_group_id) values ('client');

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

insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action) values ('server', 'client', 'W');

insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action) values ('client', 'server', 'P');

------------------------------------------------------------------------------
-- Triggers
-- 맵핑 시킬 모든 테이블을 이곳에 지정 해야함
------------------------------------------------------------------------------

insert into sym_trigger 
(trigger_id,source_schema_name,source_table_name,channel_id,last_update_time,create_time)
values('vc.cis_kv_keyvalue','vc','cis_kv_keyvalue','default',current_timestamp,current_timestamp);

insert into sym_trigger 
(trigger_id,source_schema_name,source_table_name,channel_id,last_update_time,create_time)
values('vc.cis_kv_providers','vc','cis_kv_providers','default',current_timestamp,current_timestamp);

insert into sym_trigger 
(trigger_id,source_schema_name,source_table_name,channel_id,last_update_time,create_time)
values('vc.cl_config','vc','cl_config','default',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('server waits for pull from client', 'server', 'client', 'default',current_timestamp, current_timestamp);

insert into sym_router 
(router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time)
values('client pushes to server', 'client', 'server', '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('vc.cl_config','server waits for pull from client', 50, current_timestamp, current_timestamp);

insert into sym_trigger_router 
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('vc.cl_config','client pushes to server', 50, current_timestamp, current_timestamp);

insert into sym_trigger_router 
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('vc.cis_kv_providers','server waits for pull from client', 50, current_timestamp, current_timestamp);

insert into sym_trigger_router 
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('vc.cis_kv_providers','client pushes to server', 50, current_timestamp, current_timestamp);

insert into sym_trigger_router 
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('vc.cis_kv_keyvalue','server waits for pull from client', 50, current_timestamp, current_timestamp);

insert into sym_trigger_router 
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('vc.cis_kv_keyvalue','client pushes to server', 50, current_timestamp, current_timestamp);

4. engines\client_insert.sql



5. Command 실행(Server)
symmetric-server-3.10.0\bin 경로에서 실행

dbimport --engine server
symadmin --engine server create-sym-tables
dbimport --engine server $symmetric-server 경로\engines\server_insert.sql
engines\server.properties 파일에서 지정한 포트 오픈 (31415)
sync.url=http\://DESKTOP-V5H1B3L\:31415/sync/server
sym --engine server --port 31415

6. Command 실행(Client)
symmetric-server-3.10.0\bin 경로에서 실행
dbimport --engine client-redlime
sym --engine client-redlime --port 9090

6. Command 실행(New)
symmetric-server-3.10.0\bin 경로에서 실행
symadmin --engine server open-registration redlime clinet