diff between two mysql databases

get differences between two iterations of the same database; requires the username and the password be set in ~/.my.cnf ; do not add the database name to ~/.my.cnf

... not perl, posting here so I'll have access to the script when not at home

#!/usr/bin/env bash

echo "" > differences.diff;

DB_ORIGINAL="db_old"
DB_MODIFIED="db_modified"

declare -A SKIP_TABLES
SKIP_TABLES[hits]=1
SKIP_TABLES[spam_reports]=1
SKIP_TABLES[searches]=1

for i in `mysql $DB_ORIGINAL --skip-column-names -e "show tables" | awk '{print $1}'`;
do 
    echo "looking at " $i;    
    echo "TABLE " $i >> differences.diff

    mysqldump --no-data $DB_MODIFIED $i > $i.definition.modified.sql; 
    mysqldump --no-data $DB_ORIGINAL $i > $i.definition.original.sql; 
    diff $i.definition.original.sql $i.definition.modified.sql >> differences.diff;
    
    #clean up
    rm $i.definition.original.sql 
    rm $i.definition.modified.sql
    # skip data
    if [[ ${SKIP_TABLES[$i]} = 1 ]]
    then
        echo "skipping data for " $i;
        continue;
    fi

    mysqldump --skip-extended-insert --no-create-info $DB_MODIFIED $i > $i.data.modified.sql; 
    mysqldump --skip-extended-insert --no-create-info $DB_ORIGINAL $i > $i.data.original.sql; 

    diff $i.data.original.sql $i.data.modified.sql | grep -v ' Host:'  >> differences.diff;
    
    # clean up
    rm $i.data.original.sql;
    rm $i.data.modified.sql;
done

testing Text::Clevery

... so far, so good

wondering if there are other Smarty parser in the wild

some command line shortcuts

dig for missed dependencies

egrep -r '^\s*(use|with|extends)\s+' * \
 | awk '{print $2}' | awk '{sub(/\;/, ""); print}' \
 | sort -u

jumpstart a "00-load.t"

egrep -r '^\s*package ' * \
 | awk '{ print $2}'  \
 | awk '{sub(/\;/, ""); print "use_ok(\""$1"\")"}' \
 | sort -u

quick syntax checks

for i in `find ./  | egrep '\.(pm|pl|cgi|t)$'`; do perl -wc $i; done

bad tests

bad tests are worse than no tests, and it's better to have no tests than to have tests designed to pass


there it is, I've said it publicly

sorting utf8 with Perl

What's Wrong with sort and How to Fix It

informative perl utf8 article

use unicode by default in Perl

how to do utf-8 with HTML::Mason

how to do utf-8 with HTML::Mason

4 easy steps to make Mason utf-8 Unicode clean with Apache, mod_perl, and DBI


PerlSetVar MasonPreamble "use utf8;" fixed it for me


yes, I know Mason 2 is live :)