When using the command-line mysql command with a specified username, it might authenticate without a password sometimes.
If you run: mysql -u john -p
And it authenticates without a password (and you know the user has a password), then try running this while logged in:
mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| @localhost |
+----------------+You might be authenticated as a "localhost" because the host "localhost" is more specific to your authentication request than the user name. (See this confusing explanation on the MySQL documentation):
It is a common misconception to think that, for a given user name, all rows that explicitly name that user are used first when the server attempts to find a match for the connection. This is not true. The preceding example illustrates this, where a connection from thomas.loc.gov by jeffrey is first matched not by the row containing "jeffrey" as the User column value, but by the row with no user name. As a result, jeffrey is authenticated as an anonymous user, even though he specified a user name when connecting.
To fix this, make sure to give the CREATE USER a host more specific than "%"
CREATE USER 'john'@'localhost' IDENTIFIED BY 'my-password';