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