2011年8月9日 星期二

[記事] MS SQL Server and MySQL replication - SymmetricDS

標籤：
屬於阿宅世界的技術文章，想看的再點開，切莫自誤 !

如果是同樣的資料庫，那在同步、備份資料通常都有現在的Replication機制
但如果碰到異質資料庫就會有點麻煩。

Google了一下找到了個 SymmetricDS，可以自動同步數種數個不同的資料庫，看起來好像不錯，先裝來玩玩看，效能的部份等有資料進來後再來測試。

架構

MS SQL Server (192.168.1.1) : 當作 client DB ，收集資料的資料放到 MS_TEST 這個 database的people資料表裡
MySQL Server (192.168.1.2) : 當作 host DB，client DB 收集到的資料會同步到 MySQL_TEST這個database的people資料表裡
SymmetricDS Server (192.168.1.2) : SymmetricDS程式運行的環境，可以跟以上兩台DB分開，跟其中一台放一起也可以。

安裝

抓取Source並解開
shell> cd /home
shell> wget http://downloads.sourceforge.net/project/symmetricds/symmetricds/symmetricds-2.3/symmetric-ds-2.3.1-server.zip?r=http%3A%2F%2Fsymmetricds.codehaus.org%2F&ts=1312868640&use_mirror=ncu
shell> unzip symmetric-ds-2.3.1-server.zip
在 SymmetricDS 裡，一台DB要對應一個應用程式執行同步的動作，需為每個應用程式寫好啟動的設定檔，而在最簡單的架構下，至少會有兩台DB，一台 client DB負責收集資料，另一台 host DB負責匯整資料。這邊測試的client DB 是 MS SQL Server ，host DB 是 MySQL，分別編寫設定檔如下
shell> cd /home/SymmetricDS/samples
shell> vi client.properties 
# 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://192.168.1.1:1433/MS_TEST
# The user to login as who can create and update tables
db.user=mssql_user_your_create


# The password for the user to login as
db.password=the_pwd_you_set


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


# Do not change these for running the demo
group.id=client
external.id=1
job.routing.period.time.ms=2000


# This is how often the push job will be run.
job.push.period.time.ms=5000
# This is how often the pull job will be run.
job.pull.period.time.ms=5000 
shell> vi host.properties
# The class name for the JDBC Driver
db.driver=com.mysql.jdbc.Driver
# The JDBC URL used to connect to the database
db.url=jdbc:mysql://192.168.1.2/MySQL_TEST
# The user to login as who can create and update tables
db.user=mysql_user_you_create
# The password for the user to login as
db.password=the_pwd_you_set
registration.url=http://localhost:8080/sync
sync.url=http://localhost:8080/sync
# Do not change these for running the demo
group.id=host
external.id=00000
# Don't muddy the waters with purge logging
job.purge.period.time.ms=7200000
接下來在兩邊的資料庫建立要同步的資料表，可以手動在DB裡建，也可以叫 SymmetricDS 連到DB幫你建，為了方便重覆測試，就依照 tutorial 裡的方式來吧

shell> cd /home/SymmetricDS/samples
shell> vi create_table.xml 
<?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database.dtd">
<database name="MS_TEST">
    <table name="people">
        <column name="p_id" type="INTEGER" required="true" primaryKey="true" />
        <column name="name" type="VARCHAR" size="100" />
        <column name="speed" type="FLOAT" required="true" />
    </table>
</database> 
Note : 上面database 的name屬性寫的是MS_TEST，表示是給client的MS SQL Server 用的，如果是MySQL，要改成 MySQL_TEST
另外 SymmetricDS 在執行時，還需要一些系統設定值 (存放在 sym_ 開頭的資料表中)，同樣可以手動建，但還是寫成 file 累一次就好

shell> cd /home/SymmetricDS/samples
shell> vi insert_data.sql
------------------------------------------------------------------------------
-- Sample Data
------------------------------------------------------------------------------
insert into people (p_id, name, age) values (11000001, 'Yummy Gum', 11.0);
------------------------------------------------------------------------------
-- Sample Symmetric Configuration
------------------------------------------------------------------------------
--
-- Nodes. node_group_id 的值要對應 client.properties、host.properties 裡的group.id--
delete from sym_node_group_link;
delete from sym_node_group;
delete from sym_node_identity;
delete from sym_node_security;
delete from sym_node;
insert into sym_node_group (node_group_id, description)
values ('host', 'Central Database');
insert into sym_node_group (node_group_id, description)
values ('client', 'Client Database')
insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action)
values ('client', 'host', 'P');
insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action)
values ('host', 'client', 'W');
-- 事先塞入 host node 的資料，client 的資料會在registration 後建立
insert into sym_node (node_id, node_group_id, external_id, sync_enabled)
values ('00000', 'host', '00000', 1);
insert into sym_node_identity values ('00000');
--
-- Channels
--
insert into sym_channel
(channel_id, processing_order, max_batch_size, enabled, description)
values('ch_people', 1, 100000, 1, 'client people data');
--
-- Triggers. 其中 source_table_name 就是要同步的資料表的名稱--
insert into sym_trigger
(trigger_id,source_table_name,channel_id,last_update_time,create_time)
values('trigger_people','people','ch_people',current_timestamp,current_timestamp);
--
-- Routers. 建立資料流
--
insert into sym_router
(router_id,source_node_group_id,target_node_group_id,create_time,last_update_time)
values('host_to_client_identity', 'host', 'client', current_timestamp, current_timestamp);
insert into sym_router
(router_id,source_node_group_id,target_node_group_id,create_time,last_update_time)
values('client_to_host_identity', 'client', 'host', current_timestamp, current_timestamp);
--
-- Trigger Router Links. 設定觸發時的資料流向
--
insert into sym_trigger_router
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('trigger_people','host_to_client_identity', 200, current_timestamp, current_timestamp);
初始化

先設定 host DB (建立 people 這個資料表)
shell> cd
shell> ../bin/sym -p host.properties --run-ddl create_table.xml
產生 SymmetricDS 運作時需要的系統資料表
shell> cd /home/symmetric-ds-2.3.1/samples/
shell> ../bin/sym -p host.properties --auto-create
如果沒有錯誤訊息，登入MySQL 應該會看到以下的資料表
shell> mysql -p
mysql> show tables;
+-------------------------------+
| Tables_in_test                |
+-------------------------------+
| people                        |
| sym_channel                   |
| sym_data                      |
| sym_data_event                |
| sym_data_gap                  |
| sym_data_ref                  |
| sym_incoming_batch            |
| sym_lock                      |
| sym_node                      |
| sym_node_channel_ctl          |
| sym_node_group                |
| sym_node_group_channel_window |
| sym_node_group_link           |
| sym_node_host                 |
| sym_node_host_channel_stats   |
| sym_node_host_job_stats       |
| sym_node_host_stats           |
| sym_node_identity             |
| sym_node_security             |
| sym_outgoing_batch            |
| sym_parameter                 |
| sym_registration_redirect     |
| sym_registration_request      |
| sym_router                    |
| sym_trigger                   |
| sym_trigger_hist              |
| sym_trigger_router            |
+-------------------------------+
28 rows in set (0.00 sec)

系統資料表建好之後就可以把系統設定值塞入
shell> ../bin/sym -p host.properties --run-sql insert_data.sql
最後再在 client DB 建立people 資料表
shell> ../bin/sym -p client.properties --run-ddl create_table.xml
Note : 如果兩個client 跟 host DB 使用不同的database name ，但又用同一個create_table.xml，記得改掉 name 的屬性
啟動

先啓動 host DB，指定它聽在 port 8080 上
shell>  ../bin/sym -p host.properties --port 8080 --server &
查看 log 應該會有以下訊息，比較值得注意的是建立trigger 的部份，這事關你的資料能不能正確的同步以及你在 host DB 中修改設定後會不會同步到 client DB去，在這個例子裡，會對以下幾個資料表在 Insert/Update/Delete 時建立對應的 trigger
 people, sym_channel,  sym_node_group, sym_parameter, sym_router, sym_trigger, sym_node_group_link, sym_node_group_channel_window, sym_trigger_router
啟動 client DB，指定它聽在 port 9090
shell> ../bin/sym -p client.properties --port 9090 --server
因為此時 client DB 還未向 host DB 註冊，所以會一直跳錯誤訊息，執行動作向 host DB 註冊，最後的的client 是group_id, 1 是 node_id
shell> ../bin/sym -p host.properties --open-registration "client,1";
如果正確註冊的話，應該會在訊息中看到 替 sym_node_host 建立 trigger 的訊息

最後做個初始化讀取資料的動作
shell> ../bin/sym -p host.properties --reload-node 1
那兩個資料庫的資料應該就同步了。接下來在 host DB(MySQL) 塞入一筆資料
mysql > insert into people values (200000, 'Num 2', 22.0);
然後到 client DB ( MS SQL Server) 查詢 people 資料表，應該會看到資料同步過來，接著在MS SQL Server 塞入一筆資料
insert into people values (30000, 'Num 3', 33.0);
再回到 MySQL 查詢，發現資料並未同步 ? 咦 ? 這樣好像跟我們將資料由 client 同步到 host 的需求相反，原因出在我們當初在設定資料觸發時的資料流向指定的是 host_to_client_identity，所以只有在 host 資料有變動時才會同步到 client ，明白這點之後，要改成符合我們需求的作法也很簡單，在 host db 中下達以下變動
mysql> update sym_trigger_router set router_id = 'clint_to_host_identity' where trigger_id = 'trigger_people';
然後 client 的應用程式會出現為 client 的people資料表建立 trigger 的訊息，此時再在client 下達變動的動作，再回 host 查詢，應該就會看到 client 的資料同步到 host 了。

Note:

  • Driver的部份不用擔心，SymmetricDS都已經包好好的放在 /home/symmetric-ds-2.3.1/lib 裡

Ref :


於 8/09/2011

0 意見:

張貼留言

訂閱： 張貼留言 (Atom)