表结构同步工具 - mysql-schema-sync

数据库表同步

摘要

在项目中期阶段，我们不同环境的数据库结构同步会出现很多的问题，例如我们要部署生产环境时，由于一个字段同步漏了，生产环境的程序就可能会出问题，于是就可以使用“mysql-schema-sync”工具来生成数据库同步脚本

golang 、git环境安装

yum install go git

命令 go version 查看 go 环境是否安装成功

进入 /usr/lib/golang/src 目录下

# 安装 mysql-schema-sync
go get -u github.com/hidu/mysql-schema-sync

直接 GET 会超时，用代理还是靠谱些

export GOPROXY=https://goproxy.io
export GO111MODULE=on

# go.mod 配置
module github.com/zhaojh329/rttys
 
go 1.14
 
require (
        github.com/dwdcth/consoleEx v0.0.0-20180521133551-f56f6eb78b76
        github.com/gorilla/websocket v1.4.1
        github.com/howeyc/gopass v0.0.0-20190910152052-7cb4b85ec19c
        github.com/json-iterator/go v1.1.9
        github.com/kylelemons/go-gypsy v0.0.0-20160905020020-08cad365cd28
        github.com/mattn/go-colorable v0.1.4
        github.com/rakyll/statik v0.1.7
        github.com/rs/zerolog v1.18.0
        golang.org/x/crypto v0.0.0-20200311171314-f7b00557c8c4
)

执行安装后，输出以下日志

[root@VM_0_8_centos src]# go get -u github.com/hidu/mysql-schema-sync
go: finding github.com/hidu/mysql-schema-sync latest
go: downloading github.com/hidu/mysql-schema-sync v0.0.0-20200101011914-2d93ae04acf6
go: extracting github.com/hidu/mysql-schema-sync v0.0.0-20200101011914-2d93ae04acf6
go: downloading github.com/go-sql-driver/mysql v1.4.1
go: extracting github.com/go-sql-driver/mysql v1.4.1
go: downloading google.golang.org/appengine v1.6.2
go: extracting google.golang.org/appengine v1.6.2
go: finding github.com/go-sql-driver/mysql v1.5.0
go: finding google.golang.org/appengine v1.6.6
go: downloading github.com/go-sql-driver/mysql v1.5.0
go: extracting github.com/go-sql-driver/mysql v1.5.0

查看 go 环境变量配置

go env

找到 go 默认第三方源安装目录

GO111MODULE=""
GOARCH="amd64"
GOBIN=""
GOCACHE="/root/.cache/go-build"
GOENV="/root/.config/go/env"
GOEXE=""
GOFLAGS=""
GOHOSTARCH="amd64"
GOHOSTOS="linux"
GONOPROXY=""
GONOSUMDB=""
GOOS="linux"
GOPATH="/root/go"
GOPRIVATE=""
GOPROXY="direct"
GOROOT="/usr/lib/golang"
GOSUMDB="off"
GOTMPDIR=""
GOTOOLDIR="/usr/lib/golang/pkg/tool/linux_amd64"
GCCGO="gccgo"
AR="ar"
CC="gcc"
CXX="g++"
CGO_ENABLED="1"
GOMOD=""
CGO_CFLAGS="-g -O2"
CGO_CPPFLAGS=""
CGO_CXXFLAGS="-g -O2"
CGO_FFLAGS="-g -O2"
CGO_LDFLAGS="-g -O2"
PKG_CONFIG="pkg-config"
GOGCCFLAGS="-fPIC -m64 -pthread -fmessage-length=0 -fdebug-prefix-map=/tmp/go-build499542284=/tmp/go-build -gno-record-gcc-switches"

源安装地址为 /root/go

pwd
/root/go/bin

编辑 config.json

# vim config 文件，将以下配置替换
cat /root/go/src/github.com/hidu/mysql-schema-sync/config.json
{
     "source":"数据库用户名:数据库密码@(数据库IP:端口)/数据库名称",
     "dest":"数据库用户名:数据库密码@(数据库IP:端口)/数据库名称",
     "alter_ignore":{
        "tb1*":{
            "column":[""],
            "index":[""],
            "foreign":[]
        }
     },
     //  tables: table to check schema,default is all.eg :["order_*","goods"]
     "tables":[],
     //  tables_ignore: table to ignore check schema,default is Null :["order_*","goods"]
     "tables_ignore":[],
     "email":{
          "send_mail":false,
         "smtp_host":"smtp.163.com:25",
         "from":"xxx@163.com",
         "password":"xxx",
         "to":"xxx@163.com"
     }
}

执行以下命令查看同步表差异

./mysql-schema-sync -conf /root/go/src/github.com/hidu/mysql-schema-sync/config.json 2>/dev/null >db_alter.sql

汇总查询结果

1. 进入 bin 目录
cd /root/go/bin
2. 执行查询
./mysql-schema-sync -conf /root/go/src/github.com/hidu/mysql-schema-sync/config.json 2>/dev/null >db_alter.sql
或者
./mysql-schema-sync -conf /root/go/src/github.com/hidu/mysql-schema-sync/config.json

参考资料

GitHub：https://blog.csdn.net/liyyzz33/article/details/84587300

安装MySQL同步工具：https://github.com/hidu/mysql-schema-sync/

第三方包存储路径：https://blog.csdn.net/taoerchun/article/details/92766214

GO 目录详解：https://www.baidu.com/link?url=ygwRwgKo9cc0wbFACZ7opm1JSQDXGzy5NvRZqWb_jGYc8iSoWH9E0ZYO1B7jsDmWJy1WD_u64whZOeYHhfdASq&wd=&eqid=bd3f251400168765000000035f12a269