使用Navicat实现MySQL数据库 表结构同步

  categories:资料  author:

项目开发时间长了, 难免一些数据库结构不同了, 下面是发现数据库结构差异的方法

—————-

项目开发时,一般会有两个(或以上)的数据库,一个开发库和一个正式库,在开发库修改的表结构如何才能快速同步到正式库中呢?如果手动一个个字段或属性去修改,实在太麻烦,而且容易遗漏,这更不是一个搞IT的人该用的手段!

有了Navicat,就能快速实现数据库的同步,包括结构同步,数据同步等。下面详细介绍。
【前提】navicat已经创建了数据库连接。
1.选 工具→结构同步,如下
2.在打开的界面中,选择要同步的库(源库和目标库,源便是结构同步的参考数据库,目标则是要修改结构进行同步的那个数据库),并选择要对比的项。

你可以选择仅同步表,或仅同步视图或存储过程等。都设置好后点击比对按钮即可对比这两个数据库的结构,找出其中的不同。

3.比对结果如上图所示,可以在上面的源对象和目标对象列表中看出哪些表或视图或函数等结构有不同,具体的不同则可以SQL语句来看。

4.选中要执行的查询语句点击“运行查询”便可进行同步修改。

5.运行结果将在信息日志中显示,如果出现执行不成功的查询,则会以红色字体显示,可自己再手动进行修改。若无错误,则结构同步完成。
可到数据库中查看,两个表要同步的部分是否结构已经一致。

来源:https://blog.csdn.net/wangjunjun2008/article/details/46502393

——————

mysql-schema-sync 是一款使用 Go 开发跨平台的 MySQL 表结构自动同步工具。主要用于解决多个环境数据库表结构不同步问题。

mysql-schema-sync 支持功能:

  • 同步新表
  • 同步字段 变动:新增、修改
  • 同步索引 变动:新增、修改
  • 支持预览(只对比不同步变动)
  • 邮件通知变动结果
  • 支持屏蔽更新表、字段、索引、外键
  • 支持本地比线上额外多一些表、字段、索引、外键

项目地址:https://github.com/hidu/mysql-schema-sync

安装

mysql-schema-sync 的安装非常简单,开箱即用。

1
2
3
4
5
6
7
8
9
10
11
12
13
# 安装 Go
$ apt-get install golang-go

# 建立 GOPATH 目录
$ mkdir $HOME/mysql-schema-sync

# 设置 GOPATH 并把 $GOPATH/bin 目录加入到 $PATH 变量,便于使用下面的二进制程序。
$ vim ~/.bash_profile
export GOPATH=$HOME/mysql-schema-sync
export PATH="$PATH:$GOPATH/bin"

# 下载软件包
$ go get -u github.com/hidu/mysql-schema-sync

注:GOPATH 是作为编译后二进制的存放目的地和 import 包时的搜索路径。GOPATH 之下主要包含三个目录: bin、pkg、src。

  • src 存放源代码(比如:.go .c .h .s等)。
  • pkg 存放编译后生成的文件(比如:.a)。
  • bin 存放编译后生成的可执行文件。

配置

mysql-schema-sync 默认配置文件是 config.json,默认在 src/github.com/hidu/mysql-schema-sync/ 目录里。

1
2
$ ls src/github.com/hidu/mysql-schema-sync/
check.sh  config.json  create_dest.sh  fmt.sh  Godeps  internal  LICENSE  main.go  README.md  vendor
  • 配置文件示例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
$ cd src/github.com/hidu/mysql-schema-sync/
$ vim config.json

{
     "source":"root:000000@(127.0.0.1:3306)/wordpress",
     "dest":"root:000000@(127.0.0.1:3306)/wordpress_new",
     "alter_ignore":{
        "tb1*":{
            "column":["aaa","a*"],
            "index":["aa"],
            "foreign":[]
        }
     },
     //  tables: table to check schema,default is all.eg :["order_*","goods"]
     "tables":[],
     "email":{
         "send_mail":true,
         "smtp_host":"smtp.163.com:25",
         "from":"xxx@163.com",
         "password":"xxx",
         "to":"xxx@qq.com"
     }
}
  • 主要配置项说明
1
2
3
4
5
source: 设置同步的源数据库的相关信息。
dest: 设置同步的目标数据库的相关信息。
alter_ignore:忽略指定的表或字段,表名为 tableName,可以配置 column 和 index,支持通配符 *。
tables:配置需要同步的表,为空则是不限制。数组格式,eg: ["goods","order_*"]。
email:配置发送邮件通知的相关信息,当运行失败或者有表结构变化的时候你可以收到邮件通知。

运行

  • mysql-schema-sync 命令使用语法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
$ mysql-schema-sync# mysql-schema-sync  --help
Usage of mysql-schema-sync:
  -conf string
    	json config file path (default "./config.json")
  -dest string
    	mysql dsn dest,eg test@(127.0.0.1:3306)/imis
  -drop
    	drop fields,index,foreign key
  -mail_to string
    	overwrite config's email.to
  -source string
    	mysql dsn source,eg: test@(10.10.0.1:3306)/test
	when it is not empty ignore [-conf] param
  -sync
    	sync shcema change to dest db
  -tables string
    	table names to check
	eg : product_base,order_*

mysql schema sync tools 0.3
https://github.com/hidu/mysql-schema-sync/
  • mysql-schema-sync 参数说明
1
2
3
4
5
6
-conf string 指定配置文件名称。
-dest string 指定同步的目的数据库,eg:root:000000@(127.0.0.1:3306)/wordpress。
-source string 指定同步的源数据库 eg:root:000000@(127.0.0.1:3306)/wordpress_new。该项不为空时,忽略读入 -conf 参数项。
-drop 是否对本地多出的字段和索引进行删除,默认值为否。
-sync 是否将修改同步到目标数据库中去,默认值为否。
-tables string 待检查同步的数据库表,为空则是全部。eg : product_base,order_*。
  • 预览并生成变更SQL

默认情况下,不会直接把变更应用到目标数据库的。

1
2
3
4
5
6
7
8
9
$ mysql-schema-sync -conf config.json > db_alter.sql  2>/dev/null 
$ cat db_alter.sql

-- Table : wp_2_options
-- Type  : alter
-- RealtionTables :
-- SQL   :
ALTER TABLE `wp_2_options`
ADD `addnewfield2` int(11) DEFAULT NULL;
  • 直接生成变更并同步到目标库
1
$ mysql-schema-sync -conf config.json -sync
  • 同时同步到多个目标库

官方有提供一个 SHELL 脚本 check.sh 可以同时在多个库间同步。只需为要同步的多个库生成多个以 .json 为后缀的配置文件,check.sh 脚本会依次运行每份配置。

脚本默认在当前目录调用 mysql-schema-sync,这里需要根据实际情况修改下:

1
2
3
4
5
6
7
8
$ cat check.sh
...

for f in `ls *.json`
do
     mysql-schema-sync -conf $f -sync
done
...

运行脚本,完成后会在当前目录的 log 目录下记录每次运行后的日志。

1
$ bash check.sh
  • 定时完成数据库同步

如果你需经常定时同步数据库,可以按下格式配置一个计划任务:

1
30 * * * * cd /your/path/xxx/ && bash check.sh >/dev/null 2>&1

参考文档

http://www.google.com

https://github.com/hidu/mysql-schema-sync

—————————–

MySql之自动同步表结构

开发痛点

在开发过程中,由于频繁的修改数据库的字段,导致rd和qa环境的数据库表经常不一致。
而由于这些修改数据库的操作可能由多个rd操作,很难一次性收集全。人手工去和qa环境对字段又特别繁琐,容易遗漏。

解决之道

于是笔者就写了一个能够自动比较两个数据库的表结构,并生成alter语句的程序。同时还可以进行配置从而自动这行这些alter语句。详情见github

原理

同步新增的表

如果rd环境新增的表,而qa环境没有,此程序可以直接输出create table语句。原理如下:
addtable
用到的sql主要有:

show table from rd_db;
show create table added_table_name;

同步表结构

如果rd表结构有改动,而qa环境没有,此程序可以直接输出alter语句,原理如下:
synctable
用到的sql有:

select 
	COLUMN_NAME,COLUMN_TYPE,IS_NULLABLE,COLUMN_DEFAULT,COLUMN_COMMENT,EXTRA 
from 
	information_schema.columns
where 
 	TABLE_SCHEMA='rd_db'
 	and TABLE_NAME = 'rd_table';

比较表结构的代码:

        for (Column column : sourceTable.getColumns().values()) {
            if (targetTable.getColumns().get(column.getName()) == null) {
                // 如果对应的target没有这个字段,直接alter
                String sql = "alter table " + target.getSchema() + "." + targetTable.getTableName() + " add " + column
                        .getName() + " ";
                sql += column.getType() + " ";
                if (column.getIsNull().equals("NO")) {
                    sql += "NOT NULL ";
                } else {
                    sql += "NULL ";
                }
                if (column.getDefaultValue() != null) {
                    sql += "DEFAULT " + SqlUtil.getDbString(column.getDefaultValue()) + " ";
                }
                if (column.getComment() != null) {
                    sql += "COMMENT " + SqlUtil.getDbString(column.getComment()) + " ";
                }
                if (after != null) {
                    sql += "after " + after;
                }
                changeSql.add(sql+";");
            } else {
                // 检查对应的source 和 target的属性
                String sql =
                        "alter table " + target.getSchema() + "." + targetTable.getTableName() + " change " + column
                                .getName() + " ";
                Column sourceColumn = column;
                Column targetColumn = targetTable.getColumns().get(sourceColumn.getName());
                // 比较两者字段,如果返回null,表明一致
                String sqlExtend = compareSingleColumn(sourceColumn, targetColumn);
                if (sqlExtend != null) {
                    changeSql.add(sql + sqlExtend+";");
                }
            }
            after = column.getName();
        }

同步索引结构

如果rd表的索引有改变,而qa环境没有,此程序可以直接输出修改索引语句。原理和上面类似,在此不再赘述。

配置

sourceHost=127.0.0.1:3306
sourceUser=root
sourcePass=123123123
sourceSchema=mystique_db
sourceCharset=utf8

targetHost=127.0.0.1:3306
targetUser=root
targetPass=123123123
targetSchema=mystique_test
targetCharset=utf8

autoExecute=YES //此处表明自动同步

运行

按照上面的模板进行配置 用IDE打开,找到

alchemystar.runner.ShellRunner 

运行其中的main方法即可

生成效果展示

alter table mystique_test.t_test_3 change id id bigint(20) NOT NULL AUTO_INCREMENT COMMENT ''
alter table mystique_test.t_test_3 add index (name)
alter table mystique_test.t_test_3 drop index name_id
alter table mystique_test.t_test_3 add id_2 varchar(50) NULL DEFAULT '' COMMENT '' after name

如果打开了自动执行,会自动执行这些语句

github链接

https://github.com/alchemystar/Lancer

码云链接

https://git.oschina.net/alchemystar/Lancer

原文链接

https://my.oschina.net/alchemystar/blog/858996

 

————————-

MySQL表结构同步

   现在全身心投入到MySQL中。

     项目要求:将开发环境中的数据库的修改同步至线上环境。

     开发者给出的解决办法是:利用像Python 中的South框架,自动将开发环境的变更同步至生产环境。这个对于DBA来说是无法承受的(除非是创建数据库结果类似的语句)。数据库变更在生产环境执行,必须事先经过评估。对业务的影响降到最低,这个就设计到了重新设计索引,或者采用在线修改工具之类的。本文暂时先不讨论,下面给出表同步的解决办法。

     以前的做法是通过navicate的工具将一个一个的数据库进行对比或者自己写脚本Mysqldump 只dump 表结构,利用diff 挨个对比,在数据库实例量少的情况下,可以接受。总之个人认为 不符合运维自动化的理念。

     最近尝试了一种新的工具-Schema Sync,这个是基于Python语言,遵守Apache License, Version 2.0. 开源协议。

     描述信息:

      1、该工具不会修改数据库,至会搜集在不同数据库之间的差异并生成SQL脚本。

      2、生成的SQL语句,会自动添加after,first 等关键字

      3、对于重命名的表或者字段,该工具生成先删除在重建的SQL语句

      4、对于MySQL的分区功能暂不支持。

     下载并安装:

     安装条件:

      1、Python 2.4, 2.5, or 2.6

2、MySQL, version 5.0 or higher

3、MySQLdb, version 1.2.1p2 or higher

     下载: SchemaSync-0.9.2

     安装:

       tar xvzf SchemaSync-0.9.2.tar.gz
      cd SchemaSync-0.9.2 
      sudo python setup.py install

     如果想得到最新的开发版本,或者在生产环境中不允许root 登录,请参考这个链接:http://schemasync.org/install.htm

     基本语法:

      schemasync [options] <source> <target>

source或者target的格式是:

      mysql://user:password@host:port/database

最终生成文件的格式是:<database>[_<tag>].YYYYMMDD.(patch|revert)[_<version>].sql,有一个日志文件

一个patch文件(应用到目标主机),一个revert文件(可以做回滚,这个做法是很棒的,个人很赞同,但作为DBA,最好在操作前对线上的数据库做好备份)

具体选项,可以通过  schemasync  –help 查看

常用选项:

                  -output-directory=OUTPUT_DIRECTORY 日志文件和生成的SQL文件存放的目录
                  --tag=TAG 为自己的文件打上容易辨认的便签
                  -r, --revision 如果生成的结果文件中存在相同的名字,则会另加标示。
                  -a, --sync-auto-inc 这个选项要注意,他会同步自增长的值
                  -c  --sync-comments 同步新添加字段的内容  描述(经测试该选项无效)

测试:

在192.168.1.172上有一数据库sync 库内的表:t1(id int null)

在192.168.1.175上有一数据库sync 库内的表:t1(id int not null)

现在将172中的sync库同步至175上:

       schemasync  -r --output-dir="/root/sync/"  mysql://root:XXXX@localhost:3306/sync  mysql://root:XXXXX@192.168.1.175:3307/sync

打印出的消息:

[INFO  2012-07-13 18:21:49,784] Migration scripts created for mysql://192.168.1.175/sync
Patch Script: /root/sync/sync.20120713.patch.sql
Revert Script: /root/sync/sync.20120713.revert.sql

我们可以打开文件查看下:

      USE `sync`;
      ALTER TABLE `t1` MODIFY COLUMN `id` int(11) NULL FIRST, ENGINE=InnoDB ROW_FORMAT=Compact;

      这个文件已经将id列 的非空属性设置为空。将这个文件应用到175上即可。!

(PS:此为简单测试。在生成环境下,可以用shell循环同时同步多个库,对于DBA来说,最好在应用更改之前,必须审核一下该SQL,评估下对线上的影响,考虑在线DDL等工具)

     官方地址:http://schemasync.org/




快乐成长 每天进步一点点      京ICP备18032580号-1