MySQL管理工具MySQL Utilities — mysqldiff(11)

默北
MySQL1 34,6573字数 35277阅读117分35秒阅读模式

mysqldiff 顾名思义就是来diff比较的，相当于Linux下的diff命令。mysqldiff 用来比较对象的定义是否相同并显示不同的地方，mysqldiff 是通过对象名称来进行比较的。如果要比较数据是否一致，那就要用到mysqldbcompare了，参见前面文章。

当指定了数据库对，所有的对象就要进行比较。如果其中某个库中出现的对象在另一个库中没有出现，将报错。信息如下所示：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldiff/

# WARNING: Objects in server2.ttlsa_com but not in server1.ttlsa_com:
# TABLE: t
Compare failed. One or more differences found.文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldiff/

如果要比较特定对象，使用db.obj 格式。db1.obj1:db2 和db1:db2.obj2 这样类型的格式是非法的。报错信息如下所示：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldiff/

mysqldiff: error: Incorrect object compare argument, one specific object is missing. Please verify that both object are correctly specified. No object has been specified for db1 'ttlsa_com', while object 't' was specified for db2 'ttlsa_com'. Format should be: db1[.object1]:db2[.object2].文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldiff/

比较同一实例上的不同数据库只需要指定--server1，不同的实例的话还需要指定--server2。 在这种情况下，数据库对左边的对象来自server1，右边的对象来自server2。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldiff/

执行完后，会生成一个差异报告。也可以生成一个转换的SQL语句报告来更改使两者相同。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldiff/

输出类型，也就是difftype选项值：

  • unified (默认)显示统一的格式输出.
  • context以上下文格式输出
  • differ以differ-style 格式输出.
  • sql以生成转换SQL语句输出.

要对两物进行比较，就需要参照物。参照物可以任意选定，可以选server1，也可以选server2，选择不同的参照物来描述同一物体的状态，可能得出的结论不同。“小小竹排江中游，巍巍青山两岸走” 一样的道理不是么？文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldiff/

--changes-for选项来控制对比方向。默认是以server1。同时显示，可以使用--show-reverse选项。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldiff/

  • --changes-for=server1： 以server2为参照物，针对server1
  • --changes-for=server2：以server1为参照物，针对server2
# mysqldiff --server1=instance_3306 --server2=instance_3308  ttlsa_com:ttlsa_com --difftype=sql --show-reverse -vvv
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.

# Definition for object ttlsa_com:
CREATE DATABASE `ttlsa_com` /*!40100 DEFAULT CHARACTER SET latin1 */

# Definition for object ttlsa_com:
CREATE DATABASE `ttlsa_com` /*!40100 DEFAULT CHARACTER SET latin1 */
# Comparing `ttlsa_com` to `ttlsa_com`                             [PASS]

# Definition for object ttlsa_com.bbs_categories:
CREATE TABLE `bbs_categories` (
  `cid` smallint(5) NOT NULL AUTO_INCREMENT,
  `pid` smallint(5) NOT NULL DEFAULT '0',
  `cname` varchar(30) DEFAULT NULL COMMENT '分类名称',
  `content` varchar(255) DEFAULT NULL,
  `keywords` varchar(255) DEFAULT NULL,
  `ico` varchar(128) DEFAULT NULL,
  `master` varchar(100) NOT NULL,
  `permit` varchar(255) DEFAULT NULL,
  `listnum` mediumint(8) unsigned DEFAULT '0',
  `clevel` varchar(25) DEFAULT NULL,
  `cord` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`cid`,`pid`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

# Definition for object ttlsa_com.bbs_categories:
CREATE TABLE `bbs_categories` (
  `cid` smallint(5) NOT NULL AUTO_INCREMENT,
  `pid` smallint(5) NOT NULL DEFAULT '0',
  `cname` varchar(30) DEFAULT NULL COMMENT '分类名称',
  `content` varchar(255) DEFAULT NULL,
  `keywords` varchar(255) DEFAULT NULL,
  `ico` varchar(128) DEFAULT NULL,
  `master` varchar(100) NOT NULL,
  `permit` varchar(255) DEFAULT NULL,
  `listnum` mediumint(8) unsigned DEFAULT '0',
  `clevel` varchar(25) DEFAULT NULL,
  `cord` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`cid`,`pid`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
# Comparing `ttlsa_com`.`bbs_categories` to `ttlsa_com`.`bbs_categories`   [PASS]

# Definition for object ttlsa_com.bbs_comments:
CREATE TABLE `bbs_comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fid` int(11) NOT NULL DEFAULT '0',
  `uid` int(11) NOT NULL DEFAULT '0',
  `content` text,
  `replytime` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`,`fid`,`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=371 DEFAULT CHARSET=utf8

# Definition for object ttlsa_com.bbs_comments:
CREATE TABLE `bbs_comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fid` int(11) NOT NULL DEFAULT '0',
  `uid` int(11) NOT NULL DEFAULT '0',
  `content` text,
  `replytime` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`,`fid`,`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=371 DEFAULT CHARSET=utf8
# Comparing `ttlsa_com`.`bbs_comments` to `ttlsa_com`.`bbs_comments`   [PASS]

# Definition for object ttlsa_com.bbs_favorites:
CREATE TABLE `bbs_favorites` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `uid` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `favorites` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `content` mediumtext NOT NULL,
  PRIMARY KEY (`id`,`uid`),
  KEY `uid` (`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

# Definition for object ttlsa_com.bbs_favorites:
CREATE TABLE `bbs_favorites` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `uid` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `favorites` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `content` mediumtext NOT NULL,
  PRIMARY KEY (`id`,`uid`),
  KEY `uid` (`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
# Comparing `ttlsa_com`.`bbs_favorites` to `ttlsa_com`.`bbs_favorites`   [PASS]

# Definition for object ttlsa_com.bbs_forums:
CREATE TABLE `bbs_forums` (
  `fid` int(11) NOT NULL AUTO_INCREMENT,
  `cid` smallint(5) NOT NULL DEFAULT '0',
  `uid` mediumint(8) NOT NULL DEFAULT '0',
  `ruid` mediumint(8) DEFAULT NULL,
  `title` varchar(128) DEFAULT NULL,
  `keywords` varchar(255) DEFAULT NULL,
  `content` text,
  `addtime` int(10) DEFAULT NULL,
  `updatetime` int(10) DEFAULT NULL,
  `lastreply` int(10) DEFAULT NULL,
  `views` int(10) DEFAULT '0',
  `comments` smallint(8) DEFAULT '0',
  `favorites` int(10) unsigned DEFAULT '0',
  `closecomment` tinyint(1) DEFAULT NULL,
  `is_top` tinyint(1) NOT NULL DEFAULT '0',
  `is_hidden` tinyint(1) NOT NULL DEFAULT '0',
  `ord` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`fid`,`cid`,`uid`),
  KEY `updatetime` (`updatetime`),
  KEY `ord` (`ord`)
) ENGINE=MyISAM AUTO_INCREMENT=94 DEFAULT CHARSET=utf8

# Definition for object ttlsa_com.bbs_forums:
CREATE TABLE `bbs_forums` (
  `fid` int(11) NOT NULL AUTO_INCREMENT,
  `cid` smallint(5) NOT NULL DEFAULT '0',
  `uid` mediumint(8) NOT NULL DEFAULT '0',
  `ruid` mediumint(8) DEFAULT NULL,
  `title` varchar(128) DEFAULT NULL,
  `keywords` varchar(255) DEFAULT NULL,
  `content` text,
  `addtime` int(10) DEFAULT NULL,
  `updatetime` int(10) DEFAULT NULL,
  `lastreply` int(10) DEFAULT NULL,
  `views` int(10) DEFAULT '0',
  `comments` smallint(8) DEFAULT '0',
  `favorites` int(10) unsigned DEFAULT '0',
  `closecomment` tinyint(1) DEFAULT NULL,
  `is_top` tinyint(1) NOT NULL DEFAULT '0',
  `is_hidden` tinyint(1) NOT NULL DEFAULT '0',
  `ord` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`fid`,`cid`,`uid`),
  KEY `updatetime` (`updatetime`),
  KEY `ord` (`ord`)
) ENGINE=MyISAM AUTO_INCREMENT=94 DEFAULT CHARSET=utf8
# Comparing `ttlsa_com`.`bbs_forums` to `ttlsa_com`.`bbs_forums`   [PASS]

# Definition for object ttlsa_com.bbs_links:
CREATE TABLE `bbs_links` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `url` varchar(200) DEFAULT NULL,
  `logo` varchar(200) DEFAULT NULL,
  `is_hidden` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

# Definition for object ttlsa_com.bbs_links:
CREATE TABLE `bbs_links` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `url` varchar(200) DEFAULT NULL,
  `logo` varchar(200) DEFAULT NULL,
  `is_hidden` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
# Comparing `ttlsa_com`.`bbs_links` to `ttlsa_com`.`bbs_links`     [PASS]

# Definition for object ttlsa_com.bbs_notifications:
CREATE TABLE `bbs_notifications` (
  `nid` int(11) NOT NULL AUTO_INCREMENT,
  `fid` int(11) DEFAULT NULL,
  `suid` int(11) DEFAULT NULL,
  `nuid` int(11) NOT NULL DEFAULT '0',
  `ntype` tinyint(1) DEFAULT NULL,
  `ntime` int(10) DEFAULT NULL,
  PRIMARY KEY (`nid`,`nuid`)
) ENGINE=MyISAM AUTO_INCREMENT=444 DEFAULT CHARSET=utf8

# Definition for object ttlsa_com.bbs_notifications:
CREATE TABLE `bbs_notifications` (
  `nid` int(11) NOT NULL AUTO_INCREMENT,
  `fid` int(11) DEFAULT NULL,
  `suid` int(11) DEFAULT NULL,
  `nuid` int(11) NOT NULL DEFAULT '0',
  `ntype` tinyint(1) DEFAULT NULL,
  `ntime` int(10) DEFAULT NULL,
  PRIMARY KEY (`nid`,`nuid`)
) ENGINE=MyISAM AUTO_INCREMENT=444 DEFAULT CHARSET=utf8
# Comparing `ttlsa_com`.`bbs_notifications` to `ttlsa_com`.`bbs_notifications`   [PASS]

# Definition for object ttlsa_com.bbs_page:
CREATE TABLE `bbs_page` (
  `pid` tinyint(6) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) DEFAULT NULL,
  `content` text,
  `go_url` varchar(100) DEFAULT NULL,
  `add_time` int(10) DEFAULT NULL,
  `is_hidden` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`pid`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

# Definition for object ttlsa_com.bbs_page:
CREATE TABLE `bbs_page` (
  `pid` tinyint(6) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) DEFAULT NULL,
  `content` text,
  `go_url` varchar(100) DEFAULT NULL,
  `add_time` int(10) DEFAULT NULL,
  `is_hidden` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`pid`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
# Comparing `ttlsa_com`.`bbs_page` to `ttlsa_com`.`bbs_page`       [PASS]

# Definition for object ttlsa_com.bbs_settings:
CREATE TABLE `bbs_settings` (
  `id` tinyint(5) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `value` text NOT NULL,
  `type` tinyint(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`title`,`type`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8

# Definition for object ttlsa_com.bbs_settings:
CREATE TABLE `bbs_settings` (
  `id` tinyint(5) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `value` text NOT NULL,
  `type` tinyint(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`title`,`type`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8
# Comparing `ttlsa_com`.`bbs_settings` to `ttlsa_com`.`bbs_settings`   [PASS]

# Definition for object ttlsa_com.bbs_tags:
CREATE TABLE `bbs_tags` (
  `tag_id` int(10) NOT NULL AUTO_INCREMENT,
  `tag_title` varchar(30) NOT NULL,
  `forums` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`tag_id`),
  UNIQUE KEY `tag_title` (`tag_title`)
) ENGINE=MyISAM AUTO_INCREMENT=185 DEFAULT CHARSET=utf8

# Definition for object ttlsa_com.bbs_tags:
CREATE TABLE `bbs_tags` (
  `tag_id` int(10) NOT NULL AUTO_INCREMENT,
  `tag_title` varchar(30) NOT NULL,
  `forums` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`tag_id`),
  UNIQUE KEY `tag_title` (`tag_title`)
) ENGINE=MyISAM AUTO_INCREMENT=185 DEFAULT CHARSET=utf8
# Comparing `ttlsa_com`.`bbs_tags` to `ttlsa_com`.`bbs_tags`       [PASS]

# Definition for object ttlsa_com.bbs_tags_relation:
CREATE TABLE `bbs_tags_relation` (
  `tag_id` int(10) NOT NULL DEFAULT '0',
  `fid` int(10) DEFAULT NULL,
  KEY `tag_id` (`tag_id`),
  KEY `fid` (`fid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

# Definition for object ttlsa_com.bbs_tags_relation:
CREATE TABLE `bbs_tags_relation` (
  `tag_id` int(10) NOT NULL DEFAULT '0',
  `fid` int(10) DEFAULT NULL,
  KEY `tag_id` (`tag_id`),
  KEY `fid` (`fid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
# Comparing `ttlsa_com`.`bbs_tags_relation` to `ttlsa_com`.`bbs_tags_relation`   [PASS]

# Definition for object ttlsa_com.bbs_user_follow:
CREATE TABLE `bbs_user_follow` (
  `follow_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(10) unsigned NOT NULL DEFAULT '0',
  `follow_uid` int(10) unsigned NOT NULL DEFAULT '0',
  `addtime` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`follow_id`,`uid`,`follow_uid`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

# Definition for object ttlsa_com.bbs_user_follow:
CREATE TABLE `bbs_user_follow` (
  `follow_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(10) unsigned NOT NULL DEFAULT '0',
  `follow_uid` int(10) unsigned NOT NULL DEFAULT '0',
  `addtime` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`follow_id`,`uid`,`follow_uid`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
# Comparing `ttlsa_com`.`bbs_user_follow` to `ttlsa_com`.`bbs_user_follow`   [PASS]

# Definition for object ttlsa_com.bbs_user_groups:
CREATE TABLE `bbs_user_groups` (
  `gid` int(11) NOT NULL AUTO_INCREMENT,
  `group_type` tinyint(3) NOT NULL DEFAULT '0',
  `group_name` varchar(50) DEFAULT NULL,
  `usernum` int(11) NOT NULL,
  PRIMARY KEY (`gid`,`group_type`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

# Definition for object ttlsa_com.bbs_user_groups:
CREATE TABLE `bbs_user_groups` (
  `gid` int(11) NOT NULL AUTO_INCREMENT,
  `group_type` tinyint(3) NOT NULL DEFAULT '0',
  `group_name` varchar(50) DEFAULT NULL,
  `usernum` int(11) NOT NULL,
  PRIMARY KEY (`gid`,`group_type`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
# Comparing `ttlsa_com`.`bbs_user_groups` to `ttlsa_com`.`bbs_user_groups`   [PASS]

# Definition for object ttlsa_com.bbs_users:
CREATE TABLE `bbs_users` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `password` char(32) DEFAULT NULL,
  `openid` char(32) NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  `avatar` varchar(100) DEFAULT NULL,
  `homepage` varchar(50) DEFAULT NULL,
  `money` int(11) DEFAULT '100',
  `signature` text,
  `forums` int(11) DEFAULT '0',
  `replies` int(11) DEFAULT '0',
  `notices` smallint(5) DEFAULT '0',
  `follows` int(11) NOT NULL DEFAULT '0',
  `regtime` int(10) DEFAULT NULL,
  `lastlogin` int(10) DEFAULT NULL,
  `lastpost` int(10) DEFAULT NULL,
  `qq` varchar(20) DEFAULT NULL,
  `group_type` tinyint(3) NOT NULL DEFAULT '0',
  `gid` tinyint(3) NOT NULL DEFAULT '3',
  `ip` char(15) DEFAULT NULL,
  `location` varchar(128) DEFAULT NULL,
  `token` varchar(40) DEFAULT NULL,
  `introduction` text,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`uid`,`group_type`)
) ENGINE=MyISAM AUTO_INCREMENT=109 DEFAULT CHARSET=utf8

# Definition for object ttlsa_com.bbs_users:
CREATE TABLE `bbs_users` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `password` char(32) DEFAULT NULL,
  `openid` char(32) NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  `avatar` varchar(100) DEFAULT NULL,
  `homepage` varchar(50) DEFAULT NULL,
  `money` int(11) DEFAULT '100',
  `signature` text,
  `forums` int(11) DEFAULT '0',
  `replies` int(11) DEFAULT '0',
  `notices` smallint(5) DEFAULT '0',
  `follows` int(11) NOT NULL DEFAULT '0',
  `regtime` int(10) DEFAULT NULL,
  `lastlogin` int(10) DEFAULT NULL,
  `lastpost` int(10) DEFAULT NULL,
  `qq` varchar(20) DEFAULT NULL,
  `group_type` tinyint(3) NOT NULL DEFAULT '0',
  `gid` tinyint(3) NOT NULL DEFAULT '3',
  `ip` char(15) DEFAULT NULL,
  `location` varchar(128) DEFAULT NULL,
  `token` varchar(40) DEFAULT NULL,
  `introduction` text,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`uid`,`group_type`)
) ENGINE=MyISAM AUTO_INCREMENT=109 DEFAULT CHARSET=utf8
# Comparing `ttlsa_com`.`bbs_users` to `ttlsa_com`.`bbs_users`     [PASS]

# Definition for object ttlsa_com.data:
CREATE TABLE `data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` char(30) NOT NULL DEFAULT '',
  `count` int(11) DEFAULT NULL,
  PRIMARY KEY (`value`),
  KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1

# Definition for object ttlsa_com.data:
CREATE TABLE `data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` char(30) NOT NULL DEFAULT '',
  `count` int(11) DEFAULT NULL,
  PRIMARY KEY (`value`),
  KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
# Comparing `ttlsa_com`.`data` to `ttlsa_com`.`data`               [PASS]

# Definition for object ttlsa_com.t_data:
CREATE TABLE `t_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` char(30) NOT NULL DEFAULT '',
  `count` int(11) DEFAULT NULL,
  PRIMARY KEY (`value`),
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

# Definition for object ttlsa_com.t_data:
CREATE TABLE `t_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` char(30) NOT NULL DEFAULT '',
  `count` int(11) DEFAULT NULL,
  PRIMARY KEY (`value`),
  KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
# Comparing `ttlsa_com`.`t_data` to `ttlsa_com`.`t_data`           [FAIL]
# Transformation for --changes-for=server1:
#

ALTER TABLE `ttlsa_com`.`t_data` 
  DROP INDEX id, 
  ADD INDEX id (id), 
ENGINE=MyISAM;

#
# Transformation for reverse changes (--changes-for=server2):
#
# ALTER TABLE `ttlsa_com`.`t_data` 
#   DROP INDEX id, 
#   ADD INDEX id (id), 
# ENGINE=InnoDB;
#

Compare failed. One or more differences found.

选项：

Usage: mysqldiff --server1=user:pass@host:port:socket --server2=user:pass@host:port:socket db1.object1:db2.object1 db3:db4

mysqldiff - compare object definitions among objects where the difference is
how db1.obj1 differs from db2.obj2

Options:
  --version             show program's version number and exit
  --help                display a help message and exit
  --license             display program's license and exit
  --ssl-ca=SSL_CA       The path to a file that contains a list of trusted SSL
                        CAs.
  --ssl-cert=SSL_CERT   The name of the SSL certificate file to use for
                        establishing a secure connection.
  --ssl-key=SSL_KEY     The name of the SSL key file to use for establishing a
                        secure connection.
  --server1=SERVER1     connection information for first server in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>] or <config-
                        path>[<[group]>].
  --server2=SERVER2     connection information for second server in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>] or <config-
                        path>[<[group]>].
  --character-set=CHARSET
                        sets the client character set. The default is
                        retrieved from the server variable
                        'character_set_client'.
  --width=WIDTH         display width
  --force               do not abort when a diff test fails
  -c, --compact         compact output from a diff.
  --skip-table-options  skip check of all table options (e.g., AUTO_INCREMENT,
                        ENGINE, CHARSET, etc.).
  -v, --verbose         control how much information is displayed. e.g., -v =
                        verbose, -vv = more verbose, -vvv = debug
  -q, --quiet           turn off all messages for quiet execution.
  -d DIFFTYPE, --difftype=DIFFTYPE
                        display differences in context format in one of the
                        following formats: [unified|context|differ|sql]
                        (default: unified).
  --changes-for=CHANGES_FOR
                        specify the server to show transformations to match
                        the other server. For example, to see the
                        transformation for transforming server1 to match
                        server2, use --changes-for=server1. Valid values are
                        'server1' or 'server2'. The default is 'server1'.
  --show-reverse        produce a transformation report containing the SQL
                        statements to transform the object definitions
                        specified in reverse. For example if --changes-for is
                        set to server1, also generate the transformation for
                        server2. Note: the reverse changes are annotated and
                        marked as comments.

以SQL输出的限制有：

  1. 对于分区表，如果分区表有差异，将对所有的改变生产 ALTER TABLE 语句，显示经过并省略分区差异。
  2. 事件重命名不支持。
  3. 不支持事务定义的条款。
  4. 不支持MySQL Cluster 的SQL扩展特性。

实例：

# mysqldiff --server1=instance_3306 --server2=instance_3308  ttlsa_com:ttlsa_com
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing `ttlsa_com` to `ttlsa_com`                             [PASS]
# Comparing `ttlsa_com`.`bbs_categories` to `ttlsa_com`.`bbs_categories`   [PASS]
# Comparing `ttlsa_com`.`bbs_comments` to `ttlsa_com`.`bbs_comments`   [PASS]
# Comparing `ttlsa_com`.`bbs_favorites` to `ttlsa_com`.`bbs_favorites`   [PASS]
# Comparing `ttlsa_com`.`bbs_forums` to `ttlsa_com`.`bbs_forums`   [PASS]
# Comparing `ttlsa_com`.`bbs_links` to `ttlsa_com`.`bbs_links`     [PASS]
# Comparing `ttlsa_com`.`bbs_notifications` to `ttlsa_com`.`bbs_notifications`   [PASS]
# Comparing `ttlsa_com`.`bbs_page` to `ttlsa_com`.`bbs_page`       [PASS]
# Comparing `ttlsa_com`.`bbs_settings` to `ttlsa_com`.`bbs_settings`   [PASS]
# Comparing `ttlsa_com`.`bbs_tags` to `ttlsa_com`.`bbs_tags`       [PASS]
# Comparing `ttlsa_com`.`bbs_tags_relation` to `ttlsa_com`.`bbs_tags_relation`   [PASS]
# Comparing `ttlsa_com`.`bbs_user_follow` to `ttlsa_com`.`bbs_user_follow`   [PASS]
# Comparing `ttlsa_com`.`bbs_user_groups` to `ttlsa_com`.`bbs_user_groups`   [PASS]
# Comparing `ttlsa_com`.`bbs_users` to `ttlsa_com`.`bbs_users`     [PASS]
# Comparing `ttlsa_com`.`data` to `ttlsa_com`.`data`               [PASS]
# Comparing `ttlsa_com`.`t_data` to `ttlsa_com`.`t_data`           [FAIL]
# Object definitions differ. (--changes-for=server1)
#

--- `ttlsa_com`.`t_data`
+++ `ttlsa_com`.`t_data`
@@ -4,4 +4,4 @@
   `count` int(11) DEFAULT NULL,
   PRIMARY KEY (`value`),
   KEY `id` (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
Compare failed. One or more differences found.
#  mysqldiff --server1=instance_3306 --server2=instance_3308  ttlsa_com:ttlsa_com --difftype=sql --changes-for=server1
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing `ttlsa_com` to `ttlsa_com`                             [PASS]
# Comparing `ttlsa_com`.`bbs_categories` to `ttlsa_com`.`bbs_categories`   [PASS]
# Comparing `ttlsa_com`.`bbs_comments` to `ttlsa_com`.`bbs_comments`   [PASS]
# Comparing `ttlsa_com`.`bbs_favorites` to `ttlsa_com`.`bbs_favorites`   [PASS]
# Comparing `ttlsa_com`.`bbs_forums` to `ttlsa_com`.`bbs_forums`   [PASS]
# Comparing `ttlsa_com`.`bbs_links` to `ttlsa_com`.`bbs_links`     [PASS]
# Comparing `ttlsa_com`.`bbs_notifications` to `ttlsa_com`.`bbs_notifications`   [PASS]
# Comparing `ttlsa_com`.`bbs_page` to `ttlsa_com`.`bbs_page`       [PASS]
# Comparing `ttlsa_com`.`bbs_settings` to `ttlsa_com`.`bbs_settings`   [PASS]
# Comparing `ttlsa_com`.`bbs_tags` to `ttlsa_com`.`bbs_tags`       [PASS]
# Comparing `ttlsa_com`.`bbs_tags_relation` to `ttlsa_com`.`bbs_tags_relation`   [PASS]
# Comparing `ttlsa_com`.`bbs_user_follow` to `ttlsa_com`.`bbs_user_follow`   [PASS]
# Comparing `ttlsa_com`.`bbs_user_groups` to `ttlsa_com`.`bbs_user_groups`   [PASS]
# Comparing `ttlsa_com`.`bbs_users` to `ttlsa_com`.`bbs_users`     [PASS]
# Comparing `ttlsa_com`.`data` to `ttlsa_com`.`data`               [PASS]
# Comparing `ttlsa_com`.`t_data` to `ttlsa_com`.`t_data`           [FAIL]
# Transformation for --changes-for=server1:
#

ALTER TABLE `ttlsa_com`.`t_data` 
  DROP INDEX id, 
  ADD INDEX id (id), 
ENGINE=MyISAM;

Compare failed. One or more differences found.
#  mysqldiff --server1=instance_3306 --server2=instance_3308  ttlsa_com:ttlsa_com --difftype=sql --changes-for=server2
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing `ttlsa_com` to `ttlsa_com`                             [PASS]
# Comparing `ttlsa_com`.`bbs_categories` to `ttlsa_com`.`bbs_categories`   [PASS]
# Comparing `ttlsa_com`.`bbs_comments` to `ttlsa_com`.`bbs_comments`   [PASS]
# Comparing `ttlsa_com`.`bbs_favorites` to `ttlsa_com`.`bbs_favorites`   [PASS]
# Comparing `ttlsa_com`.`bbs_forums` to `ttlsa_com`.`bbs_forums`   [PASS]
# Comparing `ttlsa_com`.`bbs_links` to `ttlsa_com`.`bbs_links`     [PASS]
# Comparing `ttlsa_com`.`bbs_notifications` to `ttlsa_com`.`bbs_notifications`   [PASS]
# Comparing `ttlsa_com`.`bbs_page` to `ttlsa_com`.`bbs_page`       [PASS]
# Comparing `ttlsa_com`.`bbs_settings` to `ttlsa_com`.`bbs_settings`   [PASS]
# Comparing `ttlsa_com`.`bbs_tags` to `ttlsa_com`.`bbs_tags`       [PASS]
# Comparing `ttlsa_com`.`bbs_tags_relation` to `ttlsa_com`.`bbs_tags_relation`   [PASS]
# Comparing `ttlsa_com`.`bbs_user_follow` to `ttlsa_com`.`bbs_user_follow`   [PASS]
# Comparing `ttlsa_com`.`bbs_user_groups` to `ttlsa_com`.`bbs_user_groups`   [PASS]
# Comparing `ttlsa_com`.`bbs_users` to `ttlsa_com`.`bbs_users`     [PASS]
# Comparing `ttlsa_com`.`data` to `ttlsa_com`.`data`               [PASS]
# Comparing `ttlsa_com`.`t_data` to `ttlsa_com`.`t_data`           [FAIL]
# Transformation for --changes-for=server2:
#

ALTER TABLE `ttlsa_com`.`t_data` 
  DROP INDEX id, 
  ADD INDEX id (id), 
ENGINE=InnoDB;

Compare failed. One or more differences found.
#  mysqldbcompare --server1=instance_3306 --server2=instance_3308  ttlsa_com:ttlsa_com  --show-reverse  --run-all-tests -vv
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Checking databases ttlsa_com on server1 and ttlsa_com on server2
#
Looking for object types table, view, trigger, procedure, function, and event.
Object types found common to both databases:
     FUNCTION : 0
      TRIGGER : 0
        TABLE : 15
        EVENT : 0
    PROCEDURE : 0
         VIEW : 0
#                                                   Defn    Row     Data   
# Type      Object Name                             Diff    Count   Check  
# ------------------------------------------------------------------------- 
# TABLE     bbs_categories                          pass    pass    -       
#           - Compare table checksum                                pass    
#           - Compare table checksum                                pass   

# Definition for object ttlsa_com.bbs_categories:
CREATE TABLE `bbs_categories` (
  `cid` smallint(5) NOT NULL AUTO_INCREMENT,
  `pid` smallint(5) NOT NULL DEFAULT '0',
  `cname` varchar(30) DEFAULT NULL COMMENT '分类名称',
  `content` varchar(255) DEFAULT NULL,
  `keywords` varchar(255) DEFAULT NULL,
  `ico` varchar(128) DEFAULT NULL,
  `master` varchar(100) NOT NULL,
  `permit` varchar(255) DEFAULT NULL,
  `listnum` mediumint(8) unsigned DEFAULT '0',
  `clevel` varchar(25) DEFAULT NULL,
  `cord` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`cid`,`pid`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

# Definition for object ttlsa_com.bbs_categories:
CREATE TABLE `bbs_categories` (
  `cid` smallint(5) NOT NULL AUTO_INCREMENT,
  `pid` smallint(5) NOT NULL DEFAULT '0',
  `cname` varchar(30) DEFAULT NULL COMMENT '分类名称',
  `content` varchar(255) DEFAULT NULL,
  `keywords` varchar(255) DEFAULT NULL,
  `ico` varchar(128) DEFAULT NULL,
  `master` varchar(100) NOT NULL,
  `permit` varchar(255) DEFAULT NULL,
  `listnum` mediumint(8) unsigned DEFAULT '0',
  `clevel` varchar(25) DEFAULT NULL,
  `cord` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`cid`,`pid`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

# TABLE     bbs_comments                            pass    pass    -       
#           - Compare table checksum                                pass    
#           - Compare table checksum                                pass   

# Definition for object ttlsa_com.bbs_comments:
CREATE TABLE `bbs_comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fid` int(11) NOT NULL DEFAULT '0',
  `uid` int(11) NOT NULL DEFAULT '0',
  `content` text,
  `replytime` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`,`fid`,`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=371 DEFAULT CHARSET=utf8

# Definition for object ttlsa_com.bbs_comments:
CREATE TABLE `bbs_comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fid` int(11) NOT NULL DEFAULT '0',
  `uid` int(11) NOT NULL DEFAULT '0',
  `content` text,
  `replytime` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`,`fid`,`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=371 DEFAULT CHARSET=utf8

# TABLE     bbs_favorites                           pass    pass    -       
#           - Compare table checksum                                pass    
#           - Compare table checksum                                pass   

# Definition for object ttlsa_com.bbs_favorites:
CREATE TABLE `bbs_favorites` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `uid` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `favorites` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `content` mediumtext NOT NULL,
  PRIMARY KEY (`id`,`uid`),
  KEY `uid` (`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

# Definition for object ttlsa_com.bbs_favorites:
CREATE TABLE `bbs_favorites` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `uid` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `favorites` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `content` mediumtext NOT NULL,
  PRIMARY KEY (`id`,`uid`),
  KEY `uid` (`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

# TABLE     bbs_forums                              pass    pass    -       
#           - Compare table checksum                                pass    
#           - Compare table checksum                                pass   

# Definition for object ttlsa_com.bbs_forums:
CREATE TABLE `bbs_forums` (
  `fid` int(11) NOT NULL AUTO_INCREMENT,
  `cid` smallint(5) NOT NULL DEFAULT '0',
  `uid` mediumint(8) NOT NULL DEFAULT '0',
  `ruid` mediumint(8) DEFAULT NULL,
  `title` varchar(128) DEFAULT NULL,
  `keywords` varchar(255) DEFAULT NULL,
  `content` text,
  `addtime` int(10) DEFAULT NULL,
  `updatetime` int(10) DEFAULT NULL,
  `lastreply` int(10) DEFAULT NULL,
  `views` int(10) DEFAULT '0',
  `comments` smallint(8) DEFAULT '0',
  `favorites` int(10) unsigned DEFAULT '0',
  `closecomment` tinyint(1) DEFAULT NULL,
  `is_top` tinyint(1) NOT NULL DEFAULT '0',
  `is_hidden` tinyint(1) NOT NULL DEFAULT '0',
  `ord` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`fid`,`cid`,`uid`),
  KEY `updatetime` (`updatetime`),
  KEY `ord` (`ord`)
) ENGINE=MyISAM AUTO_INCREMENT=94 DEFAULT CHARSET=utf8

# Definition for object ttlsa_com.bbs_forums:
CREATE TABLE `bbs_forums` (
  `fid` int(11) NOT NULL AUTO_INCREMENT,
  `cid` smallint(5) NOT NULL DEFAULT '0',
  `uid` mediumint(8) NOT NULL DEFAULT '0',
  `ruid` mediumint(8) DEFAULT NULL,
  `title` varchar(128) DEFAULT NULL,
  `keywords` varchar(255) DEFAULT NULL,
  `content` text,
  `addtime` int(10) DEFAULT NULL,
  `updatetime` int(10) DEFAULT NULL,
  `lastreply` int(10) DEFAULT NULL,
  `views` int(10) DEFAULT '0',
  `comments` smallint(8) DEFAULT '0',
  `favorites` int(10) unsigned DEFAULT '0',
  `closecomment` tinyint(1) DEFAULT NULL,
  `is_top` tinyint(1) NOT NULL DEFAULT '0',
  `is_hidden` tinyint(1) NOT NULL DEFAULT '0',
  `ord` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`fid`,`cid`,`uid`),
  KEY `updatetime` (`updatetime`),
  KEY `ord` (`ord`)
) ENGINE=MyISAM AUTO_INCREMENT=94 DEFAULT CHARSET=utf8

# TABLE     bbs_links                               pass    pass    -       
#           - Compare table checksum                                pass    
#           - Compare table checksum                                pass   

# Definition for object ttlsa_com.bbs_links:
CREATE TABLE `bbs_links` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `url` varchar(200) DEFAULT NULL,
  `logo` varchar(200) DEFAULT NULL,
  `is_hidden` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

# Definition for object ttlsa_com.bbs_links:
CREATE TABLE `bbs_links` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `url` varchar(200) DEFAULT NULL,
  `logo` varchar(200) DEFAULT NULL,
  `is_hidden` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

# TABLE     bbs_notifications                       pass    pass    -       
#           - Compare table checksum                                pass    
#           - Compare table checksum                                pass   

# Definition for object ttlsa_com.bbs_notifications:
CREATE TABLE `bbs_notifications` (
  `nid` int(11) NOT NULL AUTO_INCREMENT,
  `fid` int(11) DEFAULT NULL,
  `suid` int(11) DEFAULT NULL,
  `nuid` int(11) NOT NULL DEFAULT '0',
  `ntype` tinyint(1) DEFAULT NULL,
  `ntime` int(10) DEFAULT NULL,
  PRIMARY KEY (`nid`,`nuid`)
) ENGINE=MyISAM AUTO_INCREMENT=444 DEFAULT CHARSET=utf8

# Definition for object ttlsa_com.bbs_notifications:
CREATE TABLE `bbs_notifications` (
  `nid` int(11) NOT NULL AUTO_INCREMENT,
  `fid` int(11) DEFAULT NULL,
  `suid` int(11) DEFAULT NULL,
  `nuid` int(11) NOT NULL DEFAULT '0',
  `ntype` tinyint(1) DEFAULT NULL,
  `ntime` int(10) DEFAULT NULL,
  PRIMARY KEY (`nid`,`nuid`)
) ENGINE=MyISAM AUTO_INCREMENT=444 DEFAULT CHARSET=utf8

# TABLE     bbs_page                                pass    pass    -       
#           - Compare table checksum                                pass    
#           - Compare table checksum                                pass   

# Definition for object ttlsa_com.bbs_page:
CREATE TABLE `bbs_page` (
  `pid` tinyint(6) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) DEFAULT NULL,
  `content` text,
  `go_url` varchar(100) DEFAULT NULL,
  `add_time` int(10) DEFAULT NULL,
  `is_hidden` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`pid`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

# Definition for object ttlsa_com.bbs_page:
CREATE TABLE `bbs_page` (
  `pid` tinyint(6) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) DEFAULT NULL,
  `content` text,
  `go_url` varchar(100) DEFAULT NULL,
  `add_time` int(10) DEFAULT NULL,
  `is_hidden` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`pid`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

# TABLE     bbs_settings                            pass    pass    -       
#           - Compare table checksum                                pass    
#           - Compare table checksum                                pass   

# Definition for object ttlsa_com.bbs_settings:
CREATE TABLE `bbs_settings` (
  `id` tinyint(5) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `value` text NOT NULL,
  `type` tinyint(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`title`,`type`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8

# Definition for object ttlsa_com.bbs_settings:
CREATE TABLE `bbs_settings` (
  `id` tinyint(5) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `value` text NOT NULL,
  `type` tinyint(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`title`,`type`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8

# TABLE     bbs_tags                                pass    pass    -       
#           - Compare table checksum                                pass    
#           - Compare table checksum                                pass   

# Definition for object ttlsa_com.bbs_tags:
CREATE TABLE `bbs_tags` (
  `tag_id` int(10) NOT NULL AUTO_INCREMENT,
  `tag_title` varchar(30) NOT NULL,
  `forums` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`tag_id`),
  UNIQUE KEY `tag_title` (`tag_title`)
) ENGINE=MyISAM AUTO_INCREMENT=185 DEFAULT CHARSET=utf8

# Definition for object ttlsa_com.bbs_tags:
CREATE TABLE `bbs_tags` (
  `tag_id` int(10) NOT NULL AUTO_INCREMENT,
  `tag_title` varchar(30) NOT NULL,
  `forums` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`tag_id`),
  UNIQUE KEY `tag_title` (`tag_title`)
) ENGINE=MyISAM AUTO_INCREMENT=185 DEFAULT CHARSET=utf8

# TABLE     bbs_tags_relation                       pass    pass    -       
#           - Compare table checksum                                pass    
#           - Compare table checksum                                pass   

# Definition for object ttlsa_com.bbs_tags_relation:
CREATE TABLE `bbs_tags_relation` (
  `tag_id` int(10) NOT NULL DEFAULT '0',
  `fid` int(10) DEFAULT NULL,
  KEY `tag_id` (`tag_id`),
  KEY `fid` (`fid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

# Definition for object ttlsa_com.bbs_tags_relation:
CREATE TABLE `bbs_tags_relation` (
  `tag_id` int(10) NOT NULL DEFAULT '0',
  `fid` int(10) DEFAULT NULL,
  KEY `tag_id` (`tag_id`),
  KEY `fid` (`fid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

# TABLE     bbs_user_follow                         pass    pass    -       
#           - Compare table checksum                                pass    
#           - Compare table checksum                                pass   

# Definition for object ttlsa_com.bbs_user_follow:
CREATE TABLE `bbs_user_follow` (
  `follow_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(10) unsigned NOT NULL DEFAULT '0',
  `follow_uid` int(10) unsigned NOT NULL DEFAULT '0',
  `addtime` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`follow_id`,`uid`,`follow_uid`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

# Definition for object ttlsa_com.bbs_user_follow:
CREATE TABLE `bbs_user_follow` (
  `follow_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(10) unsigned NOT NULL DEFAULT '0',
  `follow_uid` int(10) unsigned NOT NULL DEFAULT '0',
  `addtime` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`follow_id`,`uid`,`follow_uid`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

# TABLE     bbs_user_groups                         pass    pass    -       
#           - Compare table checksum                                pass    
#           - Compare table checksum                                pass   

# Definition for object ttlsa_com.bbs_user_groups:
CREATE TABLE `bbs_user_groups` (
  `gid` int(11) NOT NULL AUTO_INCREMENT,
  `group_type` tinyint(3) NOT NULL DEFAULT '0',
  `group_name` varchar(50) DEFAULT NULL,
  `usernum` int(11) NOT NULL,
  PRIMARY KEY (`gid`,`group_type`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

# Definition for object ttlsa_com.bbs_user_groups:
CREATE TABLE `bbs_user_groups` (
  `gid` int(11) NOT NULL AUTO_INCREMENT,
  `group_type` tinyint(3) NOT NULL DEFAULT '0',
  `group_name` varchar(50) DEFAULT NULL,
  `usernum` int(11) NOT NULL,
  PRIMARY KEY (`gid`,`group_type`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

# TABLE     bbs_users                               pass    pass    -       
#           - Compare table checksum                                pass    
#           - Compare table checksum                                pass   

# Definition for object ttlsa_com.bbs_users:
CREATE TABLE `bbs_users` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `password` char(32) DEFAULT NULL,
  `openid` char(32) NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  `avatar` varchar(100) DEFAULT NULL,
  `homepage` varchar(50) DEFAULT NULL,
  `money` int(11) DEFAULT '100',
  `signature` text,
  `forums` int(11) DEFAULT '0',
  `replies` int(11) DEFAULT '0',
  `notices` smallint(5) DEFAULT '0',
  `follows` int(11) NOT NULL DEFAULT '0',
  `regtime` int(10) DEFAULT NULL,
  `lastlogin` int(10) DEFAULT NULL,
  `lastpost` int(10) DEFAULT NULL,
  `qq` varchar(20) DEFAULT NULL,
  `group_type` tinyint(3) NOT NULL DEFAULT '0',
  `gid` tinyint(3) NOT NULL DEFAULT '3',
  `ip` char(15) DEFAULT NULL,
  `location` varchar(128) DEFAULT NULL,
  `token` varchar(40) DEFAULT NULL,
  `introduction` text,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`uid`,`group_type`)
) ENGINE=MyISAM AUTO_INCREMENT=109 DEFAULT CHARSET=utf8

# Definition for object ttlsa_com.bbs_users:
CREATE TABLE `bbs_users` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `password` char(32) DEFAULT NULL,
  `openid` char(32) NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  `avatar` varchar(100) DEFAULT NULL,
  `homepage` varchar(50) DEFAULT NULL,
  `money` int(11) DEFAULT '100',
  `signature` text,
  `forums` int(11) DEFAULT '0',
  `replies` int(11) DEFAULT '0',
  `notices` smallint(5) DEFAULT '0',
  `follows` int(11) NOT NULL DEFAULT '0',
  `regtime` int(10) DEFAULT NULL,
  `lastlogin` int(10) DEFAULT NULL,
  `lastpost` int(10) DEFAULT NULL,
  `qq` varchar(20) DEFAULT NULL,
  `group_type` tinyint(3) NOT NULL DEFAULT '0',
  `gid` tinyint(3) NOT NULL DEFAULT '3',
  `ip` char(15) DEFAULT NULL,
  `location` varchar(128) DEFAULT NULL,
  `token` varchar(40) DEFAULT NULL,
  `introduction` text,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`uid`,`group_type`)
) ENGINE=MyISAM AUTO_INCREMENT=109 DEFAULT CHARSET=utf8

# TABLE     data                                    pass    FAIL    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  FAIL    
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  FAIL   

# Definition for object ttlsa_com.data:
CREATE TABLE `data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` char(30) NOT NULL DEFAULT '',
  `count` int(11) DEFAULT NULL,
  PRIMARY KEY (`value`),
  KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1

# Definition for object ttlsa_com.data:
CREATE TABLE `data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` char(30) NOT NULL DEFAULT '',
  `count` int(11) DEFAULT NULL,
  PRIMARY KEY (`value`),
  KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1

#
# Row counts are not the same among `ttlsa_com`.`data` and `ttlsa_com`.`data`.
#

# Rows in `ttlsa_com`.`data` not in `ttlsa_com`.`data`
+-----+------------+--------+
| id  | value      | count  |
+-----+------------+--------+
| 6   | value-005  | 1      |
| 5   | value-004  | 1      |
| 3   | value-002  | 1      |
| 4   | value-003  | 1      |
| 1   | value-000  | 1      |
| 2   | value-001  | 1      |
+-----+------------+--------+

# 
# Rows in `ttlsa_com`.`data` not in `ttlsa_com`.`data`
# +-----+------------+--------+
# | id  | value      | count  |
# +-----+------------+--------+
# | 6   | value-005  | 1      |
# | 5   | value-004  | 1      |
# | 3   | value-002  | 1      |
# | 4   | value-003  | 1      |
# | 1   | value-000  | 1      |
# | 2   | value-001  | 1      |
# +-----+------------+--------+
#


# TABLE     t_data                                  FAIL    pass    -       
#           - Compare table checksum                                pass    
#           - Compare table checksum                                pass   

# Definition for object ttlsa_com.t_data:
CREATE TABLE `t_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` char(30) NOT NULL DEFAULT '',
  `count` int(11) DEFAULT NULL,
  PRIMARY KEY (`value`),
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

# Definition for object ttlsa_com.t_data:
CREATE TABLE `t_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` char(30) NOT NULL DEFAULT '',
  `count` int(11) DEFAULT NULL,
  PRIMARY KEY (`value`),
  KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

#
# Object definitions differ. (--changes-for=server1)
#

--- `ttlsa_com`.`t_data`
+++ `ttlsa_com`.`t_data`
@@ -4,4 +4,4 @@
   `count` int(11) DEFAULT NULL,
   PRIMARY KEY (`value`),
   KEY `id` (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
+) ENGINE=MyISAM DEFAULT CHARSET=latin1

#
# Definition diff for reverse changes (--changes-for=server2):
#
# --- `ttlsa_com`.`t_data`
# +++ `ttlsa_com`.`t_data`
# @@ -4,4 +4,4 @@
#    `count` int(11) DEFAULT NULL,
#    PRIMARY KEY (`value`),
#    KEY `id` (`id`)
# -) ENGINE=MyISAM DEFAULT CHARSET=latin1
# +) ENGINE=InnoDB DEFAULT CHARSET=latin1
#



# Database consistency check failed.
#
# ...done

权限：

对两者对象要有SELECT 权限，同时，还需要对mysql数据库有SELECT权限。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldiff/ 文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldiff/

weinxin
我的微信
微信公众号
扫一扫关注运维生存时间公众号，获取最新技术文章~
默北
  • 本文由 发表于 03/02/2015 01:00:04
  • 转载请务必保留本文链接：https://www.ttlsa.com/mysql/mysql-utilities-mysqldiff/
  • MySQL Utilities
  • mysqldbcompare
  • mysqldiff
评论  1  访客  1
    • seanlook
      seanlook 1
      1F

      mysqldiff 不能在对象不存在时，生产create语句，也不能单独避免比较auto_increment。
      这个分支添加了这些特性 https://github.com/seanlook/mysql-utilities/commits/master

    评论已关闭！