Dump every table from every database into its own sql file

dump_tables.sh

#!/bin/bash

# Simple MySQL dump script which dumps each database to a compressed
# file with the date included in the file name

MYSQL='/usr/bin/mysql'

MYSQLDUMP='/usr/bin/mysqldump'
DUMPOPTS='--opt --hex-blob --skip-extended-insert'

DATEFORMAT='%Y%m%d-%H%M%S'  # See man date

user=dprevite
pass=
dir='.'
while getopts 'u:d:p:h' OPTION
do
    case $OPTION in
  u)
      user="$OPTARG"
            ;;
  d)
      dir="$OPTARG"
            ;;
  p)
      pass="$OPTARG"
            ;;
  h|?)
      printf  "Usage: %s: [-u USER] [-p PASSWORD] [-d DIRECTORY]\n" \
    $(basename $0) >&2
      exit 2
            ;;
    esac
done
if [ -z "$pass" ]
then
    read -s -p "password: " pass ; printf "%b" "\n"
fi

# Get the names of the database tables
databases=`$MYSQL -u$user -p$pass --skip-column-names -e'SHOW DATABASES'`

# Write the compressed dump for each table
for db in $databases; do
  if [ $db != 'information_schema' ]
  then
    echo "Opening $db..."
    tables=`$MYSQL -u$user -p$pass --skip-column-names -e"SHOW tables FROM $db"`
    for table in $tables; do
      filename=`date +"$dir/$db-$table-$DATEFORMAT.sql"`
      echo "Dumping $db.$table..."
      $MYSQLDUMP $DUMPOPTS -u$user -p$pass $db $table > $filename
      echo
      echo
    done
  fi
done