Hello Érico
On 5/29/2014 2:22 PM, Érico wrote:
> I have ran the following to test a fix for an app issue :
>
> delete from mysql.user where user='';
> 2lines got effected
>
> after this I can´t connect through command line anymore :
>
> ./mysqladmin -u root password <pwd>
>
> I get access denied for user 'root'@'localhost" (using password:'NO')
>
> how can I restore the db so I can connect through command line again ?
>
> thks
>
What that tells me is that you were never actually logging in as root
but the system was authenticating you as the 'anonymous' user. Quoting
from the very fine manual:
http://dev.mysql.com/doc/refman/5.6/en/account-names.html
####
A user name is either a nonblank value that literally matches the user
name for incoming connection attempts, or a blank value (empty string)
that matches any user name. An account with a blank user name is an
anonymous user. To specify an anonymous user in SQL statements, use a
quoted empty user name part, such as ''@'localhost'.
####
http://dev.mysql.com/doc/refman/5.6/en/connection-access.html
####
Identity checking is performed using the three user table scope columns
(Host, User, and Password). The server accepts the connection only if
the Host and User columns in some user table row match the client host
name and user name and the client supplies the password specified in
that row.
...
If the User column value is nonblank, the user name in an incoming
connection must match exactly. If the User value is blank, it matches
any user name. If the user table row that matches an incoming connection
has a blank user name, the user is considered to be an anonymous user
with no name, not a user with the name that the client actually
specified. This means that a blank user name is used for all further
access checking for the duration of the connection (that is, during
Stage 2).
...
If you are able to connect to the server, but your privileges are not
what you expect, you probably are being authenticated as some other
account. To find out what account the server used to authenticate you,
use the CURRENT_USER() function.
####
That same page in the manual (and its siblings) should also answer your
questions as to how MySQL uses the `user` table, what the empty `user`
and `password` column mean to login attempts, and how to configure
SSL-based connections.
If you have forgotten your actual root@localhost password, you can reset
it following one of the procedures provided here.
http://dev.mysql.com/doc/refman/5.6/en/resetting-permissions.html
Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.