diff - mysqldbcompare可以提供sql补丁吗？

Question

我想要的是一种比较同一数据库的两个版本并生成将 DatabaseOld 更新为 DatabaseNew 的 sql 文件的方法。 Mysqldbcompare听起来可能可以做到这一点，但我不清楚如何做到。

有人用过 mysqldbcompare 来完成这样的用例吗？

我已经看到了一些比较转储的尝试(例如 Compare two MySQL databases )等，但我正在寻找比这更强大和可靠的东西。

Answer 1

我发现您从未得到真正的答案，所以也许这会对正在搜索的人有所帮助。 mysqldbcompare 至少会接近您所追求的。到目前为止我注意到的问题是它会让您知道表丢失，但不提供 CREATE 语句。另一个潜在的问题可能是更改 AUTO_INCRMENT 字段。这是我用来获取架构差异的语句(我不关心数据)。

mysqldbcompare --server1=root:password@localhost test:prod --changes-for=server2 --difftype=sql --show-reverse --run-all-tests --skip-row-count --skip-data-check > C:\Temp\DBCompare.sql

这将提供如下输出:

# WARNING: Using a password on the command line interface can be insecure.
# server1 on localhost: ... connected.
# Checking databases test and prod on server1
#
# Transformation for --changes-for=server2:
#

ALTER DATABASE prod CHARACTER SET latin1 COLLATE = latin1_swedish_ci;

#
# Transformation for reverse changes (--changes-for=server1):
#
# ALTER DATABASE test CHARACTER SET utf8 COLLATE = utf8_general_ci;
#


# WARNING: Objects in server1.test but not in server1.prod:
#    PROCEDURE: my_test_noout
#        TABLE: test
#    PROCEDURE: my_test
#        TABLE: customer2
#        TABLE: ordgdnames
#
#                                                   Defn    Row     Data   
# Type      Object Name                             Diff    Count   Check  
# ------------------------------------------------------------------------- 
# TABLE     arahst                                  FAIL    SKIP    SKIP    
#
# Transformation for --changes-for=server2:
#

ALTER TABLE `prod`.`arahst` 
  ADD INDEX idx_arahst_CustId (CustId);

#
# Transformation for reverse changes (--changes-for=server1):
#
# ALTER TABLE `test`.`arahst` 
#   DROP INDEX idx_arahst_CustId;
#


# TABLE     aropen                                  pass    SKIP    SKIP    
# TABLE     brkitem                                 FAIL    SKIP    SKIP    
#
# Transformation for --changes-for=server2:
#

ALTER TABLE `prod`.`brkitem` 
  DROP INDEX ItemId, 
  DROP PRIMARY KEY, 
  ADD UNIQUE INDEX idx_brkitem_ItemId (ItemId);

#
# Transformation for reverse changes (--changes-for=server1):
#
# ALTER TABLE `test`.`brkitem` 
#   DROP INDEX idx_brkitem_ItemId, 
#   ADD PRIMARY KEY(`ItemId`), 
#   ADD UNIQUE INDEX ItemId (ItemId);
#


# TABLE     category                                FAIL    SKIP    SKIP    
#
# Transformation for --changes-for=server2:
#

ALTER TABLE `prod`.`category` 
  DROP INDEX GrpId, 
  DROP INDEX CatId, 
  ADD INDEX idx_category_GrpId (GrpId), 
  ADD UNIQUE INDEX idx_category_CatId (CatId);

#
# Transformation for reverse changes (--changes-for=server1):
#
# ALTER TABLE `test`.`category` 
#   DROP INDEX idx_category_GrpId, 
#   DROP INDEX idx_category_CatId, 
#   ADD INDEX GrpId (GrpId), 
#   ADD INDEX CatId (CatId);
#


# TABLE     cusord                                  pass    SKIP    SKIP    
# TABLE     customer                                FAIL    SKIP    SKIP    
#
# Transformation for --changes-for=server2:
#

ALTER TABLE `prod`.`customer` 
  DROP INDEX ID, 
  DROP INDEX CustId, 
  ADD PRIMARY KEY(`ID`), 
AUTO_INCREMENT=2200037;

#
# Transformation for reverse changes (--changes-for=server1):
#
# ALTER TABLE `test`.`customer` 
#   DROP PRIMARY KEY, 
#   ADD INDEX ID (ID), 
#   ADD UNIQUE INDEX CustId (CustId), 
# AUTO_INCREMENT=11048819;
#