mysqldump command not working in cron job

Question

I am trying to take backup using below bash script scheduled in cron.

#!/bin/bash
echo "Hello"
while read table
do
/usr/bin/mysqldump -uroot -pxxxxxx CMAYA_RadiusUserLogs $table > sc_back/${table}.sql
done < tables.txt

cron is scheduled as below

58 16 * * * root /bin/sh -x /backup/call_backup.sh > /backup/backup.log

/var/log/cron as below

Jul  3 16:27:01  (root) CMD (/bin/sh -x /backup/backup.sh > /backup/backup.log)

/backup/backup.log

[root@ backup]# cat backup.log
Hello

When I execute this same script manually it works properly and put any other command in script it works in cron job.

Answer 1

I have corrected tables.txt path but problem still persists but after redirecting std err 2>&1 it is started working. The reason I found that the warning message "Warning: Using a password on the command line interface can be insecure." creates the problem. After this message mysqldump exits in , so I redirected the error and its started working. I observed this after upgrading mysql to 5.6.

#!/bin/bash

echo "Hello"
while read table
do
touch sc_back/${table}.sql
/usr/bin/mysqldump -uroot -pxxxxx CMAYA_RadiusUserLogs $table > /backup /sc_back/${table}.sql
done < /backup/tables.txt

Answer 2

The problem is that in the last line of the script you need to enter the full absolute path for the file tables.txt, otherwise the cron job cannot find it.

The reason the script works when you launch it manually is that the tables.txtfile is located into the current directory, so the system has no problem finding it.

Answer 3

When cron executes your script current working directory is cron table's user home directory (apparently /root), while your script with its files is located under /backup. This is why script can't find table.txt and table loop has no iterations.

You should first cd to the script's directory, then execute it, like:

58 16 * * * root cd /backup && /bin/sh -x call_backup.sh > backup.log