Backup all your MySQL / MariaDB databases with rclone

backup.sh

#!/usr/bin/env bash
# To run this script you need to install https://rclone.org/ first
# Use current date and time for future backup folder name
TIMESTAMP=$(date +"%Y-%m-%d_%H-%M-%S")
# Declare the directory where the temporary backup files will be stored
BACKUP_DIR="/backup/$TIMESTAMP/mysql"
# State the username for your MySQL / MariaDB instace that can access the neccessary databases
MYSQL_USER=""
# Point this script to mysql executable file
MYSQL=/usr/bin/mysql
# State the password to the username above
# Be aware that using plain password is unsecure
MYSQL_PASSWORD=""
# Point this script to mysqldump executable file
MYSQLDUMP=/usr/bin/mysqldump
# Declare the name of the remote that will be used as a remote storage
REMOTE=""
# Create the temporary backup directory in case it doesn't exist
mkdir -p "$BACKUP_DIR"
# Get the list of all databases in your local MySQL / MariaDB instance
databases=`$MYSQL --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)"`
# Start a cycle
for db in $databases; do
	# Echo the starting notice
	echo -e "===\nStarted working with the $db."
	# Use mysqldump to create and actual backup of your database
	$MYSQLDUMP --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db > "$BACKUP_DIR/$db.sql"
	# Use rclone to upload files to the remote backup server
	rclone copy $BACKUP_DIR $REMOTE:$BACKUP_DIR
	# Echo the result
	echo -e "===\nFinished backup process for $db. Check your remote folder or watch for errors."
done

For me I use SSH to remotely create the backups which gets stored locally.

ssh -C remoteuser@remote_host mysqldump -u MYSQL_USER -p'MYSQL_PASSWORD' --all-databases | gzip -c | cat > backup.sql-date +%Y%m%d%H%M%S.gz

This way I don't have to deal with rsync or rclone. I use --all-databases to backup all databases. You can specify which database if you want.

how to exclude some database?