mysqldump script doesn't work in cron

Question

I've been going through all the various questions about why a specific script run in cron and nothing has helped me thus far. So I'm just trying to run a mysqldump script that now looks like this:

#!/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

# you must pass arguments to this script
if [ $# -eq 0 ]
        then
                echo "No arguments supplied"
                exit 1
fi

# if a path is supplied, perform the mysqldump to that directory
today=`date +%m-%d-%Y`
mysqldump --defaults-file=/home/<user>/.my.cnf --databases 360_projects --single-transaction --add-drop-database --triggers --routines -u BACKUPUSER > $1pmbackup${today}.sql

Notice that I've added explicit paths, let it know that I want to run it in bash, and in mysqldump that I'm manually specifying the path to my config file in case cron doesn't pick it up. None of this has helped thus far.

And finally my crontab, which I invoke using crontab -e so that it automatically installs when I quit my editor.

SHELL=/bin/sh
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

# Backup of MySQL Database every 4th Hour, Locally, Remotely
6 11 * * * /bin/bash /home/<user>/Scripts/sql-backup.sh /media/mysqlBackup-Daily/

I've made sure of course that my script is owned by the user to which this crontab belongs and the permissions are set to rwxr so I'm really not sure where this is going wrong.

Output of my /var/log/cron for that specific time when I was trying to test it:

Aug 21 14:06:01 www5 crond[1305]: (<user>) RELOAD (/var/spool/cron/<user>)

EDIT: Perhaps the best question for the time being is simply how to get more verbose output from a cron job. Right now, all I'm getting is a line telling me it reloaded (see above).

EDIT 8-27-18: I noticed the suggestion to use logger to get more verbose output but that doesn't seem to be working. My error now changed to:

Aug 27 13:55:01 www5 crond[1305]: (php) ERROR (getpwnam() failed)

From the command:

*/5 10 * * * /bin/bash /home/<user>/Scripts/sql-backup.sh /media/vantecBackup1/mysqlBackup-Daily/ 2>&1 | /usr/bin/logger -t sql_daily_backup

What do you make of this error? It doesn't make much sense to me since the script does not use php. Thanks.

Answer 1

In the file script you must add option passs( -p PASSWORD) on command mysqldump because when run script you can enter password so script not running. You re-add command to script with option -p.

mysqldump --defaults-file=/home/<user>/.my.cnf --databases 360_projects --single-transaction --add-drop-database --triggers --routines -u BACKUPUSER -p PASSWORD > $1pmbackup${today}.sql

I think script and cron auto running.

Answer 2

Use logger to output the result of your cron script. See https://serverfault.com/a/434902/470077

That will give you some clues as to what you should actually be doing. Otherwise we're simply making guesses as to what is wrong.

Answer 3

Huge thanks to everyone who helped on this one, it took me a lot of debugging to fix this one. Issue 1): At some point seeing everyone's advice here I changed my script to read

mysqldump --databases 360_projects --single-transaction --add-drop-database --triggers --routines -u$user -p$password > $1pmbackup${today}.sql 2>&1 >> /var/log/cron

or something like this, I deleted it as soon as I realized the idiocy. What was happening there was that I was getting a file creation, but at 0 bytes. Removing that fixed that. However after resolving everything you all had mentioned I was having this error in /var/log/cron:

Aug 28 10:27:50 www5 crontab[12101]: PAM pam_end: NULL pam handle passed

This lead me to see that it was a bug in cronie in CentOS (I guess I should have mentioned this askUbuntu is just more frequented/friendly than other Linux QA Sites). So updated that and it's working fine now. Here are my final scripts for reference.

#!/bin/bash

# you must pass arguments to this script
if [ $# -eq 0 ]
        then
                echo "No arguments supplied"
                exit 1
fi

# if a path is supplied, perform the mysqldump to that directory
today=`date +%m-%d-%Y`
mysqldump --defaults-file=/home/achamberlain/.my.cnf --databases 360_projects --single-transaction --add-drop-database --triggers --routines -u BACKUPUSER > $1pmbackup${today}.sql

And my crontab looks like this:

# Backup of MySQL Database every 4th Hour, Locally, Remotely
5 8-20/4 * * * /home/<user>/Scripts/sql-backup.sh /media/vantecBackup1/mysqlBackup-Daily/

EDIT: My solution originally used all the PATH variables that you provided as answers, but it works without it, so it might just have been the bug in cronie.