Backup-script for MySQL

Shell script for backing up MySQL database. I am running this on a Centos 7 DB-server with MariaDB, but should work on most OS and MySQL installations.

This is only an example, and the script is without any warranty.

Create a .sh file

# touch /bin/database_backup_script.sh

Edit the .sh file

# vi /bin/database_backup_script.sh

The basics

Delete old backups in folder:

find /var/mypath/ -mtime +3 -exec rm -rf {} \;

Where +3 is number of days.

Take a new backup:

mysqldump --all-databases | gzip > "/var/mypath/db_backup.sql.gz"

or

mysqldump single_database | gzip > "/var/mypath/db_backup.sql.gz"

The whole script

Please edit variables to match your setup.

#!/bin/bash

# Add single-transaction parameter if innoDB to prevent locking, See http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html#option_mysqldump_single-transaction

# modify the following to suit your environment
export NOW="$(date +'%Y%m%d_%H%M')"
export DAYS="3"
export DB_BACKUP="/var/backup/"
export DB_USER="root"
export DB_PASSWD=""
export DB_DATABASE="my_database" # If backup all, use: --all-databases
export FILENAME="$DB_DATABASE-$NOW".sql.gz # Set own file name if DB_DATABASE is --all-databases
export LOGFILE="$DB_BACKUP/backup_log_$DB_DATABASE-$(date +'%Y%m%d-%H%M').txt

# title and version
echo "MySQL backup started at $(date +'%d-%m-%Y %H:%M:%S')" >> "$LOGFILE"

# this will delete old file older than DAYS
echo "START: Deleting old backup $(date +'%d-%m-%Y %H:%M:%S')" >> "$LOGFILE"
find $DB_BACKUP -mtime +$DAYS -exec rm -rf {} \;
echo "COMPLETE: Deleting old backup $(date +'%d-%m-%Y %H:%M:%S')" >> "$LOGFILE"

# Next command take backup compressed with bzip2 save in directory DB_BACKUP
echo "START: Backing up database $(date +'%d-%m-%Y %H:%M:%S')" >> "$LOGFILE"
mysqldump -u$DB_USER -p$DB_PASSWD $DB_DATABASE | gzip > "$DB_BACKUP/$FILENAME"
echo "COMPLETE: Operation finished at $(date +'%d-%m-%Y %H:%M:%S')" >> "$LOGFILE"
echo "Done"

exit 0<span style="line-height: 1.5;">

Here is a fancy page for validating shell-scripts:
http://www.shellcheck.net/

Test the script

# sh /bin/database_backup_script.sh

Automate the backup-process with cron:

# crontab -e

Put this in the crontab file:

00 03 * * * sh /bin/database_backup.sh >> /var/backup/cronBackup

(For more information about cron, see: https://www.centos.org/docs/5/html/Deployment_Guide-en-US/ch-autotasks.html – dead link)