ERROR 1044 (42000) ERROR 1142 (42000): SELECT command denied to user ''@'localho 博客分类： mysql

ERROR: Access denied for user 'root'@'localhost' (using password: NO)
    发现：
    mysql -u root@localhost -p 成功
    mysql -u root -p 失败
    mysql> SELECT user, host FROM mysql.user;
    ERROR 1142 (42000): SELECT command denied to user ''@'localhost' for table 'user'
    mysql> SELECT USER(), CURRENT_USER();
    +--------------------------+----------------+
    | USER()                   | CURRENT_USER() |
    +--------------------------+----------------+
    | root@localhost@localhost | @localhost     |
    +--------------------------+----------------+
    1 row in set (0.01 sec)
   

   
    原因: root 设置了密码，需要把root的密码清掉。
   
    以下可以解决问题(方案一)：
    1) service mysqld stop
    2) mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
    3) mysql -u root
    4) Setup new MySQL root user password
    use mysql;
    select user,host,password from mysql.user;
        +------+-----------+-------------------------------------------+
        | user | host      | password                                  |
        +------+-----------+-------------------------------------------+
        | root | localhost | *CF1E6A25C954B638A451D6|
        | root | centos64  |                                           |
        | root | 127.0.0.1 |                                           |
        |      | localhost |                                           |
        |      | centos64  |                                           |
        +------+-----------+-------------------------------------------+
    update mysql.user set password=PASSWORD("***********") where User='root';
   
    select user,host,password from mysql.user;
        +------+-----------+-------------------------------------------+
        | user | host      | password                                  |
        +------+-----------+-------------------------------------------+
        | root | localhost | *CF1E6A25C954B638A451D6 |
        | root | centos64  | *CF1E6A25C954B638A451D6|
        | root | 127.0.0.1 | *CF1E6A25C954B638A451D6|
        |      | localhost |                                           |
        |      | centos64  |                                           |
        +------+-----------+-------------------------------------------+
    flush privileges;
    quit

    5) Stop MySQL Server: service mysqld stop

    6) Start MySQL server and test it:
    service mysqld start
    mysql -u root -p
    SELECT USER(),CURRENT_USER();
        +----------------+----------------+
        | USER()         | CURRENT_USER() |
        +----------------+----------------+
        | root@localhost | root@localhost |
        +----------------+----------------+
        1 row in set (0.00 sec)   
       
    以下可以解决问题(方案二)：
    先用方案一登录mysql，再把root的密码设置为空
    use mysql;
    update user set password=PASSWORD("****************") where User='root';
    flush privileges;
    quit
  

=========================

在授权的时候报错：
ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'mdm'

首先这里是用root用户操作，所以对这个数据库的权限绝对是*的，但是如果是非root用户操作，就可能是权限的问题，需要root用户授权。
     出现上面的报错，很可能，其实应该可以说基本上都是因为my.cnf 文件里面有skip-name-resolve参数，这个参数导致这不能解析hostname或其它方式的登录, 所以登录任何用户，匹配的时候不走root@'localhost',或者127.0.0.1或者：：1 ，而是 一个劲的走root@'%'.
再来查看一下每种方式下的root用户的具体权限：

mysql> select * from user\\G;

我这里root有三种解析登录方式分别是：Host: bidevedw\_db、Host: ::1、Host: %
一般情况还有Host:127.0.0.1、Host:localhost我的里面把哪两种删了。
请注意 ！
注意上面每条记录的的红色字体部分Grant_priv: Y
这个表示，以这种方式解析登录的root用户，是否有grant权限，Y则表示有授权限给其他用户的权限，N表示没有。
这里恰好，就是我们要找的原因，因为我的/my.cnf文件里面有skip-name-resolve参数，所以root都是解析到@'%'方式登录，于是就没有grant_priv权限。

解决方法：
1、在不重启MySQL服务的情况下，只需要在登录的时候加上-h参数。
例如：(a). /usr/local/mysql/bin/mysql -uroot -p123456 -h::1
         (b)./usr/local/mysql/bin/mysql -uroot -p123456 -h127.0.0.1

         (c)./usr/local/mysql/bin/mysql -uroot -p123456 -hlocalhost
2、需要重启MySQL。把skip-name-resolve参数去掉  ---- 还没验证。不过我记得，如果去掉了，日志里面会有大量的警告信息。我是因为那些警告信息，才添加的这个参数。

=========================================

HTTP Status 500 - javax.servlet.ServletException: java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)

  grant all privileges on aldb.* to root@localhost;
  grant select on aldb.* to nobody@localhost;
  grant all privileges on aldb.* to root@112.112.112.112;
  grant select on aldb.* to nobody@112.112.112.112;
  grant all privileges on aldb.* to [e-mail];
  grant select on aldb.* to [e-mail];



use mysql
update user set password=password('xxxxxxxxxx') where user='root';
flush privileges;



====================================================

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

-----------------------------

/etc/init.d/mysqld status
/etc/init.d/mysqld stop
mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
需要使用Enter中断会话
mysql -u root -p -hlocalhost
mysql> use mysql;
select Host, User, Password from user where user='root' and host='root' or host='localhost';
+-----------+--------+----------+
| Host      | User   | Password |
+-----------+--------+----------+
| localhost |        |          |
| localhost | aimin  |          |
| localhost | nobody |          |
| localhost | root   |          |
+-----------+--------+----------+
4 rows in set (0.00 sec)

update user set password=PASSWORD('XXXXXXXXXXXXXX') where user='root' and host='root' or host='localhost';
flush privileges;
quit

/etc/init.d/mysqld restart


mysql -u root -p -hlocalhost

----------------------------------------------

# service mysqld stop
# mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
# mysql -u root -p -hlocalhost
mysql> UPDATE user SET Password=PASSWORD('xxxxxxxxxxxxxxxxxxxxx') where USER='root';
mysql> FLUSH PRIVILEGES;
mysql> quit
# service mysqld start

restart CentOS.

-------------------------------------



Access denied for user 'root'@'localhost' (using password: YES)
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'mysql'


SELECT host,user,password,Grant_priv,Super_priv FROM mysql.user;

UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';
FLUSH PRIVILEGES;
GRANT ALL ON *.* TO 'root'@'localhost';
GRANT ALL ON *.* TO 'root'@'127.0.0.1';

GRANT ALL ON *.* TO 'root'@'xyz.xyz.com.cn';

GRANT ALL ON *.* TO 'root'@'123.123.123.123';

FLUSH PRIVILEGES;
quit



REF:

http://www.cnblogs.com/kerrycode/p/3861719.html

https://help.ubuntu.com/community/MysqlPasswordReset

http://blog.itpub.net/27099995/viewspace-1362951/