Adding the time to mysqldump cron job?

Use date +%Y-%m-%d_%H-%M-%S instead (or any other format):

EDITED

To avoid long subjects in emails from CRON, use create a file /home/<your user>/mycron.sh (file name and location is just an example):

#!/bin/sh/usr/bin/mysqldump -u[username] -p[password] --all-databases --single-transaction > /home/[domain]/public_html/backups/full_backup_`date +%Y-%m-%d_%H-%M-%S`.sql

Make sure you chmod +x /home/<your user>/mycron.sh.

This date format will output:

full_backup_2012-11-05_08-49-19.sql

Then use in your cron the name of the script, i.e:

[cron parameters]    /home/<your user>/mycron.sh

date -Iminutes could be the way to go, which uses ISO 8601 format.The Wiki page has some info.

While my original script works, something like this may be far simpler:

mysqldump [options] | gzip -c > /backups/data-$(date +%a-%H00).sql.gz

This specifically will keep 7 days worth of backups. It works by using the day of the week plus the hour. So it would look like data-Mon-00.sql.gz (Monday at midnight). When the week rolls around, the previous backups get overwritten.

If you set your cron to run every 6 hours you will get up to 28 backups (24/6) * 7 = 28.

To get a consistent backup with mysqldump you have to lock the tables, so this should only be done from a replica, or a low-volume server. mysqldump also makes a full snapshot, so you end up having a full snapshot for each backup, which can end up taking up a lot of disk space. This can quickly become unmanageable with a large database. You also may not want to trust a replica to take backups from as the replica also has to stay in sync.

A better option is xtrabackup by Percona. It's open source, so it's free. It requires InnoDB tables and is able to take a hot backup of your primary MySQL server without downtime or locks (you shouldn't be using MyISAM anyway). It uses a modified InnoDB engine with InnoDB's crash recovery feature to ensure the backups are consistent. It even takes incremental backups of a base snapshot, so you can make hundreds of backups and have it take up the size of a single snapshot. It works with MySQL, MariaDB, PerconaDB (forks of MySQL), and both InnoDB and XtraDB (improved InnoDB in both in MariaDB and Percona).

Personally I would stick with xtrabackup and not even bother with mysqldump. You have to do just as much work command-line wise, and you have the added benefit of small incremental backups. There are tools out there that automate the use of xtrabackup, even in a Galera cluster. Facebook uses it.

Original script:

The script below takes "backup.sql.gz", for example, and turns it into something like backup-13Nov2012-01_30.sql.gz, then rotates files using hard links.

#!/bin/bash############################################################################ snapshot##     Rotates snapshots of backups using hard links##     Keeps track of:#          - 48 hours of snapshots (48*60/interval)#          - 60 days worth of midnight snapshots#          - 24 months of snapshots from the 1st#     Ussage:#          snapshot /path/to/backup.sql.gz#          mysqldump ... | gzip -c | snapshot /path/to/backup.sql.gz############################################################################if ! hash date 2> /dev/null; then        echo "-snapshot: date command not found" 1>&2        exit 1fiif ! hash ln 2> /dev/null; then        echo "-snapshot: ln: command not found" 1>&2        exit 1fi# Date Infomonth=$(date +"%b")day=$(date +"%d")year=$(date +"%Y")time=$(date +"%H_%M")date=$(date +"%d%b%Y")dateFirst=$(date --date="$month 01" +"%d%b%Y" 2> /dev/null)# Test to see if we're using GNU date or BSD dateif [ "$dateFirst" == "" ]; then        dateFirst=$(date -v1d +"%d%b%Y")        back2date=$(date -v-2d +"%d%b%Y")        back2monthY=$(date -v-2m +"%b%Y")        back2year=$(date -v-2y +"%Y")else        back2date=$(date --date="-2 day" +"%d%b%Y")        back2monthY=$(date --date="-2 month" +"%b%Y")        back2year=$(date --date="-2 year" +"%Y")fiif [ "$dateFirst" == "" ]; then        echo "-snapshot: Unknown version of date command." 1>&2        exit 1fi# Directoriesfilepath=$1backup=$2if [ "$filepath" == "" ]; then        echo "-snapshot: Expecting filename as first argument" 1>&2        exit 1fiif [ "$backup" == "" ]; then        backup=/backupfiif [ ! -d "$backup" ]; then        echo "-snapshot: Directory "$backup" doesn't exist" 1>&2        exit 1fisnapshots=$backup/snapshotsdaily=$backup/dailymonthly=$backup/monthlybasename=${filepath##*/}ext=${basename#*.}basename=${basename%%.*}filename=$basename-$date-$time.$ext############################### Make new snapshot##############################if [ ! -d "$snapshots/$date" ]; then        mkdir -p "$snapshots/$date"fiif [ -t 0 ]; then        if [ ! -f "$filepath" ]; then                echo "-snapshot: '$filepath' doesn't exist" 1>&2                exit 1        fi    ln "$filepath" "$snapshots/$date/$filename"else    cat > "$snapshots/$date/$filename"fi############################### Daily/monthly snapshots##############################if [ "$time" == "00_00" ]; then        if [ ! -d "$daily/$month$year" ]; then                mkdir -p "$daily/$month$year"        fi        ln "$snapshots/$date/$filename" "$daily/$month$year/$filename"        if [ "$day" == "01" ]; then                if [ ! -d "$monthly/$year" ]; then                        mkdir -p "$monthly/$year"                fi                ln "$snapshots/$date/$filename" "$monthly/$year/$filename"        fifi############################### Clean up old snapshots##############################if [ -d "$snapshots/$back2date" ]; then        rm -fr "$snapshots/$back2date"fiif [ -d "$daily/$back2monthY" ]; then        rm -fr "$daily/$back2monthY"fiif [ -d "$monthly/$back2year" ]; then        rm -fr "$monthly/$back2year"fi