Authentication relying on thesql_native_password seems to also check for Linux user

I think you have anonymous users

Try running this:

SELECT user,host,password FROM mysql.user WHERE user='';

This will show what anonymous users exist. Most likely, you will see a line with a blank user, host %, and a blank password as shown below:

mysql> select user,host,password from mysql.user;
+-----------+-------------+-------------------------------------------+
| user      | host        | password                                  |
+-----------+-------------+-------------------------------------------+
| lwdba     | 127.0.0.1   | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| lwdba     | localhost   | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| lwdba     | %           | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| root      | localhost   | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| root      | 127.0.0.1   | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| vanilla   | localhost   |                                           |
| mywife    | %           |                                           |
|           | %           |                                           | <<<--- LOOK !!!
| replicant | 10.64.113.% | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| kumar     | %           |                                           |
+-----------+-------------+-------------------------------------------+

So, how did you login? Run this query:

SELECT USER(),CURRENT_USER();

What does this tell you?

• USER() reports how you attempted to authenticate in MySQL
• CURRENT_USER() reports how you were allowed to authenticate in MySQL

The second function CURRENT_USER() reveals how which anonymous user was used to log in.

What privileges did you have when you logged in?

Please run

SHOW GRANTS;

This will unveil what privileges you had at the time you logged in. The fact that you were blocked from creating a database shows you were not root but some lower-privileged user.

Please clean up your user grants.

• Feb 17, 2012 : MySQL : Why are there "test" entries in mysql.db?
• Feb 17, 2012 : What is the mysql.db table used for?
• Jan 18, 2012 : MySQL error: Access denied for user 'a'@'localhost' (using password: YES)

As for resetting the root password, please do the following:

echo "SET PASSWORD FOR root@localhost=PASSWORD('password');" > /var/lib/mysql/rootpwd.sql
service mysql restart
rm -f /var/lib/mysql/rootpwd.sql

I learned this efficient method from @ShlomiNoach.

Give it a Try !!!

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.