MySQL-Utilities：mysqldiff

园子看到使用MySQL对比数据库表结构，参考测试发现

mysql> use test;

create table test1

(id int not null primary key,

a varchar(10) not null,

b varchar(10),

c varchar(10) comment 'c',

d int

)

ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='test1';

create table test2

(id int not null ,

a varchar(10),

b varchar(5),

c varchar(10),

D int

)

ENGINE=myisam DEFAULT CHARSET=utf8 COMMENT='test2';

[root@localhost uest]# mysqldiff --server1=root:mysql5635@localhost:3306 --server2=root:mysql5635@localhost:3306 --changes-for=server2 --show-reverse --difftype=sql test.test1:test.test2

bash: mysqldiff: command not found

[root@localhost uest]#

搜索才知道mysqldiff工具是官方MySQL-Utilities工具集的一个脚本。mysqldiff用来比较对象的定义是否相同并显示不同的地方，mysqldiff 是通过对象名称来进行比较的。
系统环境紧接上一篇，因此需下载相应的rpm包

[root@localhost tools]# wget https://cdn.mysql.com//Downloads/MySQLGUITools/mysql-utilities-1.6.5-1.el6.noarch.rpm

[root@localhost tools]# rpm -ivh mysql-utilities-1.6.-.el6.noarch.rpm

warning: mysql-utilities-1.6.-.el6.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

error: Failed dependencies:

    mysql-connector-python >= 2.0. is needed by mysql-utilities-1.6.-.el6.noarch

[root@localhost tools]#

需要依赖文件

[root@localhost tools]# wget https://cdn.mysql.com//Downloads/Connector-Python/mysql-connector-python-2.1.5-1.el6.i686.rpm

[root@localhost tools]# rpm -ivh mysql-connector-python-2.1.-.el6.i686.rpm

warning: mysql-connector-python-2.1.-.el6.i686.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing...                ########################################### [%]

   :mysql-connector-python ########################################### [%]

[root@localhost tools]# rpm -ivh mysql-utilities-1.6.-.el6.noarch.rpm

warning: mysql-utilities-1.6.-.el6.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing...                ########################################### [%]

   :mysql-utilities        ########################################### [%]

[root@localhost tools]#

然后就可以执行mysqldiff命令了

[root@localhost tools]# mysqldiff --server1=root:mysql5635@localhost:3306 --server2=root:mysql5635@localhost:3306 --changes-for=server2 --skip-table-options --show-reverse --difftype=sql test.test1:test.test2

# WARNING: Using a password on the command line interface can be insecure.

# server1 on localhost: ... connected.

# server2 on localhost: ... connected.

# Comparing test.test1 to test.test2                               [FAIL]

# Transformation for --changes-for=server2:

#

ALTER TABLE `test`.`test2`

  DROP COLUMN D,

  ADD PRIMARY KEY(`id`),

  CHANGE COLUMN b b varchar() NULL,

  ADD COLUMN d int() NULL AFTER c,

  CHANGE COLUMN a a varchar() NOT NULL,

  CHANGE COLUMN c c varchar() NULL COMMENT 'c';

#

# Transformation for reverse changes (--changes-for=server1):

#

# ALTER TABLE `test`.`test1`

#   DROP PRIMARY KEY,

#   DROP COLUMN d,

#   CHANGE COLUMN b b varchar() NULL,

#   ADD COLUMN D int() NULL AFTER c,

#   CHANGE COLUMN a a varchar() NULL,

#   CHANGE COLUMN c c varchar() NULL;

#

# Compare failed. One or more differences found.

[root@localhost tools]#

MySQL Utilities 提供一组命令行工具用于维护和管理 MySQL 服务器，可参考官方手册，或者查阅MySQL Utilities教程。

巴特西

MySQL-Utilities：mysqldiff

最新文章

热门文章