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