Archiving all your databases easily

MariaDB_dump.sh

#!/bin/bash
# ----------------------------------------------------------------------------
# "THE BEER-WARE LICENSE" (Revision 42):
# <[email protected]> wrote this file. As long as you retain this notice you
# can do whatever you want with this stuff. If we meet some day, and you think
# this stuff is worth it, you can buy me a beer in return Antoine "Gecko" Pous
# ----------------------------------------------------------------------------

USER=<sql_username>
PASSWORD=<sql_password>
OUTPUT=/path/to/destination
TMP=/path/to/temporary/directory

# Go to temporary directory
echo "Go to $TMP"
cd $TMP

# Fetch all databases
echo "Fetch all databases"
databases=`mysql --user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

# Dump each database into `$TMP/YYYYmmdd.dbname.sql`
for db in $databases; do
  if [[ "$db" != "information_schema" ]] && [[ "$db" != _* ]] ; then
    echo "Dumping database: $db"
    mysqldump --single-transaction --force --opt --user=$USER --password=$PASSWORD --databases $db > $TMP/`date +%Y%m%d`.$db.sql
    echo "Done!"
  fi
done

# Create archive with all dump into $OUTPUT
echo "Archiving all dump"
tar -zcvf $OUTPUT/`date +%Y%m%d`.tar.gz *.sql
echo "Done!"

# Clear temporary dump
echo "Cleaning temporary files"
rm "$TMP/*" > /dev/null 2>&1
echo "Done!"