MySQL Dump cronjob on linux server

Creating mysql dump for production databases is very important. In case of any failure or malefic attack on DB, The only way to restore to previous state is by having proper mysql dump file. Follows an example mysql dump shell script with auto naming with dumping time stamp. Its important to create the shell scripts in unix mode, if you create on a PC, It'll add some unwanted character encoding, and it'll lead to some erroneous response on the script.

#!/bin/bash
today=`date +%Y%m%d%H%M`
backup="cmtdbbackup-$today.sql"
mysqldump -u user -ppassword mydb > /apps/mysql_dump/$backup

Its important to create the shell script carefully. No spaces, unless its required. in between -u and user name, there is a space, but no space between -p and password. As per the usage of system, we can decide on which interval the cronjob could be set. normally the format of a crontab entry is like the following.

* * * * * /apps/scripts/scheduled_job.sh >> /dev/null 2>&1

 Here the first 5 portions are the timing interval markers (Minute (0-59), Hour (2-24), Day of month (1-31), Month (1-12, Jan, Feb, etc), Day of week (0-6) 0 = Sunday, 1 = Monday etc or Sun, Mon etc). next to those is the script to run. The '>> /dev/null 2>&1' part means to send any standard output to /dev/null (the linux trash can) and to redirect standard error (2) to the same place as the standard output (1). Basically it runs the command without any output to a terminal.

If you want to run the script for some specific times only, lets say you want to run it on 4 pm and 8 pm, the timers looks like the following

0 16,20 * * *

if you want to run the script only in week days, the timers will be look like the following.

0 16,20 * * 1,2,3,4,5

If you run more than one script, better give them as seperate jobs.