no crontab for mysql_mysqldump不能在crontab中工作

I'm trying to add a cronjob in the crontab (ubuntu server) that backups the mysql db.

我正在嘗試在crontab (ubuntu服務器)中添加一個cronjob來備份mysql db。

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_backup文件夾中沒有出現任何文件。

(Other cronjobs work well)

(其他的計划工作)

Here is the 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有我的密碼——所有數據庫| gzip > /var/db_backups/database_ ' date +%d%m%y ' .sql.gz

what can be the problem?

有什么問題嗎?

8 个解决方案

#1

41

You need to escape % character with \

您需要用\來轉義%字符

mysqldump -u 'username' -p'password' DBNAME > /home/eric/db_backup/liveDB_`date +\%Y\%m\%d_\%H\%M`.sql

#2

2

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/mail/username

You can also set up other receiving mail in you crontab file

您還可以在crontab文件中設置其他接收郵件

MAILTO=your@mail.com

MAILTO = your@mail.com

#3

1

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.

最好使用一個文件夾來存儲所有備份，在這種情況下，最好使用一個可寫的文件夾“backup”，該文件夾首先在“your home”中創建。

My command in "usr/local/bin/mycommand":

我的命令在“當地/ usr / bin / mycommand”:

#!/bin/bash

MY_USER="your_user"

MY_PASSWORD="your_pass"

MY_HOME="your_home"

case $1 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.

克倫:每個月的第一天。

0 0 1 * * /usr/local/bin/mycommand backupall

I hope it helps somewhat.

我希望這能有所幫助。

#4

0

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運行該腳本

#5

0

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命令插入到腳本中，然后為eg執行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

#6

0

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服務器(MySQL fork)。這個包(很可能也是普通的MySQL包)帶有一個名為debian-sys-maint的維護帳戶。為了使用該帳戶，在安裝包時創建憑據;它們存儲在/etc/mysql/debian.cnf中。

And now the surprise: A symlink /root/.my.cnf pointing to /etc/mysql/debian.cnf gets installed as well.

現在，令人驚訝的是:一個指向/etc/mysql/debian.cnf的符號鏈接/root/.my.cnf也被安裝。

This file is an option file read automatically when using mysql or mysqldump. So basically you then had login credentials given twice - in that file and on command line. This was the problem I had.

此文件是使用mysql或mysqldump時自動讀取的選項文件。基本上，你有兩次登錄憑證-在那個文件和命令行。這就是我遇到的問題。

So one solution to avoid this condition is to use --no-defaults option for mysqldump. The option file then won't be read. However, you provide credentials via command line, so anyone who can issue a ps can 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 mysqldump via --defaults-file.

因此，避免這種情況的一種解決方案是對mysqldump使用-no-defaults選項。然后將不會讀取選項文件。但是，您可以通過命令行提供憑據，這樣任何能夠發出ps的人都可以在備份運行后實際看到密碼。因此，最好使用用戶名和密碼創建一個自己的選項文件，並將其傳遞給mysqldump通過——default -file。

You can create the option file by using mysql_config_editor or simply in any editor.

可以使用mysql_config_editor或任何編輯器創建選項文件。

Running mysqldump via sudo from the command line as root works, just because sudo usually does not change $HOME, so .my.cnf is not found then. When running as a cronjob, it is.

通過sudo從命令行運行mysqldump作為根工作，因為sudo通常不會更改$HOME，所以那時不會找到.my.cnf。當作為cronjob運行時，它是。

#7

0

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.

我嘗試了相同的方法，但是我發現dump是用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.

我希望這能幫助某人。

#8

-1

You might also need to restart the service to load any of your changes.

您可能還需要重新啟動服務來加載任何更改。

service cron restart

or

或

/etc/init.d/cron restart