mysqldump &gzip 命令使用 crontab 正确创建 MySQL 数据库的压缩文件 [英] mysqldump & gzip commands to properly create a compressed file of a MySQL database using crontab

问题描述

我在使用 crontab 时遇到问题.我想自动化 MySQL 数据库备份.

I am having problems with getting a crontab to work. I want to automate a MySQL database backup.

设置:

• Debian GNU/Linux 7.3(喘气)
• MySQL 服务器版本:5.5.33-0+wheezy1(Debian)
• 目录 user、backup 和 backup2 有 755 权限
• MySQL db 和 Debian 帐户的用户名相同

从 shell 这个命令起作用

From the shell this command works

mysqldump -u user -p[user_password] [database_name] | gzip > dumpfilename.sql.gz

当我使用 crontab -e 将它放在 crontab 中时

When I place this in a crontab using crontab -e

* * /usr/bin/mysqldump -u user -pupasswd mydatabase | gzip> /home/user/backup/mydatabase-backup-`date +\%m\%d_\%Y`.sql.gz >/dev/null 2>&1

每分钟在/home/user/backup 目录中创建一个文件，但有 0 个字节.

A file is created every minute in /home/user/backup directory, but has 0 bytes.

但是，当我将此输出重定向到第二个目录 backup2 时，我注意到在其中创建了适当压缩的正确 mysqldumpfile.我无法弄清楚我犯了什么错误，导致第一个目录中的文件为 0 字节，而第二个目录中的输出为预期.

However when I redirect this output to a second directory, backup2, I note that the proper mysqldumpfile duly compressed is created in it. I am unable to figure what is the mistake that I am making that results in a 0 byte file in the first directory and the expected output in the second directory.

* * /usr/bin/mysqldump -u user -pupasswd my-database | gzip> /home/user/backup/mydatabase-backup-`date +\%m\%d_\%Y`.sql.gz >/home/user/backup2/mydatabase-backup-`date +\%m\%d_\%Y`.sql.gz 2>&1

非常感谢您的解释.

谢谢

推荐答案

首先执行 mysqldump 命令并使用管道重定向生成的输出.管道将标准输出作为标准输入发送到 gzip 命令中.在 filename.gz 之后，是输出重定向操作符 (>)，它将继续重定向数据，直到最后一个文件名，这是数据将被保存的位置.

First the mysqldump command is executed and the output generated is redirected using the pipe. The pipe is sending the standard output into the gzip command as standard input. Following the filename.gz, is the output redirection operator (>) which is going to continue redirecting the data until the last filename, which is where the data will be saved.

比如这个命令会转储数据库并通过gzip运行它，数据最终会落在3.gz中

For example, this command will dump the database and run it through gzip and the data will finally land in three.gz

mysqldump -u user -pupasswd my-database | gzip > one.gz > two.gz > three.gz

$> ls -l
-rw-r--r--  1 uname  grp     0 Mar  9 00:37 one.gz
-rw-r--r--  1 uname  grp  1246 Mar  9 00:37 three.gz
-rw-r--r--  1 uname  grp     0 Mar  9 00:37 two.gz

我的原始答案是将数据库转储重定向到许多压缩文件(不进行双重压缩)的示例.(因为我扫描了问题并严重错过了 - 抱歉)

My original answer is an example of redirecting the database dump to many compressed files (without double compressing). (Since I scanned the question and seriously missed - sorry about that)

这是一个重新压缩文件的例子:

This is an example of recompressing files:

mysqldump -u user -pupasswd my-database | gzip -c > one.gz; gzip -c one.gz > two.gz; gzip -c two.gz > three.gz

$> ls -l
-rw-r--r--  1 uname  grp  1246 Mar  9 00:44 one.gz
-rw-r--r--  1 uname  grp  1306 Mar  9 00:44 three.gz
-rw-r--r--  1 uname  grp  1276 Mar  9 00:44 two.gz

这是一个很好的解释 I/O 重定向的资源:http://www.codecoffee.com/tipsforlinux/articles2/042.html

This is a good resource explaining I/O redirection: http://www.codecoffee.com/tipsforlinux/articles2/042.html

这篇关于mysqldump &gzip 命令使用 crontab 正确创建 MySQL 数据库的压缩文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！