MySQL error: Access denied for user 'a'@'localhost' (using password: YES)

  • I use the root account created the account 'a'@'%'. But I can't use the account to connect to MySQL server when I specify the host parameter. I can successfully connect without the -h parameter. Please see the transcript below. I hope someone can help me to explain it. Thanks.

    mysql> grant all on *.* to 'a'@'%' identified by a;
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a' at line 1
    mysql> grant all on *.* to 'a'@'%' identified by 'a';
    Query OK, 0 rows affected (0.00 sec)
    mysql> show grants for 'a'@'%';
    | Grants for [email protected]%                                                                                            |
    1 row in set (0.00 sec)
    mysql> exit
    [[email protected] ~]# mysql -h localhost -u a -p
    Enter password: 
    ERROR 1045 (28000): Access denied for user 'a'@'localhost' (using password: YES)
    [[email protected] ~]# mysql -h -u a -p
    Enter password: 
    ERROR 1045 (28000): Access denied for user 'a'@'localhost' (using password: YES)
    [[email protected] ~]# mysql -u a -p
    Enter password: 
    ERROR 1045 (28000): Access denied for user 'a'@'localhost' (using password: YES)
    [[email protected] ~]# mysql -u a
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 20
    Server version: 5.5.17 MySQL Community Server (GPL)
    Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    mysql> status
    mysql  Ver 14.14 Distrib 5.5.17, for Linux (x86_64) using readline 5.1
    Connection id:      20
    Current database:   
    Current user:       [email protected]
    SSL:            Not in use
    Current pager:      stdout
    Using outfile:      ''
    Using delimiter:    ;
    Server version:     5.5.17 MySQL Community Server (GPL)
    Protocol version:   10
    Connection:     Localhost via UNIX socket
    Server characterset:    utf8
    Db     characterset:    utf8
    Client characterset:    utf8
    Conn.  characterset:    utf8
    UNIX socket:        /var/lib/mysql/mysql.sock
    Uptime:         15 days 15 hours 20 min 18 sec
    Threads: 1  Questions: 40  Slow queries: 0  Opens: 41  Flush tables: 1  Open tables: 4  Queries per second avg: 0.000


    Yes, MySQL is listening on port 3306.

    [[email protected] ~]# nmap localhost
    Starting Nmap 4.11 ( ) at 2012-01-18 07:35 CST
    Interesting ports on localhost.localdomain (
    Not shown: 1674 closed ports
    22/tcp   open  ssh
    25/tcp   open  smtp
    111/tcp  open  rpcbind
    631/tcp  open  ipp
    840/tcp  open  unknown
    3306/tcp open  mysql
    Nmap finished: 1 IP address (1 host up) scanned in 0.064 seconds
    [[email protected] ~]# 

    I am not a specialist in MySql, but I saw this problem few times, I don't know what was the reason, the solution was explicitly definition of host in addition to `'a'@'%'`. so, the first record was `'a'@'%'`, and the second is `'a'@'localhost'`.

    can you please test the same thing with lower mysql version..?

    use Cpanel Login Details To connect.

    In my case I had three annonymous accounts created by default, deleting them would solve this issue

  • Here is a quick-and-dirty method for checking out how MySQL performs successful authentication.

    Please run this query:


    USER() reports how you attempted to authenticate in mysqld

    CURRENT_USER() reports how you were allowed to authenticate by mysqld

    Sometimes, USER() and CURRENT_USER() are different. That's because mysql authentication follows a specfic protocol.

    According to MySQL 5.0 Certification Study Guide

    enter image description here

    pages 486,487 state the following on mysql's authentication algorithm:

    There are two stages of client access control:

    In the first stage, a client attempts to connect and the server either accepts or rejects the connection. For the attempt to succeed, some entry in the user table must match the host from which the client connects, the username, and the password.

    In the second stage (which occurs only if a client has already connected sucessfully), the server checks every query it receives from the client to see whether the client has sufficient privileges to execute it.

    The server matches a client against entries in the grant tables based on the host from which the client connects and the user the client provides. However, it's possible for more than one record to match:

    Host values in grant tables may be specified as patterns contains wildcard values. If a grant table contains entries from,,, and %, all of them match a client who connects from

    Patterns are not allowed for the User values in grant table entries, but a username may be given as an empty string to specify an anonymous user. The empty string matches any username and thus effectively acts as a wildcard.

    When the Host and the User values in more than one user table record match a client, the server must decide which one to use. It does this by sorting records with the most specific Host and User column values first, and choosing the matching record that occurs first in the sorted list, Sorting take place as follows:

    In the Host Column, literal values such as localhost,, and sort ahead of values such as that have pattern characters in them. Pattern values are sorted according to how specific they are. For example, is more specific than, which is more specific than %.

    In the User column, non-blank usernames sort ahead of blank usernames. That is, non-anonymous users sort ahead of anonymous users.

    The server performs this sorting when it starts. It reads the grant tables into memory, sorts them, and uses the in-memory copies for access control.

    From this description, you do not need to worry about the order of the mysql.user tables since there is an in-memory copy of the grant tables which is sorted as previously mentioned.

    With regard to how you logged in, only mysql -u a worked. Go back and login again and run these commands

    SELECT user,host,password FROM mysql.user;

    Make sure that

    • every user has a password.
    • there are no anonymous users (when user is blank)

    This is just a guess, but I suspect mysql -u a of connecting via localhost because when the connection protocol is not specified, the default is to connect via the socket file. There may exist an entry in mysql.user that allow anonymous localhost connection.

    Run this query:

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

    If you get back a row with no password, that fully explains why mysq -u a works.

    UPDATE 2012-01-19 11:12 EDT

    Craig Efrein brought up an interesting question: if two identical usernames exist in the mysql.user table, one with a password and one without, does that mean that MySQL denies authentication when not using a password?

    This question is an excellent heads up about MySQL user authentication.

    Please note that the primary key of mysql.user is host,user. There are no other indexes. This allows multiple occurrences of a username. Each occurrence can have a different password or no password. This allows user 'dbuser' to login locally ([email protected]) using no password and the same user login from another server within a given netblock ([email protected]'') with a password like 'pass1' and that user to login remotely from anywhere ([email protected]'%') with a remote password like 'pass2'.

    Given the authentication algorithm that MySQL uses, there are no restrictions placed on users with the presence or absense of a password.

    This is why MySQL 5.0 Certification Study Guide says on Page 498 Paragraph 6 in its bulletpoints brings out how to cleanup the authentication process:

    On Unix, MySQL comes with a mysql_secure_installation script that can perform several helpful security-related operations on your installation. The script has the following capabilities:

    • Set a password for the root accounts
    • Remove any remotely accessible root accounts.
    • Remove the anonymous user accounts. This improves security because it prevents the possibility of anyone connecting to the MySQL server as root from a remote host. The results is that anyone who wants to connect as root must first be able to log in on the server host, which provides an additional barrier against attack.
    • Remove the test database (If you remove the anonymous accounts, you might also want to remove the test database to which they have access).

    Yes I tried, it do return a row with no password. Thanks for your excellent explanation and recommended the MySQL certification book.

    Rolando, if two identical usernames exist in the mysql.user table, one with a password and one without, does that mean that MySQL denies authentication when not using a password?

    @Craig - Your question is very noteworthy. I will transfer it into my answer and address it there.

    Thank you for the detailed answer, in my case I had anonymous users configured somehow.

    @RolandoMySQLDBA, Can all the information in the certification study guide **already** be found in the MySQL online manual?

  • The '%' host wildcard doesn't match 'localhost'. By default the mysql client will try to connect via a socket rather than tcp (usually some place like /var/lib/mysql/mysql.sock).

    You can either change your grant to 'a'@'localhost', or force the client to operate over the TCP stack like:

    mysql -u a -p --protocol=TCP

    I tried, but still of no luck.

    How to configure this option in `my.cnf` for not needing to this parameter any more?

    You don't. If you don't specify a -h hostname it assumes "localhost" meaning it's looking for a socket, not a TCP port w/o the protocol flag. You could setup a shell aliase if you're tired of typing out all the arguments.

  • Did you check to make sure that MySQL is actually listening on 3306? Run a netstat -tlpn and provide the results. If you don't see 3306 then its probably not.

    In my.cnf you should verify that --skip-networking is commented out

    user            = mysql
    pid-file        = /var/run/mysqld/
    socket          = /var/run/mysqld/mysqld.sock
    port            = 3306
    basedir         = /usr
    datadir         = /var/lib/mysql
    tmpdir          = /tmp
    language        = /usr/share/mysql/English
    bind-address    =
    # skip-networking

    I also did the same as asked in the question. and also did what you say in your answer but there is still the same issue.

    Can you provide the results from the following query: select user, host from mysql.user;

    Yes, MySQL is listening on port 3306. See my edit.

    You also may want to try mysql -u user -p -h If that works then I believe its mysql that doesn't know how to resolve localhost. An entry for localhost that points to in your /etc/hosts file will resolve that.

    Did you run Flush Privileges?

    @CraigEfrein : mysql> select User,Host,Password from mysql.user where User='a'; +------+------+-------------------------------------------+ | User | Host | Password | +------+------+-------------------------------------------+ | a | % | *667F407DE7C6AD07358FA38DAED7828A72014B4E | +------+------+-------------------------------------------+ 1 row in set (0.00 sec)

    @CraigEfrein: I think this is issue with version of mysql as i have tested it in lower mysql version it is working quite well.

    Abdul, if you created the user 'a'@' directly or 'a'@'localhost' what happens ?

    @CraigEfrein: Still the same issue.I am able to login without password for user a with both and localhost.

    @CraigEfrein Yes I've tried to flush the privileges but of no luck.

  • As @atxdba described, To connect mysql daemon from remote that not connect via socket so you need to connect from remote via TCP.

    For this, you should specify the --protocol=TCP per each connections. Although, you can set it in my.cnf on the server:


License under CC-BY-SA with attribution

Content dated before 6/26/2020 9:53 AM