Thursday, January 31, 2013

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/*

3 comments:

  1. error:sh: 0: Can't open mysql-test-db-shell-script.sh

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. please mention step by step not much clear

    path/to is no clear ,please reply me and help

    ReplyDelete