我曾经使用亚当·斯皮尔斯（Adam Spiers）的mysqldiff工具，但是多年来没有对其进行维护，因此在新服务器上，我正在尝试使用包含类似工具的mysql-utilities软件包。但是，即使我比较两个具有完全相同的架构的数据库，它们的名称也不同（du！）这一事实足以使该工具说比较失败（这似乎是发现差异的强有力的词汇，但是我想那是怎么运行的）。这是输出：
[root](22:05:48)[~]$ mysqldiff --server1=root:mypassword@localhost kizuna_master:kizuna_misa
# server1 on localhost: ... connected.
# Comparing `kizuna_master` to `kizuna_misa` [FAIL]
# Object definitions differ. (--changes-for=server1)
#
--- `kizuna_master`
+++ `kizuna_misa`
@@ -1 +1 @@
-CREATE DATABASE `kizuna_master` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */
+CREATE DATABASE `kizuna_misa` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */
Compare failed. One or more differences found.
在这种情况下，如果在同一服务器上比较两个数据库，则总是会失败-这是没有意义的。难道我做错了什么？
编辑：根据Solarflare的建议，我也尝试了mysqldbcompare：
mysqldbcompare --skip-row-count --skip-data-check --run-all-tests --server1=root:mypassword@localhost kizuna_master:kizuna_dev但结果与mysqldiff相同，但比较复杂。使用这两个命令，不仅在
CREATE DATABASE定义上失败，而且在所有具有自动递增键字段的表上也失败！令人难过的是-如果我说我不想比较数据（--skip-data-check），为什么它认为我会因为AUTO_INCREMENT而失败？这是上述mysqldbcompare调用的输出的第一部分：# server1 on localhost: ... connected.
# Checking databases kizuna_master and kizuna_dev on server1
#
# Object definitions differ. (--changes-for=server1)
#
--- `kizuna_master`
+++ `kizuna_dev`
@@ -1 +1 @@
-CREATE DATABASE `kizuna_master` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */
+CREATE DATABASE `kizuna_dev` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */
# Defn Row Data
# Type Object Name Diff Count Check
# -------------------------------------------------------------------------
# TABLE action FAIL SKIP SKIP
#
# Object definitions differ. (--changes-for=server1)
#
--- `kizuna_master`.`action`
+++ `kizuna_dev`.`action`
@@ -9,4 +9,4 @@
KEY `PersonID` (`PersonID`),
KEY `ContactTypeID` (`ActionTypeID`),
CONSTRAINT `action_ibfk_1` FOREIGN KEY (`ActionTypeID`) REFERENCES `actiontype` (`ActionTypeID`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
+) ENGINE=InnoDB AUTO_INCREMENT=1953 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
# TABLE actiontype FAIL SKIP SKIP
#
# Object definitions differ. (--changes-for=server1)
#
--- `kizuna_master`.`actiontype`
+++ `kizuna_dev`.`actiontype`
@@ -4,4 +4,4 @@
`BGColor` char(6) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'FFFFFF',
`Template` text COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`ActionTypeID`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
+) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
# TABLE addrprint pass SKIP SKIP
# TABLE attendance pass SKIP SKIP
# TABLE category FAIL SKIP SKIP
#
# Object definitions differ. (--changes-for=server1)
#
--- `kizuna_master`.`category`
+++ `kizuna_dev`.`category`
@@ -4,4 +4,4 @@
`UseFor` enum('OP','P','O') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'OP' COMMENT 'Whether the category can be used for people, orgs,
or both',
PRIMARY KEY (`CategoryID`),
KEY `Category` (`Category`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
+) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
等等...整个过程就是这样。
编辑2：添加
--skip-table-options使它对自动增量保持安静，尽管我不知道该选项将忽略什么其他差异以及我是否在乎。但是，尽管它仍然报告数据库名称有所不同，但是它不再使用“ fail”一词，因此我认为这是一个改进。这是mysqldbcompare --skip-row-count --skip-data-check --run-all-tests --skip-table-options --difftype=differ --server1=root:mypassword@localhost kizuna_master:kizuna_dev的输出：# server1 on localhost: ... connected.
# Checking databases kizuna_master and kizuna_dev on server1
#
# Object definitions differ. (--changes-for=server1)
#
- CREATE DATABASE `kizuna_master` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */
? ^^^^ ^
+ CREATE DATABASE `kizuna_dev` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */
? ^ ^
# Defn Row Data
# Type Object Name Diff Count Check
# -------------------------------------------------------------------------
# TABLE action pass SKIP SKIP
# TABLE actiontype pass SKIP SKIP
# TABLE addrprint pass SKIP SKIP
# TABLE attendance pass SKIP SKIP
# TABLE category pass SKIP SKIP
# TABLE config pass SKIP SKIP
# TABLE custom pass SKIP SKIP
# TABLE donation pass SKIP SKIP
# TABLE donationtype pass SKIP SKIP
# TABLE event pass SKIP SKIP
# TABLE household pass SKIP SKIP
# TABLE labelprint pass SKIP SKIP
# TABLE loginlog pass SKIP SKIP
# TABLE output pass SKIP SKIP
# TABLE outputset pass SKIP SKIP
# TABLE percat pass SKIP SKIP
# TABLE perorg pass SKIP SKIP
# TABLE person pass SKIP SKIP
# TABLE photoprint pass SKIP SKIP
# TABLE pledge pass SKIP SKIP
# TABLE postalcode pass SKIP SKIP
# TABLE preselect pass SKIP SKIP
# TABLE upload pass SKIP SKIP
# TABLE uploadtype pass SKIP SKIP
# TABLE user pass SKIP SKIP
# Databases are consistent given skip options specified.
#
# ...done
所以我猜在我的bash脚本中，我可以将其传送到grep并检查单词“ fail”。但是真是一团糟-旧的
mysqldiff工具是如此简单明了。另一个问题是关于工具版本。
mysqldiff --help说：“ MySQL实用程序mysqldiff版本1.3.6（MySQL Workbench发行版5.2.47的一部分）”。我最初直接下载了最新版本，但没有安装-这是yum可以在CentOS7上提供的功能。如果重要的话，我的数据库是MariaDB 10.2.8。 （当我开始讨论时是10.2.4，但此后我进行了更新。） 最佳答案
我遇到了同样的问题，但找到了解决方案。 mysqldiff和mysqldbcompare都来自mysql-utilities，因此共享相同的代码库。
就我而言，我需要在结构相同但名称不同的数据库上运行laravel迁移。当我运行mysqldbcompare --version时，它显示
MySQL实用程序mysqldbcompare版本1.6.1
许可证类型：GPLv2
现在，就像您一样，我尝试使用mysqldbcompare --server1={server1} --server2={server1} --difftype=sql --skip-row-count --skip-data-check --run-all-tests --skip-table-options --changes-for=server2 {db1}:{db2}
在股票ubuntu 16.04上，mysqldbcompare引发相同的错误，抱怨数据库名称不同：
# WARNING: Using a password on the command line interface can be insecure.
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Checking databases backend_21 on server1 and backend_1 on server2
#
# WARNING: Cannot generate SQL statements for these objects.
# Check the difference output for other discrepencies.
--- `backend_21`
+++ `backend_1`
@@ -1 +1 @@
-CREATE DATABASE `backend_21` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */
+CREATE DATABASE `backend_1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */
# WARNING: Could not generate SQL statements for differences between `backend_21` and `backend_1`. No changes required or not supported difference.
# WARNING: Cannot generate SQL statements for these objects.
# Check the difference output for other discrepencies.
--- `backend_21`
+++ `backend_1`
@@ -1 +1 @@
-CREATE DATABASE `backend_21` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */
+CREATE DATABASE `backend_1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */
# Defn Row Data
# Type Object Name Diff Count Check
# -------------------------------------------------------------------------
# TABLE bot_bus_connections pass SKIP SKIP
...
# TABLE user_settings pass SKIP SKIP
# Databases are consistent given skip options specified.
#
# ...done
经过调查后，我发现此行为是一个已报告的错误，并已在1.6.4版本中修复：https://bugs.mysql.com/bug.php?id=77058。
因此，在手动升级这些python文件之后，我现在运行
mysqldbcompare --versionMySQL实用程序mysqldbcompare版本1.6.4
许可证类型：GPLv2
结果与预期的一样：
# WARNING: Using a password on the command line interface can be insecure.
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Checking databases backend_21 on server1 and backend_1 on server2
#
# Defn Row Data
# Type Object Name Diff Count Check
# -------------------------------------------------------------------------
# TABLE bot_bus_connections pass SKIP SKIP
...
# TABLE user_settings pass SKIP SKIP
# Databases are consistent given skip options specified.
#
# ...done
但是，仍然有一件令人烦恼的事情，它被忽略
CREATE TABLE语句。因此，如果database1（我的模板数据库）中有新表，它只会发出如下警告：# WARNING: Objects in server1.backend_1 but not in server1.backend_2:
# TABLE: statistic_chats
# TABLE: localizations
# TABLE: statistic_customers
自2015年以来也已经有报道，但到目前为止mysql没有任何反应。我有点肮脏的解决方案是
搜索该字符串并
运行伪造的
CREATE TABLE语句，因此表存在并且重新运行迁移，以使用实际结构修复假表
希望能有所帮助。
干杯