SymmetricDS Setup – SQL Server 2008 to MySQL

Posted on

Question :

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

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:









  • On the intermediate Node, You will need to CREATE SYMMETRICDS CONFIGURATION TABLES with following binsymadmin --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 
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

Leave a Reply

Your email address will not be published. Required fields are marked *