Run mysqldump remotely via SSH to create local SQL dumps

remote-mysqldump.sh

#!/bin/bash

usage() {
    echo ""
    echo "USAGE:"
    echo ""
    echo "    $0 [ OPTIONS and PARAMETERS ]"
    echo ""
    echo "OPTIONS:"
    echo ""
    echo "    --sshhost    SSH hostname"
    echo "    --sshuser    SSH user"
    echo "    --sshport    SSH port"
    echo "    --mysqlhost  MySQL hostname"
    echo "    --mysqluser  MySQL user"
    echo "    --mysqlpass  MySQL password"
    echo "    --databases  comma separated database list"
    echo "    --dstdir     destination directory for dump files"
    echo ""
    echo ""
    echo "EXAMPLE:"
    echo ""
    echo "    Copy 'database1', 'database2' from 192.168.1.1 server to '/home/user/databases/'
    echo "    directory:
    echo ""
    echo "        $0 --sshhost 192.168.1.1 --sshuser <user> --sshport 22 \\"
    echo "           --mysqlhost localhost --mysqluser <user> --mysqlpass <password> \\"
    echo "           --databases database1,database2 \\"
    echo "           --dstdir /home/user/databases/"
    echo ""
    echo "RESULTs:"
    echo ""
    echo "    - _schema.sql.bz2 for tables and schema"
    echo "    - _grants.sql.bz2 for database user permissions"
    echo "    - <database>.sql.bz2 dump for each database"
    echo ""
    exit 1
}

if [[ $# -lt 16 ]] ; then usage ; fi # number of parameters * 2

while [[ $# -gt 1 ]]; do
    case "$1" in
        "--sshhost"  ) SSHHOST="$2"                          ; shift ;;
        "--sshuser"  ) SSHUSER="$2"                          ; shift ;;
        "--sshport"  ) SSHPORT="$2"                          ; shift ;;
        "--mysqlhost") MYSQLHOST="$2"                        ; shift ;;
        "--mysqluser") MYSQLUSER="$2"                        ; shift ;;
        "--mysqlpass") MYSQLPASS="$2"                        ; shift ;;
        "--databases") IFS=',' read -r -a DATABASES <<< "$2" ; shift ;;
        "--dstdir"   ) DSTDIR="$2"                           ; shift ;;
                    *) usage                                 ;       ;;
    esac
    shift
done

SSH="ssh $SSHUSER@$SSHHOST -p$SSHPORT -oBatchMode=yes -oPubkeyAuthentication=yes -oPasswordAuthentication=no"
MYSQLDUMP="mysqldump -h'$MYSQLHOST' -u'$MYSQLUSER' -p'$MYSQLPASS' --max_allowed_packet=512M --single-transaction --skip-add-locks --quick"
MYSQL="mysql -h'$MYSQLHOST' -u'$MYSQLUSER' -p'$MYSQLPASS' --batch --skip-column-names"
BZIP2="bzip2 -1"

if [ ! $($SSH "echo OK 2>&1") ]; then
    echo "User does not have public key on the remote host, use:"
    echo ""
    echo "    cat ~/.ssh/id_rsa.pub | (ssh usuario@host \"cat >> ~/.ssh/authorized_keys\")"
    echo ""
    exit
fi

echo -n "Extracting database schema... "
$SSH "$MYSQLDUMP --no-data=true --add-drop-database=true --all-databases=true" | $BZIP2 > "$DSTDIR/_schema.sql.bz2"
echo "OK"

echo -n "Extracting user privileges... "
CMD="$MYSQL -e 'SELECT user, host FROM user' mysql"
GRANTS=""
($SSH $CMD) | {
    while read USER HOST; do
        GRANTS="$GRANTS SHOW GRANTS FOR '$USER'@'$HOST';"
    done
$SSH $MYSQL " -e \"$GRANTS\"" | sed 's/$/;/g' | $BZIP2 > "$DSTDIR/_grants.sql.bz2"
}
echo "OK"

echo "Exporting data:"
for DB in ${DATABASES[@]}; do
    echo -n "  - $DB..."
    $SSH "$MYSQLDUMP --no-data=false --add-drop-table=true --databases $DB" | $BZIP2 > "$DSTDIR/$DB.sql.bz2"
    echo "OK"
done