mysqltrunc.sh

#!/usr/bin/env bash

usage() {
  echo "\
Usage: $0 -u username -d database [-h host] [-o port)] \\
          [[-p] | [-P password]]
       host defaults to localhost
       port defaults to 3306
       -p will cause mysql to prompt for the password, good
       -P password will show the password in your ps list, evil" >&2
  exit 1
}

username=
passwdprompt=no
host=localhost
port=3306
database=
password=

while getopts ":u:pPd:h:" name; do
  case $name in
    u) username=$OPTARG;;
    p) passwdprompt="yes";;
    P) password=$OPTARG;;
    d) database=$OPTARG;;
    h) host=$OPTARG;;
    ?) usage;;
  esac
done

if [ -z "${username}" ] || [ -z "${database}" ]; then
  usage
fi

basecmd="mysql -u ${username} -D ${database} -h ${host} -P ${port}"

if [ "${passwdprompt}" = "yes" ]; then
  basecmd="${basecmd} -p"
elif [ -n "${password}" ]; then
  basecmd="${basecmd} -p${password}"
fi

tables=$(${basecmd} -e "SHOW TABLES;" | grep -v "+--" | grep -v "Tables_in_${dbname}")
if [ $? -ne 0 ]; then
  echo "Unable to retrieve the table names." >&2
  exit 1
fi

cmd=""

for table in ${tables}; do
  cmd="${cmd} TRUNCATE ${table};"
done

$(${basecmd} -e "${cmd}")