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
