3

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).

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.