sqlserver定时同步数据到mysql

目录

sql server 表同步到mysql

1. sql  server 事务日志字段解释：

2. 表设计：

3. 业务逻辑：

项目结构

initdatabase

initdb.go

model

analylog.go

sync.go

cfg.json

main.go

---

sql server 表同步到mysql

1. sql  server 事务日志字段解释：

将Operation与Context字段内容（分别对应下面字符串的前者和后者）进行联合，判断对数据库的操作（这里只涉及到增删改）。

LOP_INSERT_ROWS  LCX_CLUSTERED      //表示新增一条数据
LOP_MODIFY_ROW   LCX_CLUSTERED      //表示更新一条数据
LOP_DELETE_ROWS  LCX_MARK_AS_GHOST  //表示删除一条数据
Page ID                             //表示sql server数据库，被操作的数据在第几页
Slot ID                             //表示sql server数据库，被操作的数据在第几条
Lock Information                    //该字段信息唯一，唯一是指，不管对该数据进行什么样的操作（增删改），该字段信息会保持不变
Current LSN                         //没进行一次操作（增删改），该字段的数值都会加1（16进制）

2. 表设计：

//这个是sql server 中对应的表
type Test struct {
   ID       int      `json:"id" gorm:"column:id"`
   Name     string   `json:"name" gorm:"column:name"`
   Age      int      `json:"age" gorm:"column:age"`
   Addres   string   `json:"addres" gorm:"column:addres"`
}

//这个是同步到mysql中的表
type SyncTest struct {
   ID          int        `json:"id" gorm:"column:id"`
   Name        string     `json:"name" gorm:"column:name"`
   Age         int        `json:"age" gorm:"column:age"`
   Addres      string     `json:"addres" gorm:"column:addres"`
   LockInfo    string     `json:"lockInfo" gorm:"column:lock_info"`
   CurrentLSN  int64      `json:"currentLSN" gorm:"column:current_lsn"`
}

mysql表需要多增加两个字段LockInfo和CurrentLSN（分别对应与上面的Lock Information和Current LSN），其他字段看自己需求筛选

3. 业务逻辑：

插入：根据Page ID和Slot ID查询sql server数据库插入的一条数据，更新到对应的mysql数据库中，并且把lockInfo和currentLsn一并插入

更新：根据Page ID和Slot ID查询sql server数据库更新的一条数据，根据lockInfo可以快速查询mysql数据库中被操作的数据，进行更新（前面提到过lockInfo不会发生改变，是唯一的）

删除：通过lockInfo可以快速找到要删除的数据。此外，将删除数据新产生的currentLSN进行保存，因为该currentLSN必然是当前最大的（前面提到过，每次事务currentLSN都会加1）

项目结构

initdatabase

initdb.go

package initdatabase

import (
	"database/sql"
	_ "github.com/denisenkom/go-mssqldb"
	"github.com/spf13/viper"
	"gorm.io/driver/mysql"
	"gorm.io/driver/sqlserver"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
	"gorm.io/gorm/schema"
	"log"
)

type DBPool struct {
	UicDB *gorm.DB
	Conn *sql.DB
	SQLDB *gorm.DB
}

var (
	DBP DBPool
)

func InitDB() {
	udb, err := gorm.Open(mysql.Open(viper.GetString("dsn.mysql_uic")), &gorm.Config{
		NamingStrategy: schema.NamingStrategy{
			//TablePrefix: "gormv2_",    //这个时表的前缀
			SingularTable: true,         //这个可以禁用复数表名
		},
		Logger: logger.Default.LogMode(logger.Silent),
	})
	if err != nil {
		panic(err)
	}
	DBP.UicDB = udb

	conn, err := sql.Open("mssql", viper.GetString("dsn.conn_string"))
	if err != nil {
		log.Fatal("Open Connection failed:", err.Error())
	}
	DBP.Conn = conn

	sqldb, err := gorm.Open(sqlserver.Open(viper.GetString("dsn.sql_server")), &gorm.Config{
		NamingStrategy: schema.NamingStrategy{
			SingularTable: true,
		},
		Logger: logger.Default.LogMode(logger.Silent),
	})
	if err != nil {
		panic(err)
	}
	DBP.SQLDB = sqldb
}

model

analylog.go

package model

import (
	"fmt"
	"github.com/robfig/cron"
	"github.com/spf13/viper"
	"log"
	"strconv"
	"syncdata/initdatabase"
	"time"
)

const (
	InsetParam = "LOP_INSERT_ROWS,LCX_CLUSTERED"       //新增一条数据
	UpdateParam = "LOP_MODIFY_ROW,LCX_CLUSTERED"       //更新一条数据
	DeleteParam = "LOP_DELETE_ROWS,LCX_MARK_AS_GHOST"  //删除一条数据
)

var (
	operaStr       string   //对应上面参数的前半部分
	contextStr     string   //对应上面参数的后半部分
	operaContext   string
    slotId         string   //对应表的第几条数据
    count          int
	lockInfo       string   //一一对应一条数据，不管对该数据进行什么操作，该字段内容都不会发生改变
	currentLSN     int64    //对应每条事务日志，对表中数据进行的任何操作，都会更新该字段信息
)

func TimedTask() {
	c := cron.New()
	_ = c.AddFunc(viper.GetString("timed"), func() {
		ParseLog()
	})
	c.Start()
}

func ParseLog(){
	con, err := initdatabase.DBP.Conn.Prepare(`SELECT [Operation], [Context], [Page ID], [Slot ID], [Lock Information], [Current LSN] FROM [sys].[fn_dblog](NULL,NULL) WHERE [AllocUnitName]='dbo.test.PK__test__3213E83FF65A8F50' ORDER BY [Current LSN] ASC`)
	if err != nil {
		log.Fatal("Prepare failed:", err.Error())
	}
	defer con.Close()
	rows, err := con.Query()
	if err != nil {
		log.Fatal("Query failed:", err.Error())
	}
	//LogField存放字段信息[Operation Context Page ID Slot ID Lock Information Current LSN]
	LogField, err := rows.Columns()
	logData := make([]interface{}, len(LogField))
	for i := 0; i < len(LogField); i++ {
		logData[i] = new(interface{})
	}
	//遍历sql server事务日志的每一行
	for rows.Next() {
		//将一行数据写入colsData
		rows.Scan(logData...)
		//根据查出的事务日志，进行分析
		AnalyLog(logData)
	}
	defer rows.Close()
}

func AnalyLog(logData []interface{}) {
	for _, val := range logData {
		switch v := (*(val.(*interface{}))).(type) {
		case nil:
			count++
		case bool:
			if v {
				fmt.Print("True")
			} else {
				fmt.Print("False")
			}
		case []byte:
			fmt.Print(string(v))
		case time.Time:
			fmt.Print(v.Format("2016-01-02 15:05:05.999"))
		default:
			if count == 0 {
				operaStr = fmt.Sprint(v)
				count++
				continue
			} else if count == 1 {
				contextStr = fmt.Sprint(v)
				operaContext = operaStr + "," + contextStr
				count++
				continue
			} else if count == 2 {
				count++
			} else if count == 3 {
				count++
				slotId = fmt.Sprint(v)
			} else if count == 4 {
				count++
				if len(fmt.Sprint(v)) >136 {
					lockInfo = fmt.Sprint(v)[136:]
				}
			} else {
				count=0
				currentLSN,_ = strconv.ParseInt(fmt.Sprint(v)[9:17],16,64)
			}
		}
	}
	//查询最大的current_Lsn
	//每一次对数据库的操作都会对current_lsn进行更新，current_lsn递增
	//因此，大于最大的current_lsn才是需要执行的操作
	var maxLsn int64
	query := initdatabase.DBP.UicDB.Model(&SyncTest{}).Order("current_lsn desc").Select("current_lsn").First(&maxLsn)
	if query.Error != nil{
		return
	}
	if currentLSN < maxLsn {
		return
	}
	//判断是增删改的哪一种
	switch operaContext {
	case InsetParam:
		slotID,_ := strconv.Atoi(slotId)
		Inset(slotID, lockInfo, currentLSN)
		operaContext = ""
	case UpdateParam:
		slotID,_ := strconv.Atoi(slotId)
		Update(slotID, lockInfo, currentLSN)
		operaContext = ""
	case DeleteParam:
		Delete(lockInfo, currentLSN)
		operaContext = ""
	}
}

sync.go

package model

import (
	"syncdata/initdatabase"
)

//这个是sql server 中对应的表
type Test struct {
	ID       int      `json:"id" gorm:"column:id"`
	Name     string   `json:"name" gorm:"column:name"`
	Age      int      `json:"age" gorm:"column:age"`
	Addres   string   `json:"addres" gorm:"column:addres"`
}

//这个是同步到mysql中的表
type SyncTest struct {
	ID          int        `json:"id" gorm:"column:id"`
	Name        string     `json:"name" gorm:"column:name"`
	Age         int        `json:"age" gorm:"column:age"`
	Addres      string     `json:"addres" gorm:"column:addres"`
	LockInfo    string     `json:"lockInfo" gorm:"column:lock_info"`
	CurrentLSN  int64      `json:"currentLSN" gorm:"column:current_lsn"`
}

func (SyncTest) TableName() string {
	return "sync_test"
}

func (Test) TableName() string{
	return "test"
}

func Inset(slotId int, lockInfo string, currentLSN int64) {
	//根据slotId查询sql server数据库插入的一条数据，更新到对应的mysql数据库中，并且把lockInfo和currentLsn一并插入
	test := Test{}
	scan := initdatabase.DBP.SQLDB.Model(&Test{}).Offset(slotId).Find(&test)
	if scan.Error != nil {
		return
	}
	uicTest := SyncTest{
		ID:         test.ID,
		Name:       test.Name,
		Age:        test.Age,
		Addres:     test.Addres,
		LockInfo:   lockInfo,
		CurrentLSN: currentLSN,
	}
	insetData := initdatabase.DBP.UicDB.Create(&uicTest)
	if insetData.Error != nil {
		return
	}
}

func Update(slotId int, lockInfo string, currentLSN int64) {
	//lockInfo可以快速查询mysql数据库中被操作的数据
	updateTest := Test{}
	scan := initdatabase.DBP.SQLDB.Model(&Test{}).Offset(slotId).Find(&updateTest)
	if scan.Error != nil {
		return
	}
	updateData := initdatabase.DBP.UicDB.Model(&SyncTest{}).Where("lock_info = ?", lockInfo).Updates(SyncTest{
		ID:         updateTest.ID,
		Name:       updateTest.Name,
		Age:        updateTest.Age,
		Addres:     updateTest.Addres,
		CurrentLSN: currentLSN,
	})
	if updateData.Error != nil {
		return
	}
}

func Delete(lockInfo string, currentLSN int64) {
	//通过lockInfo可以快速找到要删除的数据，并且将删除数据新产生的currentLSN进行保存，因为该currentLSN必然是当前最大的
	//删除相应的数据
	deleteData := initdatabase.DBP.UicDB.Where("lock_info = ?", lockInfo).Delete(&SyncTest{})
	if deleteData.Error != nil {
		return
	}
	var id int
	query := initdatabase.DBP.UicDB.Model(&SyncTest{}).Order("current_lsn desc").Select("id").First(&id)
	if query.Error != nil {
		return
	}
	//将currentLSN保存
	updateLsn := initdatabase.DBP.UicDB.Model(&SyncTest{}).Where("id = ?", id).Update("current_lsn", currentLSN)
	if updateLsn.Error != nil {
		return
	}
}

cfg.json

{
  "dsn": {
    "mysql_uic": "user:password@tcp(192.168.*.*:3306)/uic?charset=utf8mb4&parseTime=True&loc=Local",
    "sql_server": "sqlserver://user:password@192.168.*.*:1433?database=OA",
    "mysql_dashboard": "user:password@tcp(192.168.*.*:3306)/dashboard?charset=utf8mb4&parseTime=True&loc=Local",
    "conn_string": "server=192.168.*.*;port1433;database=OA;user id=user;password=password"
  },
  "timed": "0 */1 * * * *"
}

main.go

package main

import (
	"flag"
	"github.com/spf13/viper"
	"log"
	"strings"
	"syncdata/initdatabase"
	"syncdata/model"
)

func main(){
	//解析配置文件
	cfg := flag.String("c", "cfg.json", "配置文件")
	flag.Parse()
	c := *cfg
	viper.AddConfigPath(".")
	c = strings.Replace(c, ".json", "", 1)
	viper.SetConfigName(c)
	err := viper.ReadInConfig()
	if err != nil {
		log.Fatal(err)
	}
	//初始化数据库
	initdatabase.InitDB()
	//定时任务
	model.TimedTask()
	//阻断
	select{}
}