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.

</p>


#!/bin/bash</p>

DB=${1}
USER="USER"
PASS="PASSWORD"
BACKUP_DIR="/tmp/sqlbackups"
CMD="/usr/bin/mysqldump"
DATE=`date +%Y%m%d`
BACKUP_COMMAND="${CMD} -u${USER} -p'${PASS}' ${DB}"

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

${BACKUP_COMMAND} > ${BACKUP_DIR}/${DATE}.${DB}.sqldmp
rc=$?

if [ $rc -eq 0 ];
then
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 {} \;
else
echo "`date` -- Backup Failed! Aborting"
fi
</code></pre>

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!?!

</p>

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.

</p>

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

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

</p>

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!?!?

</p>

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.

</p>

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.

</p>

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.

</p>
comments powered by Disqus