将时间添加到mysqldump cron作业？

The following works:

以下作品:

/usr/bin/mysqldump -u[username] -p[password] --all-databases --single-transaction > /home/[domain]/public_html/backups/full_backup_`date -I`.sql

It results in "full_backup_2012-11-04.sql"

它导致“full_backup_2012-11-04.sql”

The cron job is going every minute but the filename is only for once a day... I wanted to include the hour and minute in the filename as well.... (in the end the cron job might be run every hour or so)

cron作业每分钟都在进行,但文件名只是每天一次...我想在文件名中包含小时和分钟....(最后cron作业可能每小时左右运行一次)

So date -I works... other typical options for date don't seem to work... is there any documentation that says -I is valid? If so that documentation might also have other options that work.

所以日期 - 我的工作......日期的其他典型选项似乎不起作用...是否有任何文档说 - 我是有效的?如果是这样,文档也可能有其他选项可行。

5 个解决方案

#1

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

date -Iminutes可以是使用ISO 8601格式的方式。 Wiki页面有一些信息。

#2

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

使用日期+%Y-%m-%d_%H-%M-%S代替(或任何其他格式):

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):

要避免来自CRON的电子邮件中的长主题,请使用创建文件/ home / <您的用户> /mycron.sh(文件名和位置只是一个示例):

#!/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.

确保你chmod + x / home / <你的用户> /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中使用脚本的名称,即:

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

#3

The date(1) command has a "+[FORMAT]" option. I believe the option you want are either the '%s' or '%N' options, which will give you seconds or nanoseconds respectively. From your path for MySQL, I'm going to assume you're on Linux. If this is indeed the case, you can find the manpage here which will give you further details.

date(1)命令有一个“+ [FORMAT]”选项。我相信你想要的选项是'%s'或'%N'选项,它们将分别给你秒或纳秒。从你的MySQL路径,我将假设你在Linux上。如果确实如此,您可以在此处找到可以为您提供更多详细信息的联机帮助页。

#4

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.

这将特别保留7天的备份。它的工作原理是使用星期几加上小时。所以它看起来像data-Mon-00.sql.gz(周一午夜)。当周滚动时,先前的备份将被覆盖。

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

如果您将cron设置为每6小时运行一次,则最多可以获得28个备份(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.

要使用mysqldump获得一致的备份,您必须锁定表,因此只能从副本或低容量服务器执行此操作。 mysqldump还会创建一个完整的快照,因此您最终会为每个备份创建一个完整的快照,最终可能会占用大量磁盘空间。对于大型数据库,这很快就会变得无法管理。您也可能不希望信任副本来进行备份,因为副本也必须保持同步。

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).

更好的选择是Percona的xtrabackup。它是开源的,所以它是免费的。它需要InnoDB表,并且能够在没有停机或锁定的情况下对主MySQL服务器进行热备份(无论如何都不应该使用MyISAM)。它使用改进的InnoDB引擎和InnoDB的崩溃恢复功能来确保备份的一致性。它甚至可以对基本快照进行增量备份,因此您可以进行数百次备份,并使其占用单个快照的大小。它适用于MySQL,MariaDB,PerconaDB(MySQL的分支),以及InnoDB和XtraDB(在MariaDB和Percona中都改进了InnoDB)。

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.

我个人会坚持使用xtrabackup,甚至没有使用mysqldump。您必须在命令行方面做同样多的工作,并且您还可以获得小增量备份的额外好处。有一些工具可以自动使用xtrabackup,即使在Galera集群中也是如此。 Facebook使用它。

---

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.

例如,下面的脚本采用“backup.sql.gz”,并将其转换为backup-13Nov2012-01_30.sql.gz,然后使用硬链接旋转文件。

#!/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 1
fi

if ! hash ln 2> /dev/null; then
        echo "-snapshot: ln: command not found" 1>&2
        exit 1
fi

# Date Info

month=$(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 date

if [ "$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")
fi

if [ "$dateFirst" == "" ]; then
        echo "-snapshot: Unknown version of date command." 1>&2
        exit 1
fi

# Directories

filepath=$1
backup=$2

if [ "$filepath" == "" ]; then
        echo "-snapshot: Expecting filename as first argument" 1>&2
        exit 1
fi

if [ "$backup" == "" ]; then
        backup=/backup
fi

if [ ! -d "$backup" ]; then
        echo "-snapshot: Directory "$backup" doesn't exist" 1>&2
        exit 1
fi

snapshots=$backup/snapshots
daily=$backup/daily
monthly=$backup/monthly
basename=${filepath##*/}
ext=${basename#*.}
basename=${basename%%.*}
filename=$basename-$date-$time.$ext

##############################
# Make new snapshot
##############################

if [ ! -d "$snapshots/$date" ]; then
        mkdir -p "$snapshots/$date"
fi

if [ -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"
        fi
fi

##############################
# Clean up old snapshots
##############################

if [ -d "$snapshots/$back2date" ]; then
        rm -fr "$snapshots/$back2date"
fi

if [ -d "$daily/$back2monthY" ]; then
        rm -fr "$daily/$back2monthY"
fi

if [ -d "$monthly/$back2year" ]; then
        rm -fr "$monthly/$back2year"
fi

#5

If you want to do it all in the crontab entry (and not a shell script), here's how:

如果你想在crontab条目中完成所有操作(而不是shell脚本),请按以下步骤操作:

/usr/bin/mysqldump -u[username] -p[password] --all-databases --single-transaction > /home/[domain]/public_html/backups/full_backup_`date +\%F_\%R`.sql

The filename will output as: full_backup_2017-08-18_17:52.sql

文件名将输出为:full_backup_2017-08-18_17:52.sql

The key change from your command is this: date +\%F_\%R

命令的主要变化是:date + \%F _ \%R

Note: The % in the date command must be escaped in crontab, otherwise you'll likely get an EOF error. It will work on the command line without escaping the % but won't in crontab. Hope this helps.

注意:必须在crontab中转义date命令中的%,否则您可能会收到EOF错误。它将在命令行上运行而不转义%但不会在crontab中。希望这可以帮助。