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

%d bloggers like this: