MySQL/MariaDB Backup Script
Sep 6, 2016

MySQL

Set the MySQL username/password and backup path.

Web

The WWW_PATH variable is optional. If you want to only backup MySQL databases then the variable can be removed.

S3

The s3cmd command below is set to delete removed files. This means that any files inside the bucket will be deleted if they do not exist on disk. S3 is optional. Remove the S3_BUCKET variable and it will not execute the s3cmd code.

Script

Old backups are automatically deleted after 30 days

#!/bin/bash
USER=USERNAME
PASSWORD=PASSWORD
BACKUP_PATH=/root/backup
WWW_PATH=/var/www
DATE=`date +"%H-%M-%S_%d-%m-%Y"`
DAYS=30
DATABASES=`mysql -u $USER -p$PASSWORD --batch --skip-column-names -e "SHOW DATABASES;" | grep -E -v "(information|performance)_schema"`

S3_BUCKET=S3_BUCKET_NAME

if [ ! -d $BACKUP_PATH ]; then
	mkdir -p $BACKUP_PATH
fi

if [ -d $BACKUP_PATH ]; then
	if [ ! -z $WWW_PATH ] && [ -d $WWW_PATH ]; then
		tar -jcf $BACKUP_PATH/www-$DATE.tar.bz2 $WWW_PATH
	fi

	for db in $DATABASES; do
		mysqldump -u $USER -p$PASSWORD --skip-lock-tables $db | bzip2 -c > $BACKUP_PATH/$db-$DATE.sql.bz2
	done

	find $BACKUP_PATH/* -mtime +$DAYS -type f -delete

	if [ ! -z $S3_BUCKET ]; then
		s3cmd sync --delete-removed $BACKUP_PATH/ s3://$S3_BUCKET/
	fi
fi

Cron

  • Every 6 hours.
    • 0 */6 * * * /root/run_backup
  • Every day.
    • 0 0 * * * /root/run_backup

Output

total 155512
-rw-r--r-- 1 root root    106638 Sep  7 14:40 mysql-14-39-18_07-09-2016.sql.bz2
-rw-r--r-- 1 root root   1409334 Sep  7 14:40 xxxxxxxx-14-39-18_07-09-2016.sql.bz2
-rw-r--r-- 1 root root  32935172 Sep  7 14:40 xxxxxxxx-14-39-18_07-09-2016.sql.bz2
-rw-r--r-- 1 root root 124781044 Sep  7 14:40 www-14-39-18_07-09-2016.tar.bz2
backup Related
Comments