MySQL dump CronJob

Question

I'm trying to create a cron that daily backups my MySQL slave. The backup.sh content:

#!/bin/bash
#
# Backup mysql from slave
#
#
sudo mysql -u root -p'xxxxx' -e 'STOP SLAVE SQL_THREAD;'
sudo mysqldump -u root -p'xxxxx' ng_player | gzip > database_`date +\%Y-\%m-\%d`.sql.gz 
sudo mysqladmin -u root -p'xxxxx'  start-slave

I made it executable by sudo chmod +x /home/dev/backup.sh and entered in to crontab by:

sudo crontab -e

0 12 * * * /home/dev/backup.sh

but it doesn't work, if I only run in the command line it works but not in crontab.

FIXED: I used the script from this link: mysqldump doesn't work in crontab

Answer 1

Break the problem in half. First try sending only email from the cron job to see if you are getting it to even run. Put this above in a file and have your cron job point to it:

#!/bin/bash

/bin/mail -s "test subject" "yourname@yourdomain" < /dev/null

The good thing about using this tester is that it is very simple and more likely to give you some results. It does not depend on your current working directory, which can sometimes be not what you expect it to be.

Answer 2

Try use full link to mysql bin directory in .sh file

example :

sudo /var/lib/mysql -u root -p'xxxxx' -e 'STOP SLAVE SQL_THREAD;'

Answer 3

I had this same problem.

I figured out that you can't use the command sudo in a non-interactive script. The sudo command would create a field where you would type in the password to your account (root).

If you are logged into a command prompt like ssh sudo works without typing in any passwords, but when another program runs sudo it would ask for password.

Try this instead su command doesn't require any logins and it does the same thing.

su --session-command="mysql -u root -p'xxxxx' -e 'STOP SLAVE SQL_THREAD;'" root
su --session-command="mysqldump -u root -p'xxxxx' ng_player | gzip > database_`date +\%Y-\%m-\%d`.sql.gz" root
su --session-command="mysqladmin -u root -p'xxxxx'  start-slave" root

Replace root with your linux username.

EDIT: Look at this thread for a different answer. https://askubuntu.com/questions/173924/how-to-run-cron-job-using-sudo-command

Answer 4

Let's start with the silly stuff in the script.

The only command which you don't run via 'sudo' is the, spookily enough, only command which I would expect you might need to run via sudo (depending on the permissions of the target file).

Prefixing the commands in a script with sudo without a named user (i.e. running as root) serves no useful function if you are invoking the script as root.

On a typical installation, the mysql, mysqladmin and gzip programs are typically executable by any user - the authentication and authorization of the commands to the DBMS are authenticated by the DBMS using the authentication credentials passed as arguments - hence I would not expect that any of the operations here, except possibly writing to the output file (depending on its permissions).

You don't specify a path for the backup file - maybe it's writing it somewhere other than you expect?

(similarly, you should check if any of the executables are in a location which is not in the $PATH for the crontab execution environment).

but it doesn't work

....is not an error message.

The output of any command run via cron is mailed to the owner of the crontab - go read your mail.