MySQL - Identify Differences Among Databases

a_mysqldiff.md

MySQL - Identify Differences Among Databases

Setup

• Visual C++ Redistributable Packages for Visual Studio 2013

• https://www.microsoft.com/en-US/download/details.aspx?id=40784

• MySQL Utilities - http://dev.mysql.com/downloads/utilities/

mysqldiff

mysqldiff all tables in database: https://dev.mysql.com/doc/mysql-utilities/1.5/en/mysqldiff.html

Example:

mysqldiff --server1=root@localhost --server2=root@localhost --difftype=sql --changes-for=server2 --force test:test2

mysqldbcompare

https://dev.mysql.com/doc/mysql-utilities/1.5/en/mysqldbcompare.html

Example:

mysqldbcompare --server1=root@localhost --server2=root@localhost --difftype=sql 
 --changes-for=server2 --skip-data-check --run-all-tests 
 --skip-checksum-table --skip-data-check --skip-row-count test:test2

Issue

There is a "Bug" or missing feature to disable the export of AUTO_INCREMENT.

• https://bugs.mysql.com/bug.php?id=79296
• https://bugs.mysql.com/bug.php?id=69669

mysqldbcompare_diff.sql

# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Checking databases test on server1 and test2 on server2
#
# Transformation for --changes-for=server2:
#

ALTER DATABASE test2 CHARACTER SET latin1 COLLATE = latin1_swedish_ci;


#                                                   Defn    Row     Data   
# Type      Object Name                             Diff    Count   Check  
# ------------------------------------------------------------------------- 
# TABLE     meta                                    FAIL    pass    SKIP    
#
# Transformation for --changes-for=server2:
#

ALTER TABLE `test2`.`meta` 
  DROP FOREIGN KEY meta_ibfk_1, 
ADD CONSTRAINT qqqqqq2 FOREIGN KEY(meta_key) REFERENCES `test`.`user`(username), 
ADD CONSTRAINT fk_xxx FOREIGN KEY(meta_key) REFERENCES `test`.`user`(username);


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

ALTER TABLE `test2`.`newtable` 
  DROP FOREIGN KEY newtable_ibfk_1, 
ADD CONSTRAINT fk_id FOREIGN KEY(id) REFERENCES `test`.`user`(id);


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

ALTER TABLE `test2`.`newtable2` 
  ADD UNIQUE INDEX id (id);


# TABLE     phinxlog                                pass    pass    SKIP    
# TABLE     test                                    FAIL    FAIL    SKIP    
#
# Transformation for --changes-for=server2:
#

ALTER TABLE `test2`.`test` 
  DROP PRIMARY KEY, 
  DROP COLUMN deleted, 
  ADD PRIMARY KEY(`id`), 
  ADD COLUMN deletedaaaaa tinyint(6) unsigned NOT NULL DEFAULT '1' COMMENT '5' AFTER email;

# Row counts are not the same among `test`.`test` and `test2`.`test`.
#

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

ALTER TABLE `test2`.`user` 
  DROP INDEX updated_user_id, 
  DROP INDEX created_user_id, 
  DROP INDEX username, 
  DROP PRIMARY KEY, 
  DROP INDEX deleted_user_id, 
  ADD PRIMARY KEY(`id`), 
  ADD UNIQUE INDEX username (username), 
  ADD INDEX created_user_id (created_user_id), 
  ADD INDEX updated_user_id (updated_user_id), 
  ADD INDEX deleted_user_id (deleted_user_id), 
AUTO_INCREMENT=17;

# Row counts are not the same among `test`.`user` and `test2`.`user`.
#


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

mysqldiff_diff.sql

# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing `test` to `test2`                                      [FAIL]
# Transformation for --changes-for=server2:
#

ALTER DATABASE test2 CHARACTER SET latin1 COLLATE = latin1_swedish_ci;

# Comparing `test`.`meta` to `test2`.`meta`                        [FAIL]
# Transformation for --changes-for=server2:
#

ALTER TABLE `test2`.`meta` 
  DROP FOREIGN KEY meta_ibfk_1, 
ADD CONSTRAINT qqqqqq2 FOREIGN KEY(meta_key) REFERENCES `test`.`user`(username), 
ADD CONSTRAINT fk_xxx FOREIGN KEY(meta_key) REFERENCES `test`.`user`(username);

# Comparing `test`.`newtable` to `test2`.`newtable`                [FAIL]
# Transformation for --changes-for=server2:
#

ALTER TABLE `test2`.`newtable` 
  DROP FOREIGN KEY newtable_ibfk_1, 
ADD CONSTRAINT fk_id FOREIGN KEY(id) REFERENCES `test`.`user`(id);

# Comparing `test`.`newtable2` to `test2`.`newtable2`              [FAIL]
# Transformation for --changes-for=server2:
#

ALTER TABLE `test2`.`newtable2` 
  ADD UNIQUE INDEX id (id);

# Comparing `test`.`phinxlog` to `test2`.`phinxlog`                [PASS]
# Comparing `test`.`test` to `test2`.`test`                        [FAIL]
# Transformation for --changes-for=server2:
#

ALTER TABLE `test2`.`test` 
  DROP PRIMARY KEY, 
  DROP COLUMN deleted, 
  ADD PRIMARY KEY(`id`), 
  ADD COLUMN deletedaaaaa tinyint(6) unsigned NOT NULL DEFAULT '1' COMMENT '5' AFTER email;

# Comparing `test`.`user` to `test2`.`user`                        [FAIL]
# Transformation for --changes-for=server2:
#

ALTER TABLE `test2`.`user` 
  DROP INDEX updated_user_id, 
  DROP INDEX created_user_id, 
  DROP INDEX username, 
  DROP PRIMARY KEY, 
  DROP INDEX deleted_user_id, 
  ADD PRIMARY KEY(`id`), 
  ADD UNIQUE INDEX username (username), 
  ADD INDEX created_user_id (created_user_id), 
  ADD INDEX updated_user_id (updated_user_id), 
  ADD INDEX deleted_user_id (deleted_user_id), 
AUTO_INCREMENT=17;

Compare failed. One or more differences found.