MySQL Utilities

3.2.4 How do you tell if a table on server A has the same structure as the same table on server B?

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.


The goal is to compare a table schema on one server to another and show they differ.

Example Execution

shell> mysqldiff --server1=root:root@localhost \
--server2=root:root@localhost:3307 --changes-for=server2
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing to                               [FAIL]
# Object definitions differ. (--changes-for=server2)

@@ -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,
   KEY `CountryCode` (`CountryCode`),
   CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
Compare failed. One or more differences found.


Notice to accomplish this task, we simply specified each server with --server1 and --server2 then specified the database objects to compare with the db.object:db.object syntax.

Permissions Required

The user must have SELECT privileges for both objects on both servers as well as SELECT on the mysql database.

Tips and Tricks

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 '--changes-for=server1'.