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?

psx · Accepted Answer · 2015-03-25T11:41:46.283

100

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

edited Mar 25 '15 at 11:41

answered Mar 25 '10 at 11:44

psx

1,845
2
12
11

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 – psx May 15 '13 at 06:33
3

parenthesis also need to be in quotes. – Félix Gagnon-Grenier Jun 05 '14 at 17:12
2

On Windows I found I had to use double quotes. Single quotes did not work. (MySQL 5.6) – TheStoryCoder Mar 25 '15 at 08:08
1

blanks in the password also require the ' ' – Hafenkranich Oct 22 '18 at 12:43

score 13 · Answer 2 · edited Feb 29 '12 at 16:30

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

score 2 · Answer 3 · answered Mar 25 '10 at 11:27

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.

answered Mar 25 '10 at 11:27

PP.

2,695
3
24
28

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. – psx Mar 25 '10 at 11:35

score 2 · Answer 4 · answered Mar 25 '10 at 11:38

2

Try backslashing (\) those special chars.

answered Mar 25 '10 at 11:38

antichris

1,166
7
14

score 0 · Answer 5 · answered Oct 03 '21 at 22:19

0

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" .....

answered Oct 03 '21 at 22:19

M.Ali El-Sayed

101
1

Escaping a password using mysqldump console

5 Answers5