mysqldump doesn't work in crontab

Question

I'm trying to add a cronjob in the crontab (ubuntu server) that backups the 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.

(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

what can be the problem?

Solution 1

You need to escape % character with \

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

Solution 2

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.

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.

2) Make sure you enter your USERNAME, PASSWORD, and DATABASE inside single quote (').

3) Write down above command in Crontab.

I hope this helps someone.

Solution 3

Check cron logs (should be in /var/log/syslog) You can use grep to filter them out.

grep CRON /var/log/syslog

Also you can check your local mail box to see if there are any cron mails

/var/mail/username

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

[email protected]

Solution 4

Alternatively you can create a custom command mycommand. To which you can add more options. You must give execute permissions.

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 command in "usr/local/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.

Solution 5

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:

. /home/bla/.bash_profile

then use the absolute path of the mysqldump command

/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

View more solutions

Comments

[xspecial]

I tried grep CRON and it gave me: CMD (/usr/bin/mysqldump -u root -pPASSWORD! --all-databases | gzip > /var/db_backups/database_... so the command is executed but not produces output files

[adam187]

Did you set up cron for root or for other user? Maybe your cron user has no rights to execute mysqldump

[xspecial]

I haven't setuppet nothing yet. Just used crontab -e on a blank ubuntu server. How can i setup that option? Thanks

[adam187]

Try to dump as regular user and see if it works, check writing permissions for /var/db_backups/ and executing permissions for /usr/bin/mysqldump en.wikipedia.org/wiki/Chmod

[xspecial]

you're right. as user i get: -bash: /var/db_backups/database_011113.sql.gz: Permission denied

[adam187]

Try sudo chmod 777 /var/db_backups and then check or set up cron as root

[xspecial]

How can i setup cron as root?

[xspecial]

I tried both sudo crontab -e and chmod 777 /var/db_backups. Now if i execute the command as user it works but cron still not working

[Basj]

I would like to vote +10! No other source than this (many Google results, websites, forum posts) have given me this result, this was the solution for me. Without this answer, I would have searched for hours...

[Manny265]

one mistake I made was to leave a space between -p and my password, there should be no space i.e -pPASSWORD

[WeizhongTu]

when you crontab -e and save, cron will install new crontab automatically, restart is not needed

[Benoit Duffez]

@Basj: what if I searched for hours until I found this answer? mind blown

[Geraldo Novais]

Very useful your answer. Thanks so much. After to escape % character with \ , it works fine. Thanks @Sandeep

[M. Atif Riaz]

restarting crontab is not required and is actually misleading for the issue mentioned above

[tsumnia]

This was my problem as well; if you follow one of the first Google links about "Backing up MySQL on Centos", they do not include the \

[FlyingZebra1]

putting date inside quotes also works - date +"%Y-%m-%d_%H-%M"