Sunday, August 23, 2015

Comparing MySQL databases with mysqldbcompare

If  you need to check the differences between two MySQL databases, you can use the mysqldbcompare utility. This utility is part of MySQL utilities and it is very useful when you want to check if databases from different environments (dev, testing, stage, production) are consistent. It compares the objects and data from two databases to find differences between them. The databases can be located on the same host or different hosts. This utility identifies objects having different definitions in the two databases and presents them in a diff-style format.

To use mysqldbcompare, you will need to install MySQL Utilities and MySQL Connector/Python. You can find them here:
I use mysqldbcompare to compare the database objects to identify new tables/columns or anything that has been created or dropped. You can also use it to compare data itself, which would be useful to compare a replica or backup database with its source.

The usage is very simple. I created two identical databases in my localhost using the Chinook Database named Chinook and Chinook2.

The mysqldbcompare syntax is very simple. You use --server1 and server2 to specify the hosts of the databases and then the database names between colon, for example Chinook:Chinook2. You also can use --run-all-tests to avoid stopping at the first object different, and continue to process for all db objects.

mysqldbcompare --server1=root:password@localhost:3306 --server2=root:password@localhost:3306 Chinook:Chinook2 --run-all-tests

The output is:

# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Checking databases Chinook on server1 and Chinook2 on server2
#
# Object definitions differ. (--changes-for=server1)
#

--- `Chinook`
+++ `Chinook2`
@@ -1 +1 @@
-CREATE DATABASE `Chinook` /*!40100 DEFAULT CHARACTER SET latin1 */
+CREATE DATABASE `Chinook2` /*!40100 DEFAULT CHARACTER SET latin1 */

#                                                   Defn    Row     Data   
# Type      Object Name                             Diff    Count   Check  
# ------------------------------------------------------------------------- 
# TABLE     Album                                   pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass    
# TABLE     Artist                                  pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass    
# TABLE     Customer                                pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass    
# TABLE     Employee                                pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass    
# TABLE     Genre                                   pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass    
# TABLE     Invoice                                 pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass    
# TABLE     InvoiceLine                             pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass    
# TABLE     MediaType                               pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass    
# TABLE     Playlist                                pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass    
# TABLE     PlaylistTrack                           pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass    
# TABLE     Track                                   pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass   

# Databases are consistent.
#
# ...done

Note that object definitions, row count and row content match. It shows that table checksum are different, but my guess is that they are not the exactly replica since I created Chinook2 by running the same sql script again.

You can use the option --skip-data-check if you only want to compare objects definition and not data. After I made some changes in Chinook2 (drop and create table and columns), I run mysqldbcompare with --skip-data-check:

mysqldbcompare --server1=root:password@localhost:3306 --server2=root:password@localhost:3306 Chinook:Chinook2 --run-all-tests --skip-data-check

The output is:

# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Checking databases Chinook on server1 and Chinook2 on server2
#
# Object definitions differ. (--changes-for=server1)
#

--- `Chinook`
+++ `Chinook2`
@@ -1 +1 @@
-CREATE DATABASE `Chinook` /*!40100 DEFAULT CHARACTER SET latin1 */
+CREATE DATABASE `Chinook2` /*!40100 DEFAULT CHARACTER SET latin1 */

# WARNING: Objects in server1.Chinook but not in server1.Chinook2:
#        TABLE: PlaylistTrack
#
# WARNING: Objects in server1.Chinook2 but not in server1.Chinook:
#        TABLE: Address
#
#                                                   Defn    Row     Data   
# Type      Object Name                             Diff    Count   Check  
# ------------------------------------------------------------------------- 
# TABLE     Album                                   pass    pass    SKIP    
# TABLE     Artist                                  pass    pass    SKIP    
# TABLE     Customer                                FAIL    pass    SKIP    
#
# Object definitions differ. (--changes-for=server1)
#

--- `Chinook`.`Customer`
+++ `Chinook2`.`Customer`
@@ -3,16 +3,14 @@
   `FirstName` varchar(40) CHARACTER SET utf8 NOT NULL,
   `LastName` varchar(20) CHARACTER SET utf8 NOT NULL,
   `Company` varchar(80) CHARACTER SET utf8 DEFAULT NULL,
-  `Address` varchar(70) CHARACTER SET utf8 DEFAULT NULL,
-  `City` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
-  `State` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
-  `Country` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
-  `PostalCode` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
   `Phone` varchar(24) CHARACTER SET utf8 DEFAULT NULL,
   `Fax` varchar(24) CHARACTER SET utf8 DEFAULT NULL,
   `Email` varchar(60) CHARACTER SET utf8 NOT NULL,
   `SupportRepId` int(11) DEFAULT NULL,
+  `AddressId` int(11) DEFAULT NULL,
   PRIMARY KEY (`CustomerId`),
   KEY `IFK_CustomerSupportRepId` (`SupportRepId`),
+  KEY `FK_CustomerAddressId` (`AddressId`),
+  CONSTRAINT `FK_CustomerAddressId` FOREIGN KEY (`AddressId`) REFERENCES `Address` (`AddressId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `FK_CustomerSupportRepId` FOREIGN KEY (`SupportRepId`) REFERENCES `Employee` (`EmployeeId`) ON DELETE NO ACTION ON UPDATE NO ACTION
 ) ENGINE=InnoDB AUTO_INCREMENT=60 DEFAULT CHARSET=latin1

# TABLE     Employee                                FAIL    pass    SKIP    
#
# Object definitions differ. (--changes-for=server1)
#

--- `Chinook`.`Employee`
+++ `Chinook2`.`Employee`
@@ -6,15 +6,13 @@
   `ReportsTo` int(11) DEFAULT NULL,
   `BirthDate` datetime DEFAULT NULL,
   `HireDate` datetime DEFAULT NULL,
-  `Address` varchar(70) CHARACTER SET utf8 DEFAULT NULL,
-  `City` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
-  `State` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
-  `Country` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
-  `PostalCode` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
   `Phone` varchar(24) CHARACTER SET utf8 DEFAULT NULL,
   `Fax` varchar(24) CHARACTER SET utf8 DEFAULT NULL,
   `Email` varchar(60) CHARACTER SET utf8 DEFAULT NULL,
+  `AddressId` int(11) DEFAULT NULL,
   PRIMARY KEY (`EmployeeId`),
   KEY `IFK_EmployeeReportsTo` (`ReportsTo`),
+  KEY `FK_EmployeeAddressId` (`AddressId`),
+  CONSTRAINT `FK_EmployeeAddressId` FOREIGN KEY (`AddressId`) REFERENCES `Address` (`AddressId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `FK_EmployeeReportsTo` FOREIGN KEY (`ReportsTo`) REFERENCES `Employee` (`EmployeeId`) ON DELETE NO ACTION ON UPDATE NO ACTION
 ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1

# TABLE     Genre                                   pass    pass    SKIP    
# TABLE     Invoice                                 pass    pass    SKIP    
# TABLE     InvoiceLine                             pass    pass    SKIP    
# TABLE     MediaType                               pass    pass    SKIP    
# TABLE     Playlist                                pass    pass    SKIP    
# TABLE     Track                                   pass    pass    SKIP   

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

As you can see in the diff output above, I dropped the PlaylistTrack table and created the Address table (see # WARNING). I also dropped the address related columns in Employee and Customer tables (see lines starting with -) and added the addressId foreign key (see lines starting with +).

For more information, see:
http://dev.mysql.com/doc/mysql-utilities/1.3/en/mysqldbcompare.html#option_mysqldbcompare_run-all-tests

No comments: