SymmetricDS是從大型POS開發專案衍生出來的開源資料庫同步軟體,具有異質資料庫同步、資料送達保證、集中設定、即時同步、高擴展能力..等特色,可以針對File與Database進行雙向同步,架構上採主從式架構。SymmetricDS已經廣泛應用在低頻寬、品質不穩、斷線頻頻的網路環境。SymmetricDS目前提供免費版與收費PRO版(3xxxUSD),兩種版本功能實現上相同,但PRO版另外提供Web管理介面,可以設置與監管各端點。
運作原理
- SymmetricDS是一個可以獨立運作的服務,不見得要跟資料庫裝在同套環境內,但因SymmetricDS體積小負載低,通常會裝在同一環境內,會取得最佳的效能。
- SymmetricDS透過JDBC跟資料庫連線,資料會先壓縮過再透過HTTP/HTTPS跟其他Node傳輸同步。
- 每個SymmetricDS Node都會指定群組名稱(Group)及對外ID(External ID),Group+External ID=Node辨識唯一值。
- 節點間的資料同步方式有分三種
- Push(P):讓“來源節點群組(Source Node Group)“提出HTTP PUT要求且把資料推送到”目標節點群組(Target Node Group)“。
- Wait for pull(W):”目標節點群組”會主動透過HTTP GET來跟“來源節點群組”拉資料,在這之前“來源節點群組”會一直等待HTTP GET的要求。
- Route-only(R):表示資料不會由SymmetricDS來傳送。
- SymmetricDS Server在首次使用時會在Database中建立供資料同步相關Table(共41個SYM開頭的Table),而Client在首次連線時,會同步建立Server所有SYM Table與Data。
系統設定階段
- 不論是Server還是Client都會配置SYM_Table,主要用來進行監聽與執行動作。並且是以Node來代表服務器進行設置。
- Trigger是為了實現系統的即時性,系統持續在監聽資料庫,當發生變化時,會同時向關連的Node發起Sync Request,關連的Node接收Request後做出對應的動作。
系統執行階段
系統實做
- Database:Master採用SQLServer,Client 1與Client 2是SQLServer,Client 3為Oracle。其中Master、Client 1與Client2均屬同一套SQLServer Express,但不同Database。
- O/S:均是Windows。
- Hardware:兩台PC。
程式安裝
- 安裝方式很簡單,只要將壓縮檔解開後放到任意處即可。
- 在這次測試中,因為Master、Client1跟2都在同一台電腦上,但因為一個SymmetricDS服務對應一個Database,所以要分別安裝三份SymmetricDS以對應不同的DB。
系統設置
- 在[SymmetricDS Home]\samples目錄下有預先準備好的設置範例文件。複製corp-000.properties到[SymmetricDS Home]\engines,並改名為[engine_name].properties。
- 修改.properties檔,其內容有:
- engine.name:節點的名稱
- db.driver:JDBC Driver名稱,安裝壓縮檔裡都已經有包含各資料庫的JDBC Driver。
- db.url:JDBC Connection URL
- db.user:資料庫用戶,此用戶必須要有建立、新增、修改、刪除權限,在這邊是偷懶用sa當用戶。
- db.password:資料庫用戶密碼。
- registration.url:上一級節點的註冊路徑,因為本節點為Master Node,所以此處沒有設定。
- sync.url:本機的註冊路徑,當節點為最末端節點時,此處可以不用設定。
- group.id:節點群組ID。
- external.id:節點群組下的編號,group.id+external.id等於辨識節點的唯一值。
- 其他參數為三種同步方式預設的Period Time,沒有特別需求可以不用更動。
Master Node配置
engine.name=sunserver-000 # The class name for the JDBC Driver db.driver=net.sourceforge.jtds.jdbc.Driver # The JDBC URL used to connect to the database db.url=jdbc:jtds:sqlserver://10.40.9.20:1433/sun;instance=SQLEXPRESS # The user to login as who can create and update tables db.user=sa # The password for the user to login as db.password=1qaz2wsx registration.url= sync.url=http://10.40.9.20:8080/sync/sunserver-000 # Do not change these for running the demo group.id=sunserver external.id=000 # Don't muddy the waters with purge logging job.purge.period.time.ms=7200000 # This is how often the routing job will be run in milliseconds job.routing.period.time.ms=5000 # This is how often the push job will be run. job.push.period.time.ms=10000 # This is how often the pull job will be run. job.pull.period.time.ms=10000 # Kick off initial load initial.load.create.first=true
Client 1 Node配置
engine.name=sunclient-001 # The class name for the JDBC Driver db.driver=net.sourceforge.jtds.jdbc.Driver # The JDBC URL used to connect to the database db.url=jdbc:jtds:sqlserver://10.40.9.20:1433/sun1;instance=SQLEXPRESS # The user to login as who can create and update tables db.user=sa # The password for the user to login as db.password=1qaz2wsx # The HTTP URL of the root node to contact for registration registration.url=http://10.40.9.20:8080/sync/sunserver-000 sync.url=http://10.40.9.20:7070/sync/sunclient-001 # Do not change these for running the demo group.id=sunclient external.id=001 # This is how often the routing job will be run in milliseconds job.routing.period.time.ms=5000 # This is how often the push job will be run. job.push.period.time.ms=10000 # This is how often the pull job will be run. job.pull.period.time.ms=10000
Client 2 Node配置
engine.name=sunclient-002 # The class name for the JDBC Driver db.driver=net.sourceforge.jtds.jdbc.Driver # The JDBC URL used to connect to the database db.url=jdbc:jtds:sqlserver://10.40.9.20:1433/sun2;instance=SQLEXPRESS # The user to login as who can create and update tables db.user=sa # The password for the user to login as db.password=1qaz2wsx # The HTTP URL of the root node to contact for registration registration.url=http://10.40.9.20:8080/sync/sunserver-000 sync.url=http://10.40.9.20:9090/sync/sunclient-002 # Do not change these for running the demo group.id=sunclient external.id=002 # This is how often the routing job will be run in milliseconds job.routing.period.time.ms=5000 # This is how often the push job will be run. job.push.period.time.ms=10000 # This is how often the pull job will be run. job.pull.period.time.ms=10000
因為Master、Client 1和Client 2在同一台電腦上,所以在設定sync.url時需要用
不同的port區分開來,以避免衝突。
不同的port區分開來,以避免衝突。
Client 3 Node配置
engine.name=sunclient-003 # The class name for the JDBC Driver db.driver=oracle.jdbc.driver.OracleDriver # The JDBC URL used to connect to the database db.url=jdbc:oracle:thin:@10.40.9.2:1521:XE # The user to login as who can create and update tables db.user=apps # The password for the user to login as db.password=apps registration.url=http://10.40.9.20:8080/sync/sunserver-000 sync.url=http://10.40.9.2:8888/sync/sunclient-003 # Do not change these for running the demo group.id=sunclient external.id=003 # Don't muddy the waters with purge logging job.purge.period.time.ms=7200000 # This is how often the routing job will be run in milliseconds job.routing.period.time.ms=5000 # This is how often the push job will be run. job.push.period.time.ms=10000 # This is how often the pull job will be run. job.pull.period.time.ms=10000 # Kick off initial load initial.load.create.first=true
Master Node初始化
Step 1.對Database進行初始化
- 使用Command Mode,進入[SymmectricDS Home]\engines,執行
..\bin\symadmin --engine sunserver-000 create-sym-tables
- 執行後,會自動在資料庫中建立許多sym開頭的Table跟index。
Step 2.寫入Node Group
insert into sym_node_group (node_group_id, description) values ('sunserver', '主要資料中心'); insert into sym_node_group (node_group_id, description) values ('sunclient', '次要資料中心');
Step 3.設定Node Group之間的資料同步方式
- 由data_event_action來指定同步方式
- P代表Push,W代表Wait for pull
insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action) values ('sunclient', 'sunserver', 'P'); insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action) values ('sunserver', 'sunclient', 'W');
Step 4.設定Node
- sym_node_security是用來紀錄節點通訊前的驗證密碼,同時registration_time與initial_load_time的設定是用來讓系統知道這個節點已經註冊並初始化,不用在系統啟動時再做一次。
- 僅需要註冊Master Node資訊,其他的Client Node會在註冊節點後自動寫入節點資訊。
insert into sym_node (node_id, node_group_id, external_id, sync_enabled) values ('000', 'sunserver', '000', 1); insert into sym_node_security (node_id,node_password,registration_enabled,registration_time,initial_load_enabled,initial_load_time,initial_load_id,initial_load_create_by,rev_initial_load_enabled,rev_initial_load_time,rev_initial_load_id,rev_initial_load_create_by,created_at_node_id) values ('000','123456',0,current_timestamp,0,current_timestamp,null,null,0,null,null,null,'000'); insert into sym_node_identity values ('000');
Step 5.設定通道
insert into sym_channel (channel_id, processing_order, max_batch_size, enabled, description) values('bus_info', 1, 100000, 1, '資料同步通道');
Step 6.定義Trigger
- 定義要同步的Table Name。
- 當有設定Foreign Key的Table需要設定在同一個Channel。
- Sym_trigger中的excluded_column_names column可以排除不想同步的欄位。
insert into sym_trigger (trigger_id,source_table_name,channel_id,last_update_time,create_time) values('symmetric_test_area','symmetric_test_area','bus_info',current_timestamp,current_timestamp); insert into sym_trigger (trigger_id,source_table_name,channel_id,last_update_time,create_time) values('symmetric_test_bus','symmetric_test_bus','bus_info',current_timestamp,current_timestamp);
Step 7.定義Router
- 設定資料同步的走向,即是資料從哪個節點流向那個節點。
- 當Router_Type='Column'時表示可以從來源Table的特定欄位值來決定資料的流向,可以在Router_Expression欄位中指定表達式[Example:ORG_CODE=:EXTERNAL_ID]
insert into sym_router (router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time) values('client_2_server', 'sunclient', 'sunserver', 'default',current_timestamp, current_timestamp);
Step 8.建立Trigger與Router的關連
- Trigger只有被Router關連後,SymmetricDS才會為這個Table自動建立對應的觸發器。
insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values('symmetric_test_area','client_2_server', 200, current_timestamp, current_timestamp); insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values('symmetric_test_bus','client_2_server', 200, current_timestamp, current_timestamp);
啟動SymmetricDS
Step 1.註冊Client Node
- 只有第一次使用才需要註冊
- 使用Command Mode進入[Master Node SymmetricDS Home]/engines
- 執行註冊命令
..\bin\symadmin --engine sunserver-000 open-registration sunclient 001 ..\bin\symadmin --engine sunserver-000 open-registration sunclient 002 ..\bin\symadmin --engine sunserver-000 open-registration sunclient 003
Step 2.啟動服務
- 啟動Client Node
- 使用Command Mode進入[Client Node SymmetricDS Home]/engines
- 輸入 sym –port 7070
- 啟動Master Node
- 使用Command Mode進入[Master Node SymmetricDS Home]/engines
- 輸入 sym –port 8080
這裡指定的Port需跟properties裡sync.url設定的Port一樣
沒有留言:
張貼留言