MySQL/MariaDB database daily backup

October 24, 2013
bash database mariadb

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://hacklog.mu/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"`
[email protected]

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://hacklog.mu/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
[email protected]

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

InnoDB Error Log File is of Different Size

May 19, 2013
mysql database

Bash – for loop

May 12, 2013
linux bash programming

MariaDB migration

May 11, 2013
database mariadb