Skip to content

Instantly share code, notes, and snippets.

@sutlxwhx
Last active February 1, 2024 21:08
Show Gist options
  • Save sutlxwhx/7355dd1a65ea2a0889fb8dee6059283e to your computer and use it in GitHub Desktop.
Save sutlxwhx/7355dd1a65ea2a0889fb8dee6059283e to your computer and use it in GitHub Desktop.
Backup all your MySQL / MariaDB databases with rclone
#!/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
@NoahDar
Copy link

NoahDar commented May 8, 2022

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.

@ojifahru
Copy link

ojifahru commented Jan 4, 2023

how to exclude some database?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment