Mysql – Can only execute thesqladmin as root account created with unix socket, all other accounts fail

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:

• http://www.gnu.org/software/bash/manual/bashref.html#Quoting
• http://www.sal.ksu.edu/faculty/tim/unix_sg/shell/metachar.html
• http://www.grymoire.com/Unix/Quote.html

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.

You most likely have a .my.cnf file in your home directory, which is specifying a password.

The clue here is that when you did not specify a password (sudo -u root), the error message is still saying that you did.