Skip to content

Instantly share code, notes, and snippets.

Last active February 1, 2024 21:08
Show Gist options
  • Star 11 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
  • 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 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
# State the username for your MySQL / MariaDB instace that can access the neccessary databases
# Point this script to mysql executable file
# State the password to the username above
# Be aware that using plain password is unsecure
# Point this script to mysqldump executable file
# Declare the name of the remote that will be used as a remote storage
# 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
# Echo the result
echo -e "===\nFinished backup process for $db. Check your remote folder or watch for errors."
Copy link

hlepesant commented Nov 13, 2019

Why not using "rclone rcat" ?

Copy link

sutlxwhx commented Dec 21, 2019

Why not using "rclone rcat" ?

I am not sure rcat behaves the same way as copy while processing files and folders

Copy link

I have 2 sugestions
first, let give some compression to file
$MYSQLDUMP --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip > "$BACKUP_DIR/$db.sql.gz"

and copy only that file
rclone copy "$BACKUP_DIR/$db.sql.gz" $REMOTE:$BACKUP_DIR

Copy link

BlagoYar commented Feb 8, 2021

I try and get error 23: / Syntax error: word unexpected (expecting "do")

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.

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