Basic MySQL Backup Script

A basic bash script to backup all databases a few times a day, and compress the backup file. It stores the backup file using the name mysql_DAYOFTHEWEEK.sql.gz…

#!/bin/bash

#
# Script to backup MySQL databases to a nas drive
# @author David C Boyce
# @created 2016-05-16
#

# Set up variables
FILE=/mnt/NASDRIVE/mysql_`date +"%A"`.sql
USER=root

# Remove the old GZ File
rm ${FILE}.gz 2> /dev/null

# Make the backup
nice mysqldump --user=${USER} --all-databases > ${FILE}

# gzip the backup file
nice gzip ${FILE}

The backup can be scheduled to run at 9.30, 12.30, 17:30 and 20:30 by adding the following to crontab…

30 9,12,17,20 * * * /locationofscript/nameofscript.sh >/dev/null 2>&1

There is a useful tool here to check/confirm what a crontab string will do…
http://www.dataphyx.com/cronsandbox/cronsandboxgui.php

Leave a Reply

Your email address will not be published. Required fields are marked *