Can mysqldbcompare provide a sql patch?

Question

What I want is a way to compare two versions of the same database and generate a sql file that updates DatabaseOld to DatabaseNew. Mysqldbcompare sounds like it might do that, but it is not clear to me how.

Has anyone used mysqldbcompare for a usecase like this?

I have seen some attempts to diff dumps (eg Compare two MySQL databases) and such, but I'm looking for something a little more robust and reliable than that.

you can use the percona tool pt-table-sync. That can synchrince two table or Schema and that can find out what to do and only copy the missing row or delete or update the other — Bernd Buffen, Jan 26 '16 at 20:09
Interesting tool. Am I right in thinking that it only operates on databases with an existing master-slave relationship? That's not the case for me - I need to migrate data from a staging server to a production server. — doub1ejack, Jan 26 '16 at 20:28
no, you must not have a replication. this tool synced table / databases. the only thing ist that the strcture of the tables must be the same — Bernd Buffen, Jan 26 '16 at 20:30

score 0 · Answer 1 · answered Sep 21 '18 at 21:03

I see you never got a true answer so maybe this will help someone that is searching. mysqldbcompare will at least come close to what you are after. The issue I note so far is it will let you know tables are missing but not provide the CREATE statements. Another potential issue may be altering AUTO_INCREMENT fields. Here is the statement I am using to get just the schema differences (I don't care about data).

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

This will provide output like this:

# 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;
#

Can mysqldbcompare provide a sql patch?

1 Answers1