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
