MySQL DB backup using Bash script

Backups are important for any data. Taking DB backups for MySQL/MariaDB is a cinch with the following script.

Bash script

Create a file called backup_script.sh. Replace the credentials and path to suit your system.

#!/bin/bash

USER='username'
PASSWORD='password'
DATABASE='ALL_DB'
# Directory should always be mentioned with a trailing slash
DIRECTORY='/path/for/db_backup/'
DOW=$(date +"%A")

# Delete backups which are 7 days old
rm -f $DIRECTORY$DATABASE-$DOW.sql.gz > /dev/null 2>&1

#Backup the DB and Compress it
/usr/bin/mysqldump --user=$USER --password=$PASSWORD --host=$HOST --all-databases --skip-lock-tables > $DIRECTORY$DATABASE-$DOW.sql
/bin/gzip $DIRECTORY$DATABASE-$DOW.sql

The script will create/keep in total 7 backups, with the day of the week as its suffix. Escape $ sign if its used in the PASSWORD variable.

Provide execute permission to the script
$ chmod 755 backup_script.sh
Make sure to verify the backup-file is generated by executing the script:
$ ./backup_script.sh

CRON Job

Add the following line to your crontab. This will automatically execute a backup daily at 0400 hours.
0 4 * * * /bin/sh /path/to/backup_script.sh >> /dev/null 2>&1

Comments

[Andreas Treichel]

If you use --result-file instead of the output redirection to may prevent problems with the encoding of your shell.

[KM]

Thanks for the insight :)

[AATHITH RAJENDRAN]

storing the password inside the script?😲, that's not safe right?