Windows 环境下SymmetricDS配置详情

╰半橙微兮° 2024-04-17 16:30 78阅读 0赞

> 在写我自己数据库同步时，github上也找到了很多数据库同步，但是非常冗杂。

SymmetricDS安装、配置、同步如下：  
symmetricDS 有官方操作手册不过是英文guide，这里是一份中文翻译版，([网址][Link 1])

主要步骤就是：配置-开启-注册-同步。本次配置两个节点可以双向同步且这两个节点均属于mysql。两个节点的代码是一样的，只是配置文件不同。

*  symmetricDS官方包中有配置模板 在sample下，有properties文件和系统表sql。

![配置文件截图][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhYnliYWJ5dXA_size_16_color_FFFFFF_t_70]

准备工作：  
[下载][Link 2]  
本地建两个数据库，corptest，storetest，并在两个数据库建相同的表。两个节点都在本地。

CREATE TABLE student (stuid INT PRIMARY KEY,name VARCHAR(12),age INT);

![建库][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhYnliYWJ5dXA_size_16_color_FFFFFF_t_70 1]  
建立节点文件存放目录，`F:\sym` 目录下：  
![建立目录][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhYnliYWJ5dXA_size_16_color_FFFFFF_t_70 2]  
并将下载的文件解压移到两个目录中：  
![corp][]  
![store][]

*  配置两个节点，主节点也可以是其他节点的从节点，在配置文件指出即可；

sample目录中已经附带配置properties了，复制到两个目录下的engine中，并改相应engine的名字（并非必须）原文档为：`The file is usually named according to the engine.name, but it is not a requirement.`  
`corp-000.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=corp-000
    
    # 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.h2.Driver
    
    # The JDBC URL used to connect to the database
    db.url=jdbc:mysql://localhost:3306/corptest??tinyInt1isBit=false&characterEncoding=utf8&useSSL=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:h2:corp;AUTO_SERVER=TRUE;LOCK_TIMEOUT=60000
    
    # The database user that SymmetricDS should use.
    db.user=root
    
    # The database password
    db.password=Hlm970%&
    
    # 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:8050/sync/corp-000
    
    # 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=corp
    
    # 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

`store-001.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=store-001
    
    # 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.h2.Driver
    
    # The JDBC URL used to connect to the database
    db.url=jdbc:mysql://localhost:3306/storetest?tinyInt1isBit=false&characterEncoding=utf8&useSSL=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:h2:store001;AUTO_SERVER=TRUE;LOCK_TIMEOUT=60000
    
    # The database user that SymmetricDS should use.
    db.user=root
    
    # The database password
    db.password=Hlm970%&
    
    # This node will contact the root node's sync.url to register itself.
    registration.url=http://localhost:8050/sync/corp-000
    
    # 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=store
    
    # 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

配置项内容都有注释

*  建立业务表(个人认为没必要建，但实际试了一下，不建的话会报注册不成功)  
    cmd在`F:\sym\sym-corp\bin`中运行`dbimport --engine corp-000 --format XML F:\sym\sym-corp\samples\create_sample.xml`,  
    在`F:\sym\sym-store001\bin` 运行`dbimport --engine store-001 --format XML F:\sym\sym-store001\samples\create_sample.xml`  
    生成  
    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhYnliYWJ5dXA_size_16_color_FFFFFF_t_70 3][]
 *  建立系统表  
    cmd在`F:\sym\sym-corp\bin`中运行`symadmin --engine corp-000 create-sym-tables` 不要把engine名称写错，完成后出现系统表。  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhYnliYWJ5dXA_size_16_color_FFFFFF_t_70 4]  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhYnliYWJ5dXA_size_16_color_FFFFFF_t_70 5]
 *  插入数据  
    `dbimport --engine corp-000 F:\sym\sym-corp\samples\insert_sample.sql`
 *  启动两个节点

corp：`sym --engine corp-000 --port 8050` 端口号是在配置文件写明的注册端口号。  
store：重新开启一个cmd，`sym --engine store-001 --port 8010`。

*  注册节点

重新开启cmd,定为到corp的bin目录下，为group为store的001号开启注册  
`symadmin --engine corp-000 open-registration store 001`

运行成功：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhYnliYWJ5dXA_size_16_color_FFFFFF_t_70 6]

*  测试同步  
    两个数据库已经可以双向同步了

[Link 1]: https://download.csdn.net/download/babybabyup/11635651
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhYnliYWJ5dXA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20190829111516324.PNG?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhYnliYWJ5dXA=,size_16,color_FFFFFF,t_70
[Link 2]: http://www.symmetricds.org/download
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhYnliYWJ5dXA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20190829151712858.PNG?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhYnliYWJ5dXA=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhYnliYWJ5dXA_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20190829152055794.PNG?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhYnliYWJ5dXA=,size_16,color_FFFFFF,t_70
[corp]: https://img-blog.csdnimg.cn/20190829152931773.PNG?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhYnliYWJ5dXA=,size_16,color_FFFFFF,t_70
[store]: https://img-blog.csdnimg.cn/20190829152950774.PNG?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhYnliYWJ5dXA=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhYnliYWJ5dXA_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20190830164546675.PNG?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhYnliYWJ5dXA=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhYnliYWJ5dXA_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20190829160451268.PNG?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhYnliYWJ5dXA=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhYnliYWJ5dXA_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20190829160605216.PNG?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhYnliYWJ5dXA=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhYnliYWJ5dXA_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20190830165304172.PNG?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhYnliYWJ5dXA=,size_16,color_FFFFFF,t_70