mysqldump Command Not Working in Shell Scripts

I was working on a quick and dirty mysql backup script today. It was nothing complex, yet for some reason I could not for the life of me get it to execute. Here's the script in its entirety, minus the obvious redactions.



DATE=`date +%Y%m%d`

echo "`date` -- Backing up database ${DB}"


if [ $rc -eq 0 ];
echo "`date` -- Zipping up DB"
/bin/gzip ${BACKUP_DIR}/${DATE}.${DB}.sqldmp

echo "`date` -- Deleting old backups"
/bin/find ${BACKUP_DIR} -name "*${DB}.sqldmp.gz" -mtime +5 -exec rm {} \;
echo "`date` -- Backup Failed! Aborting"

I continuously received an "Access Denied" error message for the user. Just so I knew I wasn't crazy, I echo'd the command that was being executed, copied and pasted it and voila. Backups. WUT!?!


The current password was pretty long and contained spaces, so I figured, maybe the spaces were causing problems. I created a brand new user on sql.


grant select, lock tables on 'database_name'.* TO 'database_user'@'localhost' IDENTIFIED BY 'password with no spaces'

Same results. Works when I copy and paste the command, but doesn't execute through the script.


So to debug the thing, I removed the -p from my command line so that I'd be prompted for a password. DISCO! It worked. WUT!?!?


Now that I switched the password to be a password with no spaces, I decided for shits and giggles to remove the single quotes. Suddenly I'm in backup heaven.


I don't claim to be an expert on the evils of string variables in bash, but my understanding was that quoting the string, inside of a double quote would produce a literal single quote. Based on the output when I echo'd the command line variable, that's EXACTLY what was happening. But for some reason mysqldump just didn't care for that.


Odd, but solved. I saw a bunch of people reporting the same problem on the web with no answers, so I thought I'd post my experiences.

comments powered by Disqus