dump_tables_matching_pattern.sh

#!/bin/bash

echoerr() { echo "$@" 1>&2; }

upper() { echo "$@" | tr [a-z] [A-Z]; }
 
if [ $# -lt 5 ]; then
  echoerr "Exports data from mysql database in tables matching a like pattern e.g. 'table_%'"
  echoerr "Usage: $0 host dbname dbuser dbpass pattern"
  exit 1
fi
 
HOST=$1
DBNAME=$2
DBUSER=$3
DBPASS=$4
PATTERN=$5
 
TABLES=( `mysql -h$HOST -u$DBUSER -p$DBPASS $DBNAME --silent -e "show tables like '$PATTERN'"` )
 
for TABLE in "${TABLES[@]}"; do
  TABLE_UPPER=$(upper "$TABLE")
  echoerr "*** GRABBING TABLE: $TABLE_UPPER"
  mysqldump -h$HOST -u$DBUSER -p$DBPASS $DBNAME $TABLE --quick --single-transaction
done