Script created on ticket:907 and used on PuffinServer.

#!/bin/bash

# Directory for root to dump all database files 
DUMP_DIR="/var/backups/mysql/sqldump"

if [[ ! -d "${DUMP_DIR}" ]]; then
  mkdir "${DUMP_DIR}" -p
  chmod 700 "${DUMP_DIR}"
fi

# All the MySQL databases, excluding ones we don't want 
DATABASES=$(mysql -NBA -e 'SHOW DATABASES' | grep -vw 'mysql' | grep -vw 'information_schema' | grep -vw 'performance_schema' )

# Loop through the databases and dump them
for d in ${DATABASES} ; do
  # Find the cache tables
  CACHE_TABLES=$(mysql -NBA -e 'show tables' ${d} | egrep '^cache|^flood$|^watchdog$')
  for c in ${CACHE_TABLES}; do
    mysql -NBA -e "TRUNCATE ${c}" ${d}
  done
  # Dump the databases to DUMP_DIR
  echo "Dumping ${d}"
  nice -n19 mysqldump --add-drop-table ${d} > ${DUMP_DIR}/${d}.sql || exit 1
done