0

I have a bin/bash script that mysqldumps my sql database, when I run it everything goes well with filesize=3MB, but when crontab(as sudo) runs the script then the filesize is 20B

script

#!/bin/bash
cd /home/blades/backup/databases
DATE=$(date +"%d-%m-%Y_%H:%M:%S")
mysqldump -u username -h localhost database --single-transaction | gzip -9 > database.sql_$DATE.gz

and sudo crontab -e

30 04 * * * /home/blades/backup/backup

What amI doing wrong?

0

3 Answers 3

1

You need to log your error by say adding

2>/tmp/mysqldump.log 

to figure out what is the error.

Most likely your cron environment does not have the PATH set up right. So the first thing you can do is to add the full path to your mysqldump and gzip commands, which likely will solve your problem.

If not, just examine the error log.

4
  • I don't know how to use 2>/tmp/mysqldump.log
    – blades
    Aug 23, 2013 at 6:38
  • well this is the output of the log file mysqldump: Got error: 1045: Access denied for user 'xxxxxxxxxx'@'localhost' (using password: NO) when trying to connect
    – blades
    Aug 24, 2013 at 13:59
  • so it is not finding your authentication information. are you using a config file? if so you need to explicitly specify it because in cron the environment is not your login shell environment. or you can add the username and password to your cron script, which is less secure.
    – johnshen64
    Aug 24, 2013 at 15:31
  • I have a config file /home/username/.my.cnf which supports authentication. now logfile says "mysqldump: unknown variable 'defaults-extra-file="
    – blades
    Aug 24, 2013 at 21:04
0

Try adding the complete path of mysqldump. /usr/bin/mysqldump -u username -h localhost database --single-transaction | gzip -9 > database.sql_$DATE.gz

(/usr/bin/mysqldump is an example. It could be in another place in your system). Scripts that run from crontab run in another "place" and do not hereby your current user $PATH definition. Your current user most likely has mysqldump path defined in its $PATH environmental variable, that's why when you run the script manually works with no problems. Same applies to gzip.

1
  • I did with no success
    – blades
    Aug 23, 2013 at 6:28
0

ok I managed to solve it, in the .my.cnf file I had to specify mysqldump and client, not only mysqldump so the file .my.cnf is (after that chmod to 400)

[client]
user=aaaaaaaaaaaaa
password=xxxxxxxxxxxx

[mysqldump]
user=aaaaaaaaaaaaa
password=xxxxxxxxxxxx

the other matter is the options must be first then localhost etc so the script goes like this

/usr/bin/mysqldump --defaults-file=/home/username/.my.cnf --single-transaction -h localhost database_name | gzip -9 > database_name.sql_$DATE.gz

That's it, thanx for helping anyway

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .