Multiple database servers that are kept synchronized manually or are compartmentalized for security purposes but are by practice kept up-to-date manually are prone to unintentional (and sometimes intentional) divergence.
For example, you may maintain a production server and a development server. The development server may have the same databases with the same structures as the production server (but maybe not the same data). However, the natural course of development, administrative tasks, and maintenance can sometimes leave the development server behind.
When this happens, you need to have a way to quickly check the schema for a table on the production server to see if the development server has the same structure. The utility of choice for this operation is mysqldiff.
mysqldiff --server1=root:root@localhost \
--server2=root:root@localhost:3307 world.city:world.city --changes-for=server2# server1 on localhost: ... connected. # server2 on localhost: ... connected. # Comparing world.city to world.city [FAIL] # Object definitions differ. (--changes-for=server2) # --- world.city +++ world.city @@ -4,6 +4,7 @@ `CountryCode` char(3) NOT NULL DEFAULT '', `District` char(20) NOT NULL DEFAULT '', `Population` int(11) NOT NULL DEFAULT '0', + `Climate` enum('tropical','dry','mild','continental','polar') DEFAULT NULL, PRIMARY KEY (`ID`), KEY `CountryCode` (`CountryCode`), CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`) Compare failed. One or more differences found.
The user must have SELECT privileges for both objects on both servers as well as SELECT on the mysql database.
You can set the direction of the compare by using the
--changes-for option. For
example, to see the changes for server1 as the target, use