Yuram has shown some keen interest in Bash scripting recently. He even took the Bash Cookbook from me to enrich his knowledge. To keep the spirit I often ping him with small challenges. While having a coffee at work today we discussed about a script to backup my databases on a daily basis. The script should backup the databases and send an email confirming the same has been done. At this point Yuram scratched his head & accepted the challenge. I also told him I’d publish the solution at night. Well, here we are with a small handy Bash script that backups MySQL/MariaDB databases & reports the listing via email.

Just specify a cronjob that calls the script daily.

#!/bin/bash
#
# MSave version 1.0 - MariaDB/MySQL database backup script
# Written by Ish Sookun <https://legacy.hacklog.in/about>
# Email : ishwon/@\openSUSE\./org
#
# This work is licensed under a Creative Commons Attribution 3.0 Unported License.
# License details at http://creativecommons.org/licenses/by/3.0.
#

DB_SAVE_PATH=/sites/DB/backup/
DATE=`date +"%d-%m-%y"`
MAIL=your@email.com

cd $DB_SAVE_PATH

mysqldump -u your_sql_user -pYOURPASS --all-databases | gzip -9 > all-databases-$DATE.sql.gz
printf "MariaDB databases backup complete\n\n`ls -lth`" | mail -s "MariaDB daily backup -- $DATE" $MAIL

####Update – 27/10/13

Following Jochen’s suggestion, here’s how we can eliminate the use of password in the script, thus minimizing security risk.

First we add the following lines in the MySQL/MariaDB configuration file /etc/my.cnf.

[mysqldump]
user=your_sql_user
password=your_password

You can now use mysqldump by simply specifying the user without the password. The final script can be modified as follows:

#!/bin/bash
#
# MSave version 1.1 - MariaDB/MySQL database backup script
# Written by Ish Sookun <https://legacy.hacklog.in/about>
# Email : ishwon/@\openSUSE\./org
#
# This work is licensed under a Creative Commons Attribution 3.0 Unported License.
# License details at http://creativecommons.org/licenses/by/3.0.
#

DB_SAVE_PATH=/sites/DB/backup/
DATE=`date +"%d-%m-%y"`
DB_ADM=your_sql_admin
MAIL=your@email.com

cd $DB_SAVE_PATH

mysqldump -u $DB_ADM --all-databases | gzip -9 > all-databases-$DATE.sql.gz
printf "MariaDB databases backup complete\n\n`ls -lth`" | mail -s "MariaDB daily backup -- $DATE" $MAIL

Type crontab -e and add a cronjob as follows:

# Daily MariaDB backup
30 2 * * *  /sites/DB/scripts/MSave_v1.sh