mysqldump doesn't work in crontab

You need to escape % character with \

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

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 

Notes

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.

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]

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.

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 

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

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

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.