Backup all MySQL databases into seperate files with crontab

Published: 13 years ago

It's highly recommended to keep a backup of all your data and with the following script you can setup an automated task to create a weekly backup of your data without having to manually run it; with linux!

The Shell Script "db-backup.sh"

# Backup mysql databases into seperate files
USER="mysql-username"
PASSWORD="mysql-password"
OUTPUTDIR="/path/to/db/backup/dir"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"
NOW=$(date +"%m-%d-%Y")
MSG="Just to let you know that a full backup of db has been made onto the VPS.Kind Regards"
# Remove previous backups
rm "$OUTPUTDIR/*bak" > /dev/null 2>&1
# get a list of databases
databases=`$MYSQL --user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
# dump each database in turn
for db in $databases; do
echo $db
$MYSQLDUMP --force --opt --user=$USER --password=$PASSWORD --databases $db > "$OUTPUTDIR/$db.bak"
done
# emaili reminder of backup
echo $MSG | mail -s "VPS Server Backup - Backup Created on $NOW" your@emailaddress.co.uk
view raw dailyBackup.sh hosted with ❤ by GitHub

Next Step Add to Crontab

In order for the script that we have created to be executed automatically we need to add this to the crontab task scheduler.

View existing crontab scheduled tasks

1
crontab -l

Edit the crontabs

1
crontab -e

Normally you specify the exact minute, hour, day, week, month with the either with an asteric character * (which means every) or a numeric character (which represents moments in time

The crontab also has special keywords which are predfined values

string         meaning
------         -------
@reboot        Run once, at startup.
@yearly        Run once a year, "0 0 1 1 *".
@annually      (same as @yearly)
@monthly       Run once a month, "0 0 1 * *".
@weekly        Run once a week, "0 0 * * 0".
@daily         Run once a day, "0 0 * * *".
@midnight      (same as @daily)
@hourly        Run once an hour, "0 * * * *".
@daily

Add the backup script to the crontab

1
@weekly /path/to/backup-script.sh

:?>