10

I am trying to set a cron job for taking backup from my slave machine. So I need to stop the slave

I issued a command

mysqladmin --user=root --password=test_pass stop-slave

But it is throwing error:

mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: YES)'

Now I tried with the command

mysqladmin --user=root --password stop-slave

It prompts for password and I gave as test_pass and everything was fine.

Why so happened? What is the alternative?

NB: BTW my mysql version is mysql-5.0.95-5, it it make any sense.

  • Does your password have any special characters that the shell might be interpreting? Have you tried enclosing the password in 'quotes' or "quotes"? – Michael - sqlbot Aug 27 '13 at 10:47
  • which are the special characters that shell might get interpreting? is there any documentation? – Praveen Prasannan Aug 27 '13 at 10:54
  • 1
    Characters like $ & ! \ [ ] < > all come to mind as potentially problematic. Enclosing the password in ' single quotes should prevent that problem. gnu.org/software/bash/manual/bashref.html#Quoting – Michael - sqlbot Aug 27 '13 at 11:08
  • 2
    great.. working. I would like to mark this as answer. Can u please put it as answer – Praveen Prasannan Aug 27 '13 at 11:19
  • @Michael-sqlbot yes you should post is as the answer – RolandoMySQLDBA Aug 27 '13 at 13:42
21

Command line arguments are subject to interpretation by the system's command shell, changing the behavior of the command or changing the value of the arguments before they are passed into the called program.

When an argument (such as the value for --password) contains an character that the shell may interpret, they need to be either quoted (usually enclosed in single quotes ' in unix or double quote " in Windows) or escaped individually (usually with a backslash \ before each metacharacter) to avoid interpretation by the shell.

While the specific characters are system-specific, some characters to watch out for include:

$ & ! \ [ ] < > `

If the password, for a really bad example, were set to pa$$word ...

mysql --password=pa$$word     # does not work
mysql --password='pa$$word'   # works
mysql --password=pa\$\$word   # works, but slightly unclear what's going on at first glance

Further reading:


Update: to escape either ' single or " double quotes in the password, you can either escape them with a leading backslash, or enclose the entire argument in the opposite style of quotes if there are no other characters that the chosen style of quoting isn't compatible with.

mysql --password="like'this" # password has a single quote in the middle
mysql --password='like"this' # password with a double quote in the middle

If you have a single quote and other special characters as well, you're stuck with backslash escaping because, in unix, the double quote is "weaker" than a single quote and many metacharacters are still expanded when enclosed in double quotes but not single quotes.

This is not MySQL-specific but applies to anything with command line arguments.

You can typically use the echo command to see how the shell is interpreting your arguments.

$ echo foo$bar 
foo                # literal 'foo' plus the (empty) shell variable $bar

$ echo foo\$bar
foo$bar            # backslash prevents expansion of $bar as a variable

$ echo "foo$$bar"  # weaker double quote doesn't prevent expansion so
foo9691bar         # the $$ expands to the unix process id (pid) of the current shell

$ echo 'foo$$bar'
foo$$bar           # "stronger" single quote prevents shell expansion

$ echo "foo'bar"
foo'bar            # double quote allows single quote within the literal

Follow-up: The bash shell (and presumably some others) allows escaping single quotes within single-quoted strings, though the convention is bizarre (probably based on some long-forgotten decision lost now to the mists of time):

Replace each ' inside the string with '\'' before wrapping the entire string in single quotes... so the literal string foo'bar is expressed as 'foo'\''bar'.

Like I said, bizarre. This is needed because a backslash escapes a single quote outside of a single quoted string, a backslash escapes nothing inside a single-quoted string in bash, and single quoted strings can be closed and reopened by multiple single quotes as long as there are no unescaped intervening characters that have special meaning. So '\'' closes the quoting of the string, then supplies the escaped literal, then reopens the quoting of the string.

  • 1
    Need a little more clarification. What if single quotes (') coming in password field? Escaping it with slash() is the only way? – Praveen Prasannan Aug 27 '13 at 16:09
  • What a co-incident that my live server's password is set to pa$$word – Umair Apr 21 '17 at 17:09
0

Addition:

On a Windows Machine, we struggled with a password as well: it contained % which need to be escaped as %% (Quotes alone are not enough here)

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.