I've build a shell mysql backup script which works very well.

It only has problems with passwords with special chars like this: xgT{uic[Is?uJ+.

Here is the important part of the script:

ssh $SUSER@$SHOST "mysqldump -q -u $MUSER -h $MHOST -p$MPASS $db --no-create-db | gzip -9 > $FILE" 2> $ERROR

I've changed it to:

ssh $SUSER@$SHOST 'mysqldump -q -u $MUSER -h $MHOST -p'\''$MPASS'\'' $db --no-create-db | gzip -9 > $FILE' 2> $ERROR

As you can see I've already tried double quotes. But now I get the error:

bash: $FILE: ambiguous redirect

I'm getting the password and other info from an external file:

DB_USER=db user

Within my script it take the info from the external file:

Source dbserver.conf
  • I was able to have it echo your special character string without a problem. What was your error before you quoted the password? – user508889 May 2 '16 at 18:33
  • It was: "mysqldump: Got error: 1045: Access denied for user '...'@'...' (using password: YES) when trying to connect – HHGK May 2 '16 at 18:38
  • That's exactly the error '...'@'...' or did you mask that? Obviously keep your username and password secret, but did it omit some characters, or the entire string? – user508889 May 2 '16 at 18:41
  • I've masked it. The original error was with the correct user and address. – HHGK May 2 '16 at 18:46
  • Could be the problem that I'm getting the password from an external file?: – HHGK May 2 '16 at 18:53

Your password needs to have the special characters escaped. So if your original password is xgT{uic[Is?uJ+it will have to become xgT\{uic\[Is\?uJ\+ (A slash before the special characters).

You might also want to look at something already written to do this such as AutoMySQLBackup, but I'm not sure if you need daily, weekly or monthly backup rotations. However the same rule applies, if the password has special characters those special characters need to be escaped.

Hope that helps!


Yes. It helped to escape special chars:

MPASS=$(printf "%q\n" "$DB_PASS")

But there was still a problem with mysqldump. I finally found out, that mysqldump only worked for me with passwords with special characters when I don't tell mysqldump the host (-h hostname). This seems to be a bug. Or isn't it?

Unfortunately I also have sites where the mysql server is an other host. So this solution isn't perfect.


You should write like this:

ssh "$SUSER@$SHOST" "mysqldump -q -u \"$MUSER\" -h \"$MHOST\" -p\"$MPASS\" \"$db\" --no-create-db | gzip -9 > \"$FILE\"" 2> "$ERROR"

That is, all variables used as command line arguments should be protected by enclosing within quotes. The double-quotes around variables ensure that the expression is treated as a single value, so the shell will not split it to words. Notice that " embedded within "..." need to be escaped, and written as \". A simpler solution is to use single-quotes instead of the embedded double-quotes, like this:

ssh "$SUSER@$SHOST" "mysqldump -q -u '$MUSER' -h '$MHOST' -p'$MPASS' '$db' --no-create-db | gzip -9 > '$FILE'" 2> "$ERROR"

Lastly, I'm wondering if you really meant to have 2> "$ERROR" on the local host, and not on the remote host. I suspect you actually wanted that on the remote host, so that's another error in the original command line. If that's the case, then here's the fix:

ssh "$SUSER@$SHOST" "mysqldump -q -u '$MUSER' -h '$MHOST' -p'$MPASS' '$db' --no-create-db | gzip -9 > '$FILE' 2> '$ERROR'"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.