Escaping a password using mysqldump console

Question

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?

Answer 1

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: * ? [ < > & ; ! | $ ( )

Answer 2

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

Answer 3

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.

Answer 4

Try backslashing (\) those special chars.

Answer 5

IF you are using mysqldump.exe on windows you should use the double quotes, not single quotes like

mysqldump.exe --user=roo --password="WE&&^6HJg&^7" .....