Skip to content

Latest commit

 

History

History
137 lines (128 loc) · 4.33 KB

README.md

File metadata and controls

137 lines (128 loc) · 4.33 KB

MySQL-to-MSSQL

This is a simple server for synchronizing changes (CUD operations) from MySQL source database to MSSQL target database.

The architecture is simple:

  1. parser: listen to changes from source db (via binlog)
  2. changes will be logged into an embedded database
  3. syncer: scan the log store & sync logged events to target db on a fixed interval

DEMO:

MYSQL

  1. Set up local MYSQL instance with user root/root
  2. Use the staff table in sakila schema that comes with MySQL 8.0 installation package (demo db)

MSSQL

  1. Set up local MSSQL instance (2008+)

  2. Enable TCP/IP in Configuration Manager

  3. Create table Staff with same column names as MYSQL
    CREATE TABLE [dbo].[Staff](
        [staff_id] int NOT NULL primary key,
        [first_name] varchar(50) NOT NULL,
        [last_name] varchar(50) NOT NULL,
        [address_id] smallint NOT NULL,
        [picture] varbinary(max) NULL,
        [email] varchar(50) NULL,
        [store_id] smallint NOT NULL,
        [active] bit NOT NULL,
        [username] varchar(50) NULL,
        [password] varchar(50) NULL,
        [last_update] [datetime] NOT NULL,
    )
    ALTER TABLE [dbo].[Staff] SET (LOCK_ESCALATION = AUTO)

GO

  1. Install go 1.14 amd64. Note that this library has not work with go >=1.15 yet, and it does not support 32-bit platforms ⚠️

  2. Clone this repo to local machine, example: d:\demo

  3. CD into directory (cd d:\demo)

  4. Execute go run . It should start the Echo server

  5. Send a POST request to /struct/put to let server knows about the table structure. For details about type enum, see db/types.go
    //deliberately removed "username" & "password" from the request, so these 2 fields won't be synced
    {
        "table":"staff",
        "columns": [
            {
                "name": "staff_id",
                "type": 1,
                "is_primary": true // currently a PK must be defined, otherwise server won't know how to handle update/delete
            },
            {
                "name": "first_name",
                "type": 5
            },
            {
                "name": "last_name",
                "type": 5
            },
            {
                "name": "picture",
                "type": 18
            },
            {
                "name": "address_id",
                "type": 1
            },
            {
                "name": "store_id",
                "type": 1
            },
            {
                "name": "email",
                "type": 6
            },
            {
                "name": "last_update",
                "type": 9
            },
            {
                "name": "active",
                "type": 7
            }
        ]
    }
  6. Send a POST request with MySQL db info to /parser/start to start listening for changes
    {
        "server_id": 1,
        "addr": "127.0.0.1:3306",
        "user": "root",
        "password": "root",
        "use_decimal": true,
        "include_table_regex": ["sakila\\.staff"] //listen to changes from sakila.staff table only!
    }
  7. Send another POST request with MSSQL db info to /syncer/start to start syncing changes received from parser
    {
        "server": "127.0.0.1",
        "database": "master", //where Staff table is
        "log": 63, //full logging
        "appname": "mysql-to-mssql" //the programe_name in dm_exec_sessions
    }
🔥 Make changes & see sync 🔥

FAQ:

1. Why not SSIS?

  • No real time support
  • Incremental load requires something like lastupdated column from source table

2. Why not trigger?

  • Hard to maintain, you may not have full control to this trigger
  • SQL Developers might be unaware of this side effect
  • Slower performance (for example, trigger fires whenever a row is updated, whereas binlog only capture the final commited data once)