// you’re reading...

MySQL

Comparing MySQL table structures across hosts

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



Discussion

No comments for “Comparing MySQL table structures across hosts”

Post a comment

Help support my site and buy a domain name at http://domainsemailhosting.com/