db_dump.sh

#! /bin/bash
# Simple script to dump the contents of a database into separate files in the directory "/backups/"
# Symlink into /etc/cron.daily/ or add to crontab
# Originally from: https://gist.github.com/brianredbeard
# /usr/local/scripts/db_dump


DESTDIR=/vagrant/backups/
DATEFORMAT="+%Y%m%d-%H%M"
 
#mysqldump -x --add-drop-table --all-databases > /backups/mysqldb-`date +%F-%I%p`.sql

# Getting all databases form MySQL/MariaSB
ALLDBS=$(/usr/bin/mysql --defaults-extra-file=/root/.my.cnf -e 'show databases' | awk '{print $1}' | grep -v "^Database$")

# It is also possible to specify single DB
# ALLDBS="db1 db2 db3"

# One line Script for all DBs
# /usr/bin/mysql --defaults-extra-file=/root/.my.cnf -e 'show databases' | awk '{print $1}' | grep -v "^Database$" | xargs -i{} mysqldump --defaults-extra-file=/root/.my.cnf --opt -Q -r ${DESTDIR}{}-`date ${DATEFORMAT}`.sql {}
# gzip -q ${DESTDIR}/*.sql 
 
for DB in $ALLDBS ; do
    echo " Dumping DB: ${DB}"
    FILENAME=${DESTDIR}${DB}-$(date ${DATEFORMAT})
    # FILENAME=${DESTDIR}${DB}
    mysqldump --defaults-extra-file=/root/.my.cnf --skip-dump-date --databases --opt -Q -r ${FILENAME}.sql ${DB}
    echo " Compressing SQL: ${FILENAME}.sql"
    # Removing date and filename to allow comparison between compressed contentents
    gzip -q ${FILENAME}.sql  --force --no-name --best
    echo ""
done

# echo " LogRotating DUMP files"
# logrotate /vagrant/backups/logrotate
# echo ""

# Using fdupes to remove duplicated files (by file contents)
fdupes ${DESTDIR} --delete --quiet  --noprompt