Documentation Home
MySQL Utilities 1.5 Manual
Related Documentation Download this Manual
PDF (US Ltr) - 1.5Mb
PDF (A4) - 1.5Mb
EPUB - 366.1Kb
HTML Download (TGZ) - 289.0Kb
HTML Download (Zip) - 301.1Kb


MySQL Utilities 1.5 Manual  /  ...  /  How do you tell if a table on server A has the same structure as the same table on server B?

3.1.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.

Objectives

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 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.
        

Discussion

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'.


User Comments
Sign Up Login You must be logged in to post a comment.