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?

Answer 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

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

Answer 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

MAILTO=your@mail.com

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

Answer 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

Answer 6

Local Host mysql Backup: 0 1 * * * /usr/local/mysql/bin/mysqldump -uroot -ppassword --opt database > /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.)

It works for me. no space between the -p and password or -u and username

Answer 7

Create a new file and exec the code there to dump into a file location and zip it . Run that script via a cron

Answer 8

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.

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

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.

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.

You can create the option file by using mysql_config_editor or simply in any 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.

Answer 9

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

service cron restart

or

/etc/init.d/cron restart