The database is a critical part of any application and we often need to keep the backup of the database on regular basis to avoid any crisis. In this short tutorial, I’ll explain you how to schedule a backup of a MySQL database on a Linux system.
Steps to create shell script and scheduling using crontab:
1. Choose or create a directory where your DB files will be stored. Here I am using /var/www/backup directory to store the DB backup.
2. Create a shell file dbdump.sh and add the mysqldump script in that file. Please remember to add the absolute path of the output file in the mysqldump script. In this script root is the username and dbpwd is db password. In this script, we have also added timestamp for the filename.
# mysqldump -u root -pdbpwd dbname>/var/www/backup/dbname-`date '+%Y-%m-%d'`.sql
3. Once this file is created, we need to make this file executable. Please execute following command to make dbdump.sh file executable. The script won’t work if you change its mode to executable.
# chmod +x dbdump.sh
4. Our next task is to setup a cronjob that will execute our shell script on regular basis. Open crontab in edit mode and the following script in the file. Here are we are scheduling our script once in a day.
# Open crontab in edit mode
crontab -e
# Crontab script to schedule the shell script
0 0 * * * sh /var/www/backup/dbdump.sh
===========================================================================
