Backup all MySQL databases into separate files

dump.sh

#!/bin/sh
### (v2)
## backup each mysql db into a different file, rather than one big file
## as with --all-databases. This will make restores easier.
## To backup a single database simply add the db name as a parameter (or multiple dbs)

## IGNORE THIS BLOCK TO >=(v2)
## Putting the script in /var/backups/mysql seems sensible... on a debian machine that is
## Create the user and directories
# mkdir -p /var/backups/mysql/databases
# useradd --home-dir /var/backups/mysql --gid backup --no-create-home mysql-backup
## Remember to make the script executable, and unreadable by others
# chown -R mysql-backup:backup /var/backups/mysql
# chmod u=rwx,g=rx,o= /var/backups/mysql/dump.sh

## (v2) 
# This version work to execute the bash whit a crontab/cronjob and the save all the dbs in a tar.gz file
## To save de databases the dir 'databases' and bk to save all the databases in a compress file
# mkdir databases
# mkdir bk

## crontab entry - backup every night at 02:00
# sudo -u mysql-backup crontab -e
# 0 2 * * * /var/backups/mysql/dump.sh

## Create 'backup' mysql user
# CREATE USER 'backup'@'localhost' IDENTIFIED BY 's3cr3t';
# GRANT EVENT, LOCK TABLES, PROCESS, REFERENCES, SELECT, SHOW DATABASES, SHOW VIEW, TRIGGER ON *.* TO 'backup'@'localhost' ;


USER="root"
#USER="backup"
#PASSWORD="s3cr3t"
DIR=$(dirname $(readlink -f $0))
OUTPUTDIR=$DIR"/databases"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"
#Consulta que excluye las DB de sytema y otras innecesarias
SHOW="SHOW DATABASES WHERE \`DATABASE\` NOT IN ( \
                                'mysql','information_schema')"
FILE=$DIR"/bk/DB_"$(date '+%y%m%d%H%M.tar.gz')
if [ -z "$1" ]; then
        databases=`$MYSQL --user=$USER --batch --skip-column-names -e "$SHOW"`
        for database in $databases; do
                $MYSQLDUMP \
                --user=$USER \
                --force \
                --quote-names --dump-date \
                --opt --single-transaction \
                --events --routines --triggers \
                --databases $database \
                --result-file="$OUTPUTDIR/$database.sql"
        done
else
        for database in ${@}; do
                $MYSQLDUMP \
                --user=$USER \
                --force \
                --quote-names --dump-date \
                --opt --single-transaction \
                --events --routines --triggers \
                --databases $database \
                --result-file="$OUTPUTDIR/$database.sql"
        done
fi
#Compress all the databases
tar -czvf $FILE -C $DIR "databases"
echo 'Save in: '$FILE

Se añadio:

• Excluye de datos innecesarias como mysql, information_schema.

• Al final comprime todas las bases de datos en un archivo que incluye como nombre la fecha del bk

The script to do the backup to multiples and especific databases, now can execute ouput a compress file of all the databases.
Too this script was editing to run from cronjob/crontab whithout trouble