• MySQL知识库 :: security
  • Why is my password blank and why can't I reset it?

  • Discussion

    When logging into MySQL through a client like mysql as a particular user (e.g., root), if you find that no password or an incorrect password is accepted, it may be that the user account has no password set for it. If you tried setting the password for the account and you still can log in without the password, then you may have more than one account set up for the user and one of them doesn't require a password.

    After you log into MySQL through the client, run the following statement:

    SELECT USER(), CURRENT_USER();

    Normally, the results for both columns should be the same. However, for the scenario described, they will probably be different. The results for the first column of the SQL statement above may be 'user'@'somehost', while the second may be 'user'@'%'. It may be that you have a couple of 'user'@'host' accounts set up for the same user, but with different host addresses. One of them is probably 'user'@'%' and without a password. When you attempt to log in as the user from one host address and fail, MySQL then attempts to authenticate that user at the wildcard address of '%'. If it has no password set for it, the server sets the current user for the session to that combination. When you attempt to reset the password, however, the client is sending the user/host combination in which you logged into the client as, not the user/host combination for the session on the server.

    Solution

    To fix the problem described above, you would either drop the user combination of 'user'@'%' or set the password for that combination. To drop the user, you would enter something like this:

    REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'%'; 

DROP USER 'user'@'%';

    If you want to keep the user/host combination, you can set the password like so:

    SET PASSWORD FOR 'user'@'%' = PASSWORD('new_pwd');