Backup MySQL databases to external server via SFTP

databases_backup_sftp.sh

#! /bin/bash

 TIMESTAMP=$(date +"%F-%T")
 BACKUP_DIR="/var/backups/databases/my_website_backups/$TIMESTAMP"
 MYSQL_USER="root"
 MYSQL=/usr/bin/mysql
 MYSQL_PASSWORD="password"
 MYSQL_HOST="localhost"
 REMOTE_HOST="192.168.1.55"
 REMOTE_USER="backup"
 REMOTE_PASSWORD="password"
 REMOTE_DIR=/home/backup/db_backups/
 REMOTE_PORT="22"
 SSHPASS=/usr/bin/sshpass
 RSYNC=/usr/bin/rsync

 MYSQLDUMP=/usr/bin/mysqldump databases=`$MYSQL --user=$MYSQL_USER -h$MYSQL_HOST -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)"`
 mkdir -p "$BACKUP_DIR"

 for db in $databases; do
   echo $db
   time $MYSQLDUMP -Qc --add-drop-table --single-transaction --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip > "$BACKUP_DIR/$db.gz"
 done
 $SSHPASS -p $REMOTE_PASSWORD $RSYNC -r -e "ssh -p $REMOTE_PORT" $BACKUP_DIR $REMOTE_USER@$REMOTE_HOST:$REMOTE_DIR
 rm -rf /var/backups/databases/my_website_backups/*