Mysql – Compare two MySQL dump files

backupMySQLmysqldumpschema

I am trying to compare two MySQL dump files. One was made from a fresh install of a web application and the other is a dump from the same web application that has been upgraded from a previous version to the same version number as the fresh install.

Basically I want to make sure that the upgrade scripts worked properly and all the proper columns and constraints are in the upgraded version just as they are in fresh install. I thought a simple diff/sdiff would be good enough for this but I ran into the problem with that: Column order.

The column order between the fresh install and the upgrade are different giving many false positive "differences". It is easy to see when looking at the output that the same column is simply in a different spot but with hundreds of tables it would take a long time to check and identify these false positives.

Is there a better way to compare these two dumps that will ignore column order (or a way to force the dumps to have columns in alphabetical order or something similar) or an entirely different approach that would work better. I am admittedly fairly new to MySQL and am just learning as I go so any help would be much appreciated.

Thanks.

Best Answer

Unfortunately, you cannot force the ordering of columns in a mysqldump. You can, however, use the table INFORMATION_SCHEMA.COLUMNS. You will need the following:

  • MySQL Instance with the old data loaded
  • MySQL Instance with the new data loaded
  • Some Patience and Scripting

With this setup in mind, here is what you need to do:

STEP 01) Use Query to Present Columns Ordered Per Database/Table

Here is the Query:

SELECT CONCAT(table_schema,'.',table_name),
GROUP_CONCAT(column_name ORDER BY column_name)
FROM information_schema.columns WHERE table_schema NOT IN
('information_schema','performance_schema')
GROUP BY table_schema,table_name;

This will give you every table followed by a CSV list of alphabetically-sorted columns

STEP 02) Launch this Column Query From Both MySQL Instances

IPADDR_OLD=10.1.20.30
IPADDR_NEW=10.1.20.40
SQLSTMT="SELECT CONCAT(table_schema,'.',table_name),"
SQLSTMT="${SQLSTMT} GROUP_CONCAT(column_name ORDER BY column_name)"
SQLSTMT="${SQLSTMT} FROM information_schema.columns WHERE table_schema NOT IN"
SQLSTMT="${SQLSTMT} ('information_schema','performance_schema')"
SQLSTMT="${SQLSTMT} GROUP BY table_schema,table_name"
mysql -u... -p... -h${IPADDR_OLD} -ANe"${SQLSTMT}"  > column_listing1.txt
mysql -u... -p... -h${IPADDR_NEW} -ANe"${SQLSTMT}" >> column_listing2.txt
cat column_listing1.txt  > column_listing.txt
cat column_listing2.txt >> column_listing.txt

Run the Column Query and Collect the Results in column_listing.txt

STEP 03) Perform Unique Sort ; Check For Duplicate Table Names

sort -u < column_listing.txt > column_listing.unq
UNQ_LINES=`wc -l < column_listing.unq`
TXT_LINES=`wc -l < column_listing.txt`
(( DIF_LINES = (UNQ_LINES + UNQ_LINES - TXT_LINES) / 2))
echo ${DIF_LINES}

If everything is identical, DIF_LINES should 0. Otherwise, there are duplicates present.

STEP 04) If There Are Duplicates, Find Them

diff column_listing1.txt column_listing2.txt

Give it a Try !!!

UPDATE 2013-03-22 13:04 EDT

You can also check other metadata tables such as

TABLE_CONSTRAINTS

mysql> desc TABLE_CONSTRAINTS;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG | varchar(512) | NO   |     |         |       |
| CONSTRAINT_SCHEMA  | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_NAME    | varchar(64)  | NO   |     |         |       |
| TABLE_SCHEMA       | varchar(64)  | NO   |     |         |       |
| TABLE_NAME         | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_TYPE    | varchar(64)  | NO   |     |         |       |
+--------------------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

STATISTICS

mysql> desc STATISTICS;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512)  | NO   |     |         |       |
| TABLE_SCHEMA  | varchar(64)   | NO   |     |         |       |
| TABLE_NAME    | varchar(64)   | NO   |     |         |       |
| NON_UNIQUE    | bigint(1)     | NO   |     | 0       |       |
| INDEX_SCHEMA  | varchar(64)   | NO   |     |         |       |
| INDEX_NAME    | varchar(64)   | NO   |     |         |       |
| SEQ_IN_INDEX  | bigint(2)     | NO   |     | 0       |       |
| COLUMN_NAME   | varchar(64)   | NO   |     |         |       |
| COLLATION     | varchar(1)    | YES  |     | NULL    |       |
| CARDINALITY   | bigint(21)    | YES  |     | NULL    |       |
| SUB_PART      | bigint(3)     | YES  |     | NULL    |       |
| PACKED        | varchar(10)   | YES  |     | NULL    |       |
| NULLABLE      | varchar(3)    | NO   |     |         |       |
| INDEX_TYPE    | varchar(16)   | NO   |     |         |       |
| COMMENT       | varchar(16)   | YES  |     | NULL    |       |
| INDEX_COMMENT | varchar(1024) | NO   |     |         |       |
+---------------+---------------+------+-----+---------+-------+
16 rows in set (0.01 sec)

REFERENTIAL_CONSTRAINTS

mysql> desc REFERENTIAL_CONSTRAINTS;
+---------------------------+--------------+------+-----+---------+-------+
| Field                     | Type         | Null | Key | Default | Extra |
+---------------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG        | varchar(512) | NO   |     |         |       |
| CONSTRAINT_SCHEMA         | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_NAME           | varchar(64)  | NO   |     |         |       |
| UNIQUE_CONSTRAINT_CATALOG | varchar(512) | NO   |     |         |       |
| UNIQUE_CONSTRAINT_SCHEMA  | varchar(64)  | NO   |     |         |       |
| UNIQUE_CONSTRAINT_NAME    | varchar(64)  | YES  |     | NULL    |       |
| MATCH_OPTION              | varchar(64)  | NO   |     |         |       |
| UPDATE_RULE               | varchar(64)  | NO   |     |         |       |
| DELETE_RULE               | varchar(64)  | NO   |     |         |       |
| TABLE_NAME                | varchar(64)  | NO   |     |         |       |
| REFERENCED_TABLE_NAME     | varchar(64)  | NO   |     |         |       |
+---------------------------+--------------+------+-----+---------+-------+
11 rows in set (0.01 sec)

mysql>

Setup queries that perform the same styled comparison

Related Question