Bash script to backup all mysql databases thru mysqldump

backup_mysqldb.sh

#!/bin/bash

# Destiny folder where backups are stored
DEST=/tmp/bacula/server01
CURRDATE=$(date +"%F")

# Hostname where MySQL is running
HOSTNAME="srv-mysql"
# User name to make backup
USER="root"
# File where has the mysql user password
PASS="$(cat /root/etc/mysqlpass)"

DATABASES=$(mysql -h $HOSTNAME -u $USER -p$PASS -e "SHOW DATABASES;" | tr -d "| " | grep -v Database)

[ ! -d $DEST ] && mkdir -p $DEST

for db in $DATABASES; do
  FILE="${DEST}/$db.sql.gz"
  FILEDATE=

  # Be sure to make one backup per day
  [ -f $FILE ] && FILEDATE=$(date -r $FILE +"%F")
  [ "$FILEDATE" == "$CURRDATE" ] && continue

  [ -f $FILE ] && mv "$FILE" "${FILE}.old"
  mysqldump --single-transaction --routines --quick -h $HOSTNAME -u $USER -p$PASS -B $db | gzip > "$FILE"
  chown bacula:disk "$FILE"
  rm -f "${FILE}.old"
done

mysql -h $HOSTNAME -u $USER -p$PASS -e "SHOW DATABASES;" | tr -d "| " | grep -v Database can be simplified to mysql -h $HOSTNAME -u $USER -p$PASS -s -N -e "SHOW DATABASES;"

-s (--silent) - Remove the grid
-N (--skip-column-names) - Hide headers