Create separate MySQL db dumps and use hot-backup for InnoDB-only dbs

mysql-backup.sh

#!/bin/bash

# set your user and password in ~/.my.cnf
# [client]
# user=root
# password=pass
#
# otherwise change to
# MYSQL_USER="-u user"
# MYSQL_PASSWORD="-pPASS"

MYSQL_USER=""
MYSQL_PASSWORD=""
MYSQL=/usr/bin/mysql
MYSQLDUMP=/usr/bin/mysqldump
NOW=$(date +"%a")

databases=`$MYSQL $MYSQL_USER $MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Evi "(Database|information_schema)" | grep -v ^performance_schema$`

for db in $databases; do

  # check if there are any tables with a non-transactional engine in db
  # disable locking only if no such table is present to ensure consistency
  # if you want hot backups (no locking) make sure to use InnoDB for all tables
  nontransactional=`$MYSQL $MYSQL_USER $MYSQL_PASSWORD -e "SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = '$db';" | grep -Evi "(ENGINE|InnoDB)"`
  if [ -z "$nontransactional" ]
  then
    NO_LOCKING="--single-transaction --quick"
  fi

  $MYSQLDUMP $MYSQL_USER $MYSQL_PASSWORD $NO_LOCKING --routines --triggers $db | gzip > ./${db}_${NOW}.sql.gz
  NO_LOCKING=""

done