2

I'm a newbie to shell scripting. I have written a shell script to do incremental backup of MySQL database.The script is in executable format and runs successfully when executed manually but fails when executed through crontab.
Crontab entry is like this : */1 * * * * /home/db-backup/mysqlbackup.sh
Below is the shell script code -

 #!/bin/sh
MyUSER="root"       # USERNAME
MyPASS="password"         # PASSWORD
MyHOST="localhost"  # Hostname
Password="" #Linux Password

MYSQL="$(which mysql)"
if [ -z "$MYSQL" ]; then
echo "Error: MYSQL not found"
exit 1
fi
MYSQLADMIN="$(which mysqladmin)"
if [ -z "$MYSQLADMIN" ]; then
    echo "Error: MYSQLADMIN not found"
    exit 1
fi
CHOWN="$(which chown)"
if [ -z "$CHOWN" ]; then
    echo "Error: CHOWN not found"
    exit 1
fi
CHMOD="$(which chmod)"
if [ -z "$CHMOD" ]; then
    echo "Error: CHMOD not found"
    exit 1
fi

GZIP="$(which gzip)"
if [ -z "$GZIP" ]; then
    echo "Error: GZIP not found"
    exit 1
fi
CP="$(which cp)"
if [ -z "$CP" ]; then
    echo "Error: CP not found"
    exit 1
fi
MV="$(which mv)"
if [ -z "$MV" ]; then
    echo "Error: MV not found"
    exit 1
fi
RM="$(which rm)"
if [ -z "$RM" ]; then
    echo "Error: RM not found"
    exit 1
fi
RSYNC="$(which rsync)"
if [ -z "$RSYNC" ]; then
    echo "Error: RSYNC not found"
    exit 1
fi

MYSQLBINLOG="$(which mysqlbinlog)"
if [ -z "$MYSQLBINLOG" ]; then
    echo "Error: MYSQLBINLOG not found"
    exit 1
fi
# Get data in dd-mm-yyyy format
NOW="$(date +"%d-%m-%Y-%T")"

DEST="/home/db-backup"
mkdir $DEST/Increment_backup.$NOW
LATEST=$DEST/Increment_backup.$NOW
$MYSQLADMIN -u$MyUSER -p$MyPASS flush-logs
newestlog=`ls -d /usr/local/mysql/data/mysql-bin.?????? | sed 's/^.*\.//' | sort -g | tail -n 1`
echo $newestlog
for file in `ls /usr/local/mysql/data/mysql-bin.??????`
do
        if [ "/usr/local/mysql/data/mysql-bin.$newestlog" != "$file" ]; then
     echo $file             
     $CP "$file" $LATEST         
        fi
done
for file1 in `ls $LATEST/mysql-bin.??????`
do
 $MYSQLBINLOG $file1>$file1.$NOW.sql 
 $GZIP -9 "$file1.$NOW.sql"     
 $RM "$file1"
done
$RSYNC -avz $LATEST /home/rsync-back
  • First of all, when scheduled on crontab it is not showing any errors. How can I get to know whether the script is running or not?
  • Secondly, what is the correct way to execute the shell script in a crontab.
  • Some blogs suggest for change in environment variables. What would be the best solution
Sven
  • 97,248
  • 13
  • 177
  • 225
Rudra
  • 189
  • 2
  • 6
  • 11

8 Answers8

2

Scripts being ran from crontab do not always have the same environmental variables you normally take gfor granted... Do this:

Change

 #!/bin/sh

to

 #!/bin/sh -x

with -x, you may try setting PATH manually, and even kicking off /etc/profile if all else fails.

#!/bin/sh -x
PATH=/bin:/sbin:/usr/bin:/usr/sbin
/bin/sh /etc/profile
David Houde
  • 3,160
  • 1
  • 15
  • 19
0

The /bin/sh version of the shell doesn't like the $(...) construct. Your best bet is to do one of the following:
1. Change the shebang line to #!/bin/bash
2. Change the crontab command to */1 * * * * /bin/bash /home/db-backup/mysqlbackup.sh
3. Both 1 and 2.

John
  • 8,920
  • 1
  • 28
  • 34
0

try exporting paths and variables, in crontabs paths have to be specified, the way you are executing is fine, there are a lot of posts on StackO and serverfault where you can find answer to this problem

APZ
  • 954
  • 2
  • 12
  • 24
  • Thanks for the quick reply APZ, can you explain me in detail, like which PATH and VARIABLES. – Rudra Jan 30 '13 at 20:12
  • add PATH as David has suggested and also do */1 * * * * /bin/bash /home/db-backup/mysqlbackup.sh – APZ Jan 30 '13 at 20:43
  • @Vishnu cool, would you please mark this as answer so that others can get help from this too. – APZ Jan 30 '13 at 20:50
0

For quick troubleshooting, liberally sprinkle your script with echo's to an output file. Like put one at the top:

#!/bin/bash
LOGFILE=/tmp/myoutput.log
echo "Starting" > $LOGFILE

and so on. Then you can look at the output file after the script is supposed to have run and perhaps see where it is failing. If there is no output at all, it is possible that it isn't even running, which we haven't determined yet. Then you can troubleshoot further.

lsd
  • 1,653
  • 10
  • 8
0

The needed environment variables are probably not be set when the script is run from cron. Try:

*/1 * * * * . /etc/profile; /home/db-backup/mysqlbackup.sh >>/tmp/mysqlbackup.log 2>&1

meaning that first set up the environment by running the default profile script before the actual backup. Additionally at the end there is redirection of STDOUT and STDERR to a logfile.

Look for /var/log/cron or similar. Depending on your *nix flavour the log may also be in .../messages or .../syslog. Look for cronin /etc/syslog.conf exact config.

Hopefully this helps :-)

grassroot
  • 683
  • 5
  • 14
0

Short wording solution: comment HOME=/ directive in your /etc/crontab file

Now explanation:

I found this topic because my mysql backup script , flushing bin-logs , worked just fine interactively, but failed when cron. But it occurred, that my setup was slightly different from Rudra’s, so the root cause and solution either, but still let me post it here for others with same setup as I have.

Ok, my difference from Rudra’s setup is that I do not pass user name and password to mysqladmin in the script. Instead, I setup as suggested in “6.1.2.1. End-User Guidelines for Password Security” of MySQL documentation (http://dev.mysql.com/doc/refman/5.5/en/password-security-user.html ) i.e. in the [client] section of ~/.my.cnf file of root.

Linux resolves resolves ~ into the values of HOME environment variable. cron daemon (as per man 5 crontab) “Several environment variables are set up automatically by the cron(8) daemon. SHELL is set to /bin/sh, and LOGNAME and HOME are set from the /etc/passwd line of the crontab´s owner. HOME and SHELL may be overridden by settings in the crontab”

So, with that setup “mysqladmin flush-logs” should work just fine without passing it password as a command line parameter just fine, but it did NOT. And the reason for this occurred that (for whatever reason) default /etc/crontab file (at least in CenOS 6.2) has a directive HOME=/ , overwriting the one setup as described in man.

So, comment it in your /etc/crontab and see that help you!

Cheers, Roman.

Arregator
  • 1
0

I had the same issue with crontab on AIX 7.1.

For that, I replaced the command /bin/sh with the profile shell instead. Also, on the command shell file.sh added . ~/.profile at the first line. After that, the command worked fine.

This is the line on crontab that I used:

mm hh * * * d . ~/.profile;/Directory/Command.sh > /LOG/logfile_command.log.`date | awk '{print $3$2$6}'` 2>&1

where mm: minutes, hh: hour, d:day (0-6)

Colt
  • 1,939
  • 6
  • 20
  • 25
echumbes57
  • 1
  • 2
0

Whenever you use the DataBase query in a script you need to export the path. Following is the Oracle database export:

ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
LD_LIBRARY_PATH=$ORACLE_HOME/lib
PATH=$ORACLE_HOME/bin:$PATH

export PATH
export ORACLE_HOME
export ORACLE_SID
export LD_LIBRARY_PATH
techraf
  • 4,163
  • 8
  • 27
  • 44
haroon
  • 1