使用mysql-schema-sync 自动同步数据库之间表结构

李指导、

已于 2023-08-18 14:25:01 修改

阅读量4k

点赞数 8

分类专栏：数据库文章标签：数据库 mysql sql mysqlSchemaSync 结构同步

于 2022-10-26 22:07:46 首次发布

本文链接：https://blog.csdn.net/LiHaoHang6/article/details/127539183

版权

数据库专栏收录该内容

10 篇文章 2 订阅

订阅专栏

应用背景

在主从同步情况下，很多方式只能同步数据，而源库表结构一旦被修改，则同步端将无法同步数据。类似于数据对接的项目，甲方提供数据，乙方提供数据库和业务系统，一个甲方一个数据库，如果甲方过多，那么维护数据库的成本就高，每次新增删除字段修改表结构都要去把每一个甲方的数据库进行修改，操作起来复杂麻烦。

mysql-schema-sync 介绍

SchemaSync是一款由Go开源的命令行工具，支持跨平台的、绿色无依赖的 MySQL 表结构自动同步工具。用于读取源DB和目标DB的schema，自动生成同步更新和回滚的sql，方便自动化的Schema同步。

官方地址：Schemasync.orghttps://www.schemasync.org/

源码地址：https://github.com/hidu/mysql-schema-sync

mysql-schema-sync 这类数据库表结构同步工具在许多应用场景下都非常有用，特别是在开发和维护数据库驱动的应用程序时。以下是一些可能的应用场景：

多环境开发： 在软件开发中，通常需要在不同的环境中进行测试、开发和生产。使用数据库表结构同步工具可以确保不同环境的数据库结构保持一致，从而避免由于结构差异而导致的错误。
团队协作： 如果有多个开发人员同时工作，他们可能会对同一个数据库进行不同的结构更改。这可能导致冲突和错误。使用同步工具，开发人员可以轻松地比较和合并彼此的更改，以确保数据库结构的一致性。
数据库迁移： 当您需要将数据库从一个服务器迁移到另一个服务器，或者从一个数据库版本升级到另一个版本时，同步工具可以帮助您在目标数据库中重建相同的表结构。
持续集成/持续交付（CI/CD）： 在 CI/CD 流程中，您可能需要在不同的部署环境中频繁地更新数据库结构。同步工具可以与自动化流程集成，确保数据库结构与应用程序代码的更改相匹配。
备份和恢复： 在恢复数据库到先前状态时，如果数据库结构与备份不匹配，可能会出现问题。同步工具可以帮助您在恢复过程中保持数据库结构的一致性。
版本控制： 将数据库结构纳入版本控制系统，以便跟踪结构的变化并与代码更改进行关联。同步工具可以协助将数据库结构更改纳入版本控制流程中。

mysql-schema-sync 主要功能

用于将线上数据库 Schema 变化同步到本地测试环境! 只同步 Schema、不同步数据。

支持功能：

同步新表
同步字段变动：新增、修改
同步索引变动：新增、修改
支持预览（只对比不同步变动）
邮件通知变动结果
支持屏蔽更新表、字段、索引、外键
支持本地比线上额外多一些表、字段、索引、外键
在该项目的基础上修复了比对过程中遇到分区表会终止后续操作的问题，支持分区表，对于分区表，会同步除了分区以外的变更。
支持每条 ddl 只会执行单个的修改，目的兼容tidb ddl问题 Unsupported multi schema change，通过single_schema_change字段控制，默认关闭。

一: centos7 安装 Go

由于 SchemaSync是一款由Go开源的命令行工具，所以要先配置GO环境

## 1.1 使用wget下载go安装包，或者本地下载后直接上传

[root@mysql Go]# wget -c https://studygolang.com/dl/golang/go1.13.3.linux-amd64.tar.gz

## 1.2 将go安装包解压至 /usr/local

[root@mysql Go]# tar -zxvf go1.13.3.linux-amd64.tar.gz -C /usr/local

[root@mysql Go]# cd /usr/local/go

## 1.3 设置环境变量

[root@mysql go]# vim /etc/profile
#Go
export GOROOT=/usr/local/go
export GOPATH=/home/hyh/gopath
export PATH=$PATH:$GOROOT/bin:$GOPATH/bin
## 1.4 刷新环境变量

[root@mysql go]# source /etc/profile

## 1.5 校验Go版本

[root@mysql go]# go version

二: centos7 安装编译mysql-schema-sync

##2.1 从github下载,参考教程：mysql-schema-sync表结构同步_我的喵叫初六的博客-CSDN博客_mysql-schema-sync

也可以用我编译过的 mysql-schema-sync 文件, 下载之后上传到服务器自定目录下。

链接地址:

https://pan.baidu.com/s/1aZmWg6MZBCoKpkc9bkjpGA
提取码：8804

## 2.2 将mysql-schema-sync文件赋值执行权限。

[root@mysql mysql-schema-sync]# chmod 777 mysql-schema-sync

三: mysql-schema-sync 解析

使用mysql-schema-sync 进行自动同步, 由两部分, 一个是mysql-schema-sync执行文件, 一个是执行同步的配置文件config.json

3.1 配置示例 (config.json):

{
      //source：数据库同步源,格式: 数据库用户:密码@(数据库IP:端口)/数据库名称 
      "source":"user:passwd@(127.0.0.1:3306)/test1",

      //dest：待同步的数据库,格式: 数据库用户:密码@(数据库IP:端口)/数据库名称 
      "dest":"user:passwd@(127.0.0.1:3306)/test2",
        
      //tables：数组,配置需要同步的表，为空则是不限制，eg: [“goods”,”order_”]
      "tables":[],

      //alter_ignore：同步时忽略的字段和索引，没有的话就瞎填，反正找不到也没事
      "alter_ignore":{
        "tb1*":{
            "column":["aaa","a*"], //参考格式
            "index":["aa"]   //参考格式
        }
      },

      //tables_ignore：忽略修改的配置，默认会跳过所有表的检测，所以可以填写一个不存在的表，就会检查所有表了
      "tables_ignore":["A"],

      //有变动或者失败时，邮件接收人
      "email":{
          "send_mail":false,
         "smtp_host":"smtp.163.com:25",
         "from":"xxx@163.com",
         "password":"xxx",
         "to":"xxx@163.com"
      }
}

config.json配置项说明:

#mysql-schema-sync -help

source:

         数据库同步源格式: 数据库用户:密码@(数据库IP:端口)/数据库名称
dest:

        待同步的数据库格式: 数据库用户:密码@(数据库IP:端口)/数据库名称
tables：

        数组，配置需要同步的表，为空则是不限制，eg: [“goods”,”order_”]
alter_ignore：

        同步时忽略的字段和索引，没有的话就瞎填，反正找不到也没事

tables_ignore：

        忽略修改的配置，默认会跳过所有表的检测，所以可以填写一个不存在的表，就会检查所有表了
email ：

         同步完成后发送邮件通知信息

3.2: 运行mysql-schema-sync,参数解释

#运行同步
mysql-schema-sync -conf config.json -sync

#预览并生成变更sql
mysql-schema-sync -conf config.json 2>/dev/null >db_alter.sql

四: 创建测试数据库和表

-- 创建test1数据库
create database test1;

-- 在test1数据库下创建test表
CREATE TABLE `test` (
  `A1` varchar(255) NOT NULL,
  `A2` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_croatian_ci DEFAULT NULL,
  `A3` varchar(255) DEFAULT NULL,
  `A4` varchar(255) DEFAULT NULL,
  `A5` varchar(255) DEFAULT NULL COMMENT '这是第五个字段',
  `A6` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`A1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

-- 创建test2数据库
create database test2;

-- 在test2数据库下创建test表
CREATE TABLE `test` (
  `A1` varchar(255) DEFAULT NULL,
  `A2` varchar(255) DEFAULT NULL,
  `A3` tinyint(255) DEFAULT NULL,
  `A4` varchar(10) DEFAULT NULL,
  `A5` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

A1：字段在"test1"数据库中是主键，在"test2"中不是。
A2：字段在"test1"数据库中字符集是ucs2，在"test2"中则是utf8mb4
A3：字段在"test1"数据库中类型是varchar，在"test2"中则是tinyint
A4：字段在"test1"数据库中长度是255，在"test2"中则是10
A5：字段在"test1"数据库中有注释，在"test2"中则没有
A6：字段在"test1"数据库中有，在"test2"中则没有

注: 另外也可以在test1数据库中创建一个在test2中不存在的表,也可以进行同步

五：执行脚本

## 5.1 执行mysql-schema-sync命令预览并生成预执行SQL

[root@mysql mysql-schema-sync]# /opt/mysql-schema-sync/mysql-schema-sync -conf config.json 2>/dev/null >db_alter.sql

可以看到生成了一个db_alter.sql的文件, 将文件打开,可以看到将会执行的SQL语句。

可以看到,将test2数据库的test表按照test1数据库的test表进行更改了。

-- Table : test
-- Type : alter
-- RelationTables :
-- Comment :
-- SQL :
ALTER TABLE `test`
CHANGE `A1` `A1` varchar(255) NOT NULL,
CHANGE `A2` `A2` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_croatian_ci DEFAULT NULL,
CHANGE `A3` `A3` varchar(255) DEFAULT NULL,
CHANGE `A4` `A4` varchar(255) DEFAULT NULL,
CHANGE `A5` `A5` varchar(255) DEFAULT NULL COMMENT '这是第五个字段',
ADD `A6` varchar(32) DEFAULT NULL AFTER A5,
ADD PRIMARY KEY (`A1`);

## 5.2 执行mysql-schema-sync命令, 运行

[root@mysql mysql-schema-sync]# /opt/mysql-schema-sync/mysql-schema-sync -conf config.json -sync

[root@mysql mysql-schema-sync]# /opt/mysql-schema-sync/mysql-schema-sync -conf config.json -sync
2022/10/26 db.go:86: [SQL] [source] show table status []
2022/10/26 db.go:86: [SQL] [dest] show table status []
2022/10/26 db.go:86: [SQL] [source] show create table `test` []
2022/10/26 db.go:86: [SQL] [dest] show create table `test` []
2022/10/26 sync.go:140: [Debug] check column.alter  test.A1 alterSQL= CHANGE `A1` `A1` varchar(255) NOT NULL
2022/10/26 sync.go:140: [Debug] check column.alter  test.A2 alterSQL= CHANGE `A2` `A2` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_croatian_ci DEFAULT NULL
2022/10/26 sync.go:140: [Debug] check column.alter  test.A3 alterSQL= CHANGE `A3` `A3` varchar(255) DEFAULT NULL
2022/10/26 sync.go:140: [Debug] check column.alter  test.A4 alterSQL= CHANGE `A4` `A4` varchar(255) DEFAULT NULL
2022/10/26 sync.go:140: [Debug] check column.alter  test.A5 alterSQL= CHANGE `A5` `A5` varchar(255) DEFAULT NULL COMMENT '这是第五个字段'
2022/10/26 sync.go:140: [Debug] check column.alter  test.A6 alterSQL= ADD `A6` varchar(32) DEFAULT NULL AFTER A5
2022/10/26 sync.go:174: [Debug] indexName---->[ test.PRIMARY KEY ] dest_has: false 
dest_idx: null 
source_idx: {
   "IndexType": "PRIMARY",
   "Name": "PRIMARY KEY",
   "SQL": "PRIMARY KEY (`A1`)",
   "RelationTables": []
  }
2022/10/26 sync.go:186: [Debug] check index.alter  test.PRIMARY KEY alterSQL= [ADD PRIMARY KEY (`A1`)]
-- Table : test
-- Type : alter
-- RelationTables :
-- Comment :
-- SQL :
ALTER TABLE `test`
CHANGE `A1` `A1` varchar(255) NOT NULL,
CHANGE `A2` `A2` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_croatian_ci DEFAULT NULL,
CHANGE `A3` `A3` varchar(255) DEFAULT NULL,
CHANGE `A4` `A4` varchar(255) DEFAULT NULL,
CHANGE `A5` `A5` varchar(255) DEFAULT NULL COMMENT '这是第五个字段',
ADD `A6` varchar(32) DEFAULT NULL AFTER A5,
ADD PRIMARY KEY (`A1`);

2022/10/26 sync.go:358: [Debug] changedTables: map[single_test:[-- Table : test
-- Type : alter
-- RelationTables :
-- Comment :
-- SQL :
ALTER TABLE `test`
CHANGE `A1` `A1` varchar(255) NOT NULL,
CHANGE `A2` `A2` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_croatian_ci DEFAULT NULL,
CHANGE `A3` `A3` varchar(255) DEFAULT NULL,
CHANGE `A4` `A4` varchar(255) DEFAULT NULL,
CHANGE `A5` `A5` varchar(255) DEFAULT NULL COMMENT '这是第五个字段',
ADD `A6` varchar(32) DEFAULT NULL AFTER A5,
ADD PRIMARY KEY (`A1`);]]
2022/10/26 sync.go:370: runSyncType: single_test
2022/10/26 sync.go:265: Exec_SQL_START:
>>>>>>
ALTER TABLE `test`
CHANGE `A1` `A1` varchar(255) NOT NULL,
CHANGE `A2` `A2` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_croatian_ci DEFAULT NULL,
CHANGE `A3` `A3` varchar(255) DEFAULT NULL,
CHANGE `A4` `A4` varchar(255) DEFAULT NULL,
CHANGE `A5` `A5` varchar(255) DEFAULT NULL COMMENT '这是第五个字段',
ADD `A6` varchar(32) DEFAULT NULL AFTER A5,
ADD PRIMARY KEY (`A1`);
<<<<<<<<

2022/10/26 db.go:86: [SQL] [dest] ALTER TABLE `test`
CHANGE `A1` `A1` varchar(255) NOT NULL,
CHANGE `A2` `A2` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_croatian_ci DEFAULT NULL,
CHANGE `A3` `A3` varchar(255) DEFAULT NULL,
CHANGE `A4` `A4` varchar(255) DEFAULT NULL,
CHANGE `A5` `A5` varchar(255) DEFAULT NULL COMMENT '这是第五个字段',
ADD `A6` varchar(32) DEFAULT NULL AFTER A5,
ADD PRIMARY KEY (`A1`); []
2022/10/26 sync.go:298: EXEC_SQL_SUCCESS, used: 0.080036 s
2022/10/26 sync.go:300: EXEC_SQL_RET: [] <nil>
2022/10/26 db.go:86: [SQL] [dest] show create table `test` []
2022/10/26 sync.go:408: execute_all_sql_done, success_total: 1 failed_total: 0
2022/10/26 statics.go:216: html result: /tmp/mysql-schema-sync_last.html <nil>
[root@mysql mysql-schema-sync]#

## 5.3 再次对比两边数据库表结构。

可以看到,表结构已经完全一致。

六: 将自动同步数据库之间表结构,加入shell脚本定时执行

未完待续:.......

参考:

        mysql-schema-sync表结构同步_我的喵叫初六的博客-CSDN博客_mysql-schema-sync

        MySQL表结构自动同步工具mysql-schema-sync安装使用 - DBA社区 - 博客园

  mysql-schema-sync- MySQL表结构同步工具-面圈网

李指导、

关注

8
点赞
踩
21

收藏

觉得还不错? 一键收藏
打赏
10
评论
使用mysql-schema-sync 自动同步数据库之间表结构

SchemaSync是一款由Go开源的命令行工具，支持跨平台的、绿色无依赖的 MySQL 表结构自动同步工具。用于读取源DB和目标DB的schema，自动生成同步更新和回滚的sql，方便自动化的Schema同步。官方地址：mysql-schema-sync 这类数据库表结构同步工具在许多应用场景下都非常有用，特别是在开发和维护数据库驱动的应用程序时。在软件开发中，通常需要在不同的环境中进行测试、开发和生产。使用数据库表结构同步工具可以确保不同环境的数据库结构保持一致，从而避免由于结构差异而导致的错误。
复制链接

扫一扫