14

Hi i want to take database backup at daily mid night using cron job... and the name of database backup should append with current date... the format of backup file should be mydata_yyyy_mm_dd.sql ... backup file should be placed in /root directory

Hussy
  • 2,039
  • 4
  • 25
  • 32

2 Answers2

22

something like

0 0 * * * /path/to/mysqldump ... > /path/to/backup/mydata_$( date +"%Y_%m_%d" ).sql

should work.

Please read

  • man date
  • man 5 crontab
wonk0
  • 13,402
  • 1
  • 21
  • 15
  • 8
    I had to add escaping slashes to the `%`, as `%` is often interpreted by cron as a newline character. E.g., `... /path/to/backup/mydata_$( date +"\%Y_\%m_\%d" )`. – Nick Merrill Jun 06 '13 at 15:44
  • centos 6.4 is ok. didn't need the escapes – Reza S Oct 25 '13 at 22:19
  • @wonk0 please guide me regarding this. http://stackoverflow.com/questions/33057744/send-email-via-cron-job-every-day-with-database-csv-xls-or-xml-file-backup-usi – Muddasir Abbas Oct 10 '15 at 19:05
7

Create a cron.sh file with this content:

 mysqldump -u root -p{PASSWORD} DBNAME 2>> "/filename_`date '+%Y-%m-%d'`.sql"

And give the Read permission or full access permission for that cron.sh file.

and add this line into crontab file ($ crontab -e) 

 0 0 * * *   cron.sh
KumarA
  • 1,368
  • 3
  • 18
  • 41