Have you ever had to quickly scan table structures to see if they were the same on two separate hosts? Eyeballing this can be a total pain.

I wrote a quickie Bash script to automate this. Please note, this is NOT a thorough test. Just a glimpse or cursory check. A deeper look will reveal things like auto increment values producing false positives.

#!/bin/bash

########################################
#
# Find table structure differences across 2 hosts.
# License: Use at your own risk!
#
########################################


ORIGIN_DB_HOST=localhost
ORIGIN_DB_USER=test
ORIGIN_DB_PASS=test

TARGET_DB_HOST=remotehost
TARGET_DB_USER=remoteuser
TARGET_DB_PASS=secret

DB_NAME=some_schema_name

SQL_SHOW_TABLES="SHOW TABLES"
SQL_SHOW_CREATE="SHOW CREATE TABLE "


###############################################################################
################# Test if same tables exist ###################################
###############################################################################

ORIGIN_TABLE_NAMES=`mysql -u $ORIGIN_DB_USER -p$ORIGIN_DB_PASS -h $ORIGIN_DB_HOST -s -e "$SQL_SHOW_TABLES" $DB_NAME`
TARGET_TABLE_NAMES=`mysql -u $TARGET_DB_USER -p$TARGET_DB_PASS -h $TARGET_DB_HOST -s -e "$SQL_SHOW_TABLES" $DB_NAME`

echo "Origin Table Names"
md5 -qs "$ORIGIN_TABLE_NAMES"

echo "Target Table Names"
md5 -qs "$TARGET_TABLE_NAMES"


###############################################################################
##################### Diff the table structures ###############################
###############################################################################

for t in $TARGET_TABLE_NAMES; do

    echo "=========== Comparing table: $t ============\n"
    ORIGIN_TABLE=`mysql -u $ORIGIN_DB_USER -p$ORIGIN_DB_PASS -h $ORIGIN_DB_HOST -s -e "$SQL_SHOW_CREATE $t\G" $DB_NAME`
    TARGET_TABLE=`mysql -u $TARGET_DB_USER -p$TARGET_DB_PASS -h $TARGET_DB_HOST -s -e "$SQL_SHOW_CREATE $t\G" $DB_NAME`

    MD5_ORIGIN=`md5 -qs "$ORIGIN_TABLE"`
    MD5_TARGET=`md5 -qs "$TARGET_TABLE"`

    if [ "$MD5_ORIGIN" != "$MD5_TARGET" ]; then
        echo "[Different table structures]\n"
        echo $ORIGIN_TABLE
        echo $TARGET_TABLE
    else
        echo "Tables jive"
        #echo $ORIGIN_TABLE
        #echo $TARGET_TABLE
    fi

done