42

I'm setting up a Cronjob that will backup a MySQL database I have in my server, but I don't want it to keep overwriting the same file over and over again. Instead, I want to have an array of backups to choose from, done automatically. For example:

## Cronjob, run May 21st, 2011:
mysqldump -u username -ppasword database > /path/to/file/21-03-2011.sql

## SAME Conjob, run May 28th, 2011:
mysqldump -u username -ppasword database > /path/to/file/28-03-2011.sql

And so on.

Is there any way that I can use the system date and/or time as some kind of variable in my Cronjob? If not, what are your suggestions to accomplish the same?

Improve this question

5 Answers 5

Reset to default
51

You could try something like this (as glenn jackmann notes below, you have to escape all % characters):

15 11 * * * touch "/tmp/$(date +\%d-\%m-\%Y).sql"

To see if your particular cron will run the command out of crontab as a script in and of itself, or if you need to write a script that figures out the date as a string, and then runs your mysqldump command.

Without escaping the %, "cron" on Redhat Enterprise Linux 5.0 (I think) kept giving me errors about not finding a matching ). This is because everything after an unescaped % is sent to standard input of the command.

I would also take the recommendation to use ISO8601 date format (yyyy-mm-dd, which is %F) to make the file names order by date when sorted lexically.

Improve this answer
4
  • 25
    Have to be careful with date inside a cronfile: some crons (all?) treat % as the end of the command. (so the $() was not the problem). You have to escape all percent signs: ... touch "/tmp/$(date +\%Y-\%m-\%d)" (nicer to use a date format that sorts lexicographically)
    – glenn jackman
    Mar 4, 2011 at 18:36
  • 2
    glenn jackman is correct: escaping the '%' characters in the crontab entry above works. A RHEL 5.0 crontab entry looks like this:50 11 * * * touch "/tmp/backup.$(date +\%Y-\%m-\%d).sql"
    – user732
    Mar 4, 2011 at 19:11
  • It's worth noting that using a subshell doesn't work in an environment variable. So, DATE=$( date -I ) and then using ${DATE} later on results in using a literal $( date -I ) in the job's command-line.
    – Christopher Schultz
    Apr 7, 2016 at 18:17
  • 1
    It seems that escaping of % is necessary on OpenSuse 42 as well.
    – kgadek
    Apr 18, 2016 at 15:07
8

You should be able to use date.
Type info date or man date for details.

Something like the following might suit you (change the date format to your needs)

yourcommand > filepathandnameprefix$(date +"\%d-\%m-\%Y").extension
Improve this answer
2
  • Again, be careful to escape all % chars inside cron.
    – glenn jackman
    Mar 4, 2011 at 18:37
  • @glenn: Oops, sure. As to the lexicographic order, I just matched the OP's date format. I personally like the ISO format, like you.
    – asoundmove
    Mar 4, 2011 at 18:55
4

In order to have such a command running appropriately we will have to escape % and then it will run as expected.

See: http://www.ducea.com/2008/11/12/using-the-character-in-crontab-entries/

Improve this answer
1

Here's the bash script I used:

#!/bin/bash
mysqldump -u user1 -p DatabaseName | gzip > BackupFolder/backup`date +%F_%T`.sql.gz

Files look like:

backup2011-03-02_15:16:46.sql.gz

Point the cron job at this to run nightly or whatever you prefer.

Improve this answer
3
  • This works like a charm. Although, I don't quite understand what's happening in here. Does the backticks allow inclusion of the output of other programs or something? What about the formatting (like +%F and such)?
    – AeroCross
    Mar 6, 2011 at 19:25
  • 1
    Yep the backticks perform command substitution where the output is substituted for the command itself. Refer to Section 3.4.5 Here is a more comprehensive list of modifiers for data link
    – user5319
    Mar 11, 2011 at 20:28
  • Oh, wow, that's an amazing resource. It's great to always keep learning. Thank you very much for the link!
    – AeroCross
    Mar 11, 2011 at 21:23
0

Write a small wrapper script that utilizes the date command and calls your backup command.

#!/bin/bash
NOW=`/bin/date +"%m%d%Y-%H%M%S"`
if [[ "$?" != "0" ]]; then
 NOW="UNKNOWN_DATE"
 fi
mysqldump -u username -ppassword database > /path/to/file/$NOW.sql
if [[ "$?" != "0" ]]; then
 echo "$0: backup failed with error code $?"
 fi
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .