MySQL mysqldump 在 crontab 中不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19731148/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysqldump doesn't work in crontab
提问by xspecial
I'm trying to add a cronjob in the crontab (ubuntu server) that backups the mysql db.
我正在尝试在备份 mysql 数据库的 crontab（ubuntu 服务器）中添加一个 cronjob。
Executing the script in the terminal as root works well, but inserted in the crontab nothing happens. I've tried to run it each minutes but no files appears in the folder /var/db_backups.
以 root 身份在终端中执行脚本效果很好，但插入到 crontab 中没有任何反应。我试图每分钟运行一次，但文件夹 /var/db_backups 中没有出现任何文件。
(Other cronjobs work well)
（其他 cronjob 运行良好）
Here is the cronjob:
这是cronjob：
* * * * * mysqldump -u root -pHERE THERE IS MY PASSWORD --all-databases | gzip > /var/db_backups/database_`date +%d%m%y`.sql.gz
* * * * * mysqldump -u root -pHERE 这是我的密码 --all-databases | gzip > /var/db_backups/database_`date +%d%m%y`.sql.gz
what can be the problem?
可能是什么问题？
回答by The Java Guy
You need to escape % character with \
您需要使用 \ 转义 % 字符
mysqldump -u 'username' -p'password' DBNAME > /home/eric/db_backup/liveDB_`date +\%Y\%m\%d_\%H\%M`.sql
回答by adam187
Check cron logs (should be in /var/log/syslog) You can use grep to filter them out.
检查 cron 日志（应该在 /var/log/syslog 中）您可以使用 grep 将它们过滤掉。
grep CRON /var/log/syslog
grep CRON /var/log/syslog
Also you can check your local mail box to see if there are any cron mails
您也可以检查您的本地邮箱以查看是否有任何 cron 邮件
/var/mail/username
/var/邮件/用户名
You can also set up other receiving mail in you crontab file
你也可以在你的 crontab 文件中设置其他接收邮件
回答by Krupal Patel
I was trying the same but I found that dump was created with 0KB. Hence, I got to know about the solution which saved my time.
我正在尝试相同的方法，但我发现转储是用 0KB 创建的。因此，我了解了节省我时间的解决方案。
Command :
命令 ：
0 0 * * * mysqldump -u 'USERNAME' -p'PASSWORD' DATEBASE > /root/liveDB_`date +\%Y\%m\%d_\%H\%M\%S`.sql
NOTE:1) You can change the time setting as per your requirement. I have set every day in above command.
注意：1) 您可以根据需要更改时间设置。我每天都在上面的命令中设置。
2) Make sure you enter your USERNAME, PASSWORD, and DATABASE inside single quote (').
2) 确保在单引号 (') 内输入您的用户名、密码和数据库。
3) Write down above command in Crontab.
3) 在 Crontab 中写下上面的命令。
I hope this helps someone.
我希望这可以帮助别人。
回答by alditis
Alternatively you can create a custom command mycommand. To which you can add more options. You must give execute permissions.
或者，您可以创建自定义命令mycommand。您可以添加更多选项。您必须授予执行权限。
It is preferable to have a folder where they store all your backups, in this case using a writable folder "backup" which first create in "your home" for example.
最好有一个文件夹来存储您的所有备份，在这种情况下，使用可写文件夹“备份”，例如首先在“您的家”中创建的文件夹。
My commandin "usr/local/bin/mycommand":
我在“usr/local/bin/mycommand”中的命令：
#!/bin/bash
MY_USER="your_user"
MY_PASSWORD="your_pass"
MY_HOME="your_home"
case in
"backupall")
cd $MY_HOME/backup
mysqldump --opt --password=$MY_PASSWORD --user=$MY_USER --all-databases > bckp_all_$(date +%d%m%y).sql
tar -zcvf bckp_all_$(date +%d%m%y).tgz bckp_all_$(date +%d%m%y).sql
rm bckp_all_$(date +%d%m%y).sql;;
*) echo "Others";;
esac
Cron:Runs the 1st day of each month.
Cron：在每个月的第一天运行。
0 0 1 * * /usr/local/bin/mycommand backupall
I hope it helps somewhat.
我希望它有所帮助。
回答by niroshan.l
Ok, I had a similar problem and was able to get it fixed.
好的，我遇到了类似的问题，并且能够修复它。
In your case you could insert that mysqldump command to a script then source the profile of the user who is executing the mysqldump command for eg:
在您的情况下，您可以将该 mysqldump 命令插入脚本，然后获取执行 mysqldump 命令的用户的配置文件，例如：
. /home/bla/.bash_profile
then use the absolute path of the mysqldump command
然后使用mysqldump命令的绝对路径
/usr/local/mysql/bin/mysqldump -u root -pHERE THERE IS MY PASSWORD --all-databases | gzip > /var/db_backups/database_`date +%d%m%y`.sql.gz
回答by user3277973
Create a new file and exec the code there to dump into a file location and zip it . Run that script via a cron
创建一个新文件并在那里执行代码以转储到文件位置并压缩它。通过 cron 运行该脚本
回答by u4960473
Local Host mysql Backup: 0 1 * * * /usr/local/mysql/bin/mysqldump -uroot -ppassword --opt database > /path/to/directory/filename.sql
本地主机 mysql 备份：0 1 * * * /usr/local/mysql/bin/mysqldump -uroot -ppassword --opt 数据库 > /path/to/directory/filename.sql
(There is no space between the -p and password or -u and username - replace root with a correct database username.)
（ -p 和密码或 -u 和用户名之间没有空格 - 用正确的数据库用户名替换 root 。）
It works for me. no space between the -p and password or -u and username
这个对我有用。-p 和密码或 -u 和用户名之间没有空格
回答by Shi
I am using Percona Server(a MySQL fork) on Ubuntu. The package (very likely the regular MySQL package as well) comes with a maintenance account called debian-sys-maint. In order for this account to be used, the credentials are created when installing the package; and they are stored in /etc/mysql/debian.cnf.
我在 Ubuntu 上使用Percona Server（一个 MySQL 分支）。该软件包（也很可能是常规 MySQL 软件包）带有一个名为debian-sys-maint. 为了使用此帐户，在安装软件包时会创建凭据；它们存储在/etc/mysql/debian.cnf.
And now the surprise: A symlink /root/.my.cnfpointing to /etc/mysql/debian.cnfgets installed as well.
现在令人惊讶的是：/root/.my.cnf指向的符号链接也/etc/mysql/debian.cnf被安装了。
This file is an option fileread automatically when using mysqlor mysqldump. So basically you then had login credentials given twice - in that file and on command line. This was the problem I had.
此文件是使用或时自动读取的选项文件。所以基本上你有两次登录凭据 - 在该文件和命令行中。这是我遇到的问题。mysqlmysqldump
So one solution to avoid this condition is to use --no-defaultsoption for mysqldump. The option file then won't be read. However, you provide credentials via command line, so anyone who can issue a pscan actually see the password once the backup runs. So it's best if you create an own option file with user name and password and pass this to mysqldumpvia --defaults-file.
因此，避免这种情况的一种解决方案是使用--no-defaultsoption for mysqldump。选项文件将不会被读取。但是，您通过命令行提供凭据，因此ps一旦备份运行，任何可以发出密码的人实际上都可以看到密码。因此，最好使用用户名和密码创建自己的选项文件并将其传递给mysqldumpvia --defaults-file。
You can create the option file by using mysql_config_editoror simply in any editor.
您可以使用mysql_config_editor或简单地在任何编辑器中创建选项文件。
Running mysqldumpvia sudofrom the command line as rootworks, just because sudousually does not change $HOME, so .my.cnfis not found then. When running as a cronjob, it is.
从命令行运行mysqldumpvia正常工作，只是因为通常不会改变，所以没有找到。当作为 cronjob 运行时，它是。sudorootsudo$HOME.my.cnf
回答by maomoa
You might also need to restart the service to load any of your changes.
您可能还需要重新启动服务以加载您的任何更改。
service cron restart
or
或者
/etc/init.d/cron restart
