50

I am running a mysqldump via a bash script and have encountered a problem with a password that contains special characters.

mysqldump -hlocalhost -uUSERNAME -pPA$$W0RD DATABASE | 
                                gzip > /home/USERNAME/backups-mysql/BACKUP.gz

How do I escape the password?

81

I found the answer. You have to quote the password, like this:

mysql -u root -p'PASSWORD'

You must do this if the password has any of the following characters: * ? [ < > & ; ! | $ ( )

  • Do you know how to escape apostrophes within the password? – Steve Mayne May 14 '13 at 20:00
  • 3
    @SteveMayne i think its just a backslash before it – psynnott May 15 '13 at 6:33
  • 3
    parenthesis also need to be in quotes. – Félix Gagnon-Grenier Jun 5 '14 at 17:12
  • 1
    On Windows I found I had to use double quotes. Single quotes did not work. (MySQL 5.6) – TheStoryCoder Mar 25 '15 at 8:08
  • 1
    blanks in the password also require the ' ' – Hafenkranich Oct 22 '18 at 12:43
11

when you use the quotes, make sure there is no space :
between -p and 'PASSWORD' or
between --password= and 'PASSWORD'

correct:
mysql -u root -p'PASSWORD'
mysql -u root --password='PASSWORD'

does not work:
mysql -u root -p 'PASSWORD'
mysql -u root --password = 'PASSWORD'

you can also define a variable and then use it for the command (still with no spaces in between) MSQLPWD='PASSWORD'
mysql -u root -p$MSQLPWD

2

Depends on your shell. Are you using Microsoft Windows or Linux? If you are using Linux/BASH then it is likely that $$ is being interpreted as your current process ID. Have you tried putting a backslash in front of each dollar sign? e.g.

mysqldump \
  -hlocalhost \
  -uUSERNAME \
  -pPA\$\$W0RD \
  DATABASE \
| gzip -c \
> /home/USERNAME/backups-mysql/BACKUP.gz

Note that gzip probably requires the "-c" option if you want to compress to STDOUT.

  • The password I am using is not PA$$W0RD but I used this as an example. The actual password I am using has an ampersand and this is what is causing the problem. I used the backslash as you suggested but it did not work. – psynnott Mar 25 '10 at 11:35
2

Try backslashing (\) those special chars.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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