Sql-server – SymmetricDS Setup – SQL Server 2008 to MySQL

MySQLreplicationsql serversymmetric-ds

I need to replicate just a couple of tables from a Microsoft SQL Server 2008 database to a MySQL 5.7 database (one-way only). I've been struggling to find a simple solution.

I've ultimately decided to try SymmetricDS, but the setup is confusing: Assuming I've got SQL Server running on Computer1, and MySQL on Computer2, and I just need Table1 and Table2, how am I supposed to set this up?

As I understand already, I need to add four additional tables to the Master Node (SQL Server in my case), do I add these tables directly to the database I need to copy? This may prove problematic as this database is managed by a very temperamental third-party application and we really don't want to touch it, should we replicate the specific data we need into a local copy on the SQL Server and then "sym" that entire db over to MySQL?

I got SSIS talking my MySQL instance, if it's possible to set that up with a trigger of some kind to automatically run the procedure that would probably work as an alternative.

However, to add, I'm pretty sure I understand the process of creating the .properties files, what I don't fully follow is the Configuration settings, or more specifically, how to set these up properly so the connection is only one-way (the SQL Server should be essentially read-only).

Best Answer

It seem that the official documents are not clear enough to help you. The steps are below:

  • You need 1( or 2) intermediate node(s)(Linux or Windows) which have connection between Source and Destination databases and install symmetricDs on it.

  • You need to build the schema(database structure) on the destination(Mysql) node by symmetricds script or by insert yourself.

  • You need to create 2 users on 2 nodes who have access at read and write to database

  • You need to create the engine file for source and destination in the intermediate(s) node similar like this:

For mssql.properties

  engine.name=MSSQL
  db.driver=net.sourceforge.jtds.jdbc.Driver
db.url=jdbc:jtds:sqlserver://[sqlserver:port]/[DatabaseName];useCursors=true;bufferMaxMemory=10240;lobBuffer=5242880

 db.user=[username]
 db.password=[password]

registration.url=
sync.url=http://localhost:8080/sync/MSSQL

group.id=source
external.id=0000000


job.purge.period.time.ms=7200000


job.routing.period.time.ms=5000

 job.push.period.time.ms=10000

 job.pull.period.time.ms=10000

 initial.load.create.first=true

For mysql.properties

engine.name=mysql

db.driver=com.mysql.jdbc.Driver

db.url=jdbc:mysql://<IP>/databasename?tinyInt1isBit=false


registration.url=http://localhost:8080/sync/MSSQL


group.id=destination
external.id=0000002

job.routing.period.time.ms=5000

job.push.period.time.ms=10000

job.pull.period.time.ms=10000

jobs.synchronized.enable=true
  • On the intermediate Node, You will need to CREATE SYMMETRICDS CONFIGURATION TABLES with following bin\symadmin --engine source create-sym-tables
  • On the intermediate Node, You need sql file containing the initial table configurations to insert to source engine node with at least 4 parameter below to make trigger and synchronization:
insert into sym_node_group (node_group_id, description) values ('MSSQL', 'SQL Servers Group');

insert into sym_node_group (node_group_id, description) values ('MYSQL', 'MYSQL server');

insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action) values ('MSSQL', 'MYSQL', 'P');

insert into sym_router 
(router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time)
values('MSSQL_2_MYSQL', 'MSSQL', 'MYSQL', 'default',current_timestamp, current_timestamp);
    
insert into sym_channel (channel_id, processing_order, max_batch_size, enabled, description) values ('main_channel', 1, 100000, 1, 'channel for replication');
 
insert into sym_trigger (trigger_id,source_table_name,channel_id,last_update_time,create_time) values ('table1','table1','main_channel',current_timestamp,current_timestamp);
    
insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values ('table1','MSSQL_2_MYSQL', 100, current_timestamp, current_timestamp);

insert into sym_trigger (trigger_id,source_table_name,channel_id,last_update_time,create_time) values ('table2','table2','main_channel',current_timestamp,current_timestamp);
    
insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values ('table2','MSSQL_2_MYSQL', 100, current_timestamp, current_timestamp);

  • And the last step is run the script for source engine node and destination engine node.

    If you want the connection is only one-way just

Related Question