Backup your MySQL databases automatically

This is the snippet you are looking for in case you need a way to backup your MySQL databases automatically.

Since I’m running multiple individual virtual machines I needed a way to sync all of the data back to my XPenology file server for safe storage. Since I don’t use RAID or any other protection against disk drive failures it’s important that all files are synced every day.

rsync is perfect for handling files stored on the filesystem, so the only thing I had to do was to write a small script that makes daily backups of all my MySQL databases into small neat .gz files. In case anyone is interested in the script, here it is. Simply provide a valid username and passord and specify where you want the archives to be saved. The script will create subdirectories within the specified path matching the date when the backup was saved. The script will keep backups for a week before they are automatically deleted: -ctime +7.

 

#! /bin/bash

MYSQL_USER=""
MYSQL_PASSWORD=""
BACKUP_DIR="/opt/mysql_backup"

TIMESTAMP=$(date +"%F")
MYSQL=/usr/bin/mysql
MYSQLDUMP=/usr/bin/mysqldump

mkdir -p "$BACKUP_DIR/$TIMESTAMP"

databases=`$MYSQL --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|mysql)"`

for db in $databases; do
  $MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip > "$BACKUP_DIR/$TIMESTAMP/$db.gz"
done

# delete backup older then a week
find "$BACKUP_DIR" -type d -ctime +7 -exec rm -rf {} \;

 

Share this:

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.