#!/bin/bash
#
# Backup all databases to separate archives.
#    1. Each table is dumped into separate *.sql file.
#    2. Each view is dumped into separate file with DEFINER=CURRENT_USER.
#    3. All routines are dumped into routines-dbname.sql,
#    4. All files are added to archive dbname.7z, compressed with 7-zip, max compression.
#
# Usage: mysqlback.sh /output/directory
#
# Author: Artur Bodera <abodera@gmail.com>
# Latest version of this script at: https://gist.github.com/2215200
#
#USER="root"
#PASSWORD=""
OUTPUTDIR="$1"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"

# get a list of databases
databases=`$MYSQL --user=$USER --password=$PASSWORD \
 -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

# backup all databases
for db in $databases; do

    # get list of items in this database
    tables=`$MYSQL --batch -N -e "select table_name from information_schema.tables where table_type='BASE TABLE' and table_schema='$db';"`
    views=`$MYSQL --batch -N -e "select table_name from information_schema.tables where table_type='VIEW' and table_schema='$db';"`
    routines=`$MYSQL --batch -N -e "select routine_name from information_schema.routines where routine_schema='$db';"`


    # backup all items as separate files
    for table in $tables; do
        echo "table-$db.$table"
        $MYSQLDUMP -Q -f --add-drop-table $db $table | \
        7z u -bd -ms=off -si"table-$db.$table.sql" -mx=9 "$OUTPUTDIR/$db.7z" >/dev/null
    done

    for view in $views; do
        echo "view-$db.$view"
        $MYSQLDUMP -Q -f --add-drop-table --no-data $db $view | \
        sed -r 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' | \
        7z u -bd -ms=off -si"view-$db.$view.sql" -mx=9 "$OUTPUTDIR/$db.7z" >/dev/null
    done

    for routine in $routines; do
        echo "routine-$db.$routine"
    done
    $MYSQLDUMP --routines --no-data --no-create-info --no-create-db $db | \
    sed -r 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' | \
    7z u -bd -ms=off -si"routines-$db.sql" -mx=9 "$OUTPUTDIR/$db.7z" >/dev/null

done
echo -------------------------------------
echo   Finished!