Create automatic mysql database backup using cronjob, gzip and shell script in linux

MySQL which is the most popular and widely used opensource database. Many web applications are developed using the MySQL database. It's always a best practice to backup the your CMS or any custom developed web application database daily.

The three easy steps to configure the automatic backup in Linux is as follows,



STEP 1: Creating a Shell Script using VIM editor

vi db_backup.sh

in VIM: press "i" to start inserting below "db_backup.sh" shell script
in VIM: press "Esc" then ":wq"

db_backup.sh

FILENAME="$(date +"%Y%m%d"_db_bak.sql.gz)"
DATABASENAME="database_name"
DATABASEUSER="database_username"
DBPASSWORD="database_password"
mysqldump -u $DATABASEUSER -p$DBPASSWORD -B $DATABASENAME | gzip > /path/to/$FILENAME

STEP 2: Create and activate CRON job
List active CRON jobs

root@example.com:~$ crontab -l

Edit CRON jobs list

root@example.com:~$ crontab -e

m h dom mon dow command

• m - Minutes


• h - Hours (24 time)


• dom - Day of the Month


• mon - Month


• dow - Day of the week


• command - The command you want to run. This can contain spaces or point to a bash script.

1 3 * * * sh /path/to/db_backup.sh

STEP 3: Change folder permission

See the Shell script using

cat db_backup.sh

Check if the Shell script is working

sh db_backup.sh

go to the /path/to/ and see if the file is created


If the file is NOT created apply the 775 folder permission to the folder /path/to/

chmod -R 775 /path/to/*