估计很多同学都会被无脑百度之后被虐的很惨,这个错误哥们也是饱受折磨,当然主要是懒,想着一个简单的问题随便百度就行了。但是很快就会发现浪费时间不说,根本就无法解决问题。下面就一步步记录这中间发生的很多奇妙的事情。(顺便再恶心下MySQL的安装过程,真特么傻逼)
mysql安装过程
这个没啥值得说的,大部分安装的过程比较简单(百度出来的基本靠谱儿),在CentOS7.x下默认如果yum install mysql是安装的MariaDB这个姥姥不疼,舅舅不亲的玩意儿。所以必须要重新获取源才行。下面一步步记录(默认root账户,如果不是加上sudo):
wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
rpm -ivh mysql-community-release-el7-5.noarch.rpm
yum install mysql-server
这个过程中会自动安装MySQL server、client等一大东西,就不贴了,正常情况下不会出现错误(至少我在很多次安装过程中没有发现任何错误)
安装之后尝试第一次登录:
service mysql start
mysql -uroot -p
不用密码就可以进去了,看起来一切很完美。然后按照百度出现各种无脑拷贝的东东一股脑上。
service mysql stop
mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
mysql> UPDATE user SET Password=PASSWORD(’123456’) where USER=’root’;
mysql> FLUSH PRIVILEGES;
mysql> quit
service mysql restart
mysql -uroot -p
Enter password:
然后乐滋滋的输入123456,等待进入MySQL体内。但是不好意思出现错误:
Access denied for user 'root'@'localhost' (using password: NO) denied for user 'root'@'localhost' (using password: NO)
排查过程
然后开始了异常艰苦的排查过程,首先按照了方法一,还有方法二,方法三 诸如此类无脑拷贝的方法无法解决问题,依然无法登陆。时间过去了若干小时,有点令人抓狂了。冷静下来,知道这些无脑拷贝的东西根本无法解决问题。
梳理错误:
首先密码肯定应该是生效,现在问题有两个:
1.无密码的登录是可以的,说明匿名账户还是可以正常登录;
2.root账户密码如果正确的情况下,出现错误就是'root'@'localhost'是不是有问题;
匿名账户是可以进入MySQL,但是无权限去查看甚至修改mysql系统数据库中的user表,所以这个账户应该干掉。
清理匿名账户
service mysql stop
mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
mysql> DELETE FROM mysql.user WHERE User = '';
mysql> FLUSH PRIVILEGES;
mysql> quit
service mysql restart
mysql -uroot -p
Enter password:
不输入密码肯定是进不去了。
排查root无法进入的问题
根据stackoverflow的提示,修改如下:
service mysql stop
vim /etc/my.cnf
在[mysqld]的区域中添加一行skip-grant-tables,然后启动mysql:
service mysql start
mysql -uroot -p
mysql> select host, user from user;
查看系统用户:
仔细看,这里根本就没有host=local,user=root的组合存在嘛(这里要秀一句WTF!),怪不得会提示’root’@’localhost’会登录失败。那就好办了,没有就添加呗,注意这里有127.0.0.1,就可以使用此IP登录,在这之前将my.cnf添加的skip-grant-tables先注释掉,然后重启mysql
service mysql restart
[root@localhost ~]# mysql -uroot -p -h 127.0.0.1
Enter password:
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
这句话语法错误竟然!!(原因我到现在还不知道T_T)。那就直接添加得了:
mysql> INSERT INTO mysql.user (user, host, password) VALUES ('root', 'localhost', PASSWORD('123456'));
mysql> FLUSH PRIVILEGES;
mysql> select host, user from user;
然后再重新使用root登录,成功了!
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.38 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
但是还是有问题:
mysql> use mysql;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'mysql'
重新使用127.0.0.1登录:
[root@localhost ~]# mysql -uroot -p -h 127.0.0.1
Enter password:
mysql> grant all privileges on *.* to root@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
退出重新登录,在查看,就可以了!
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.38 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select host, user from mysql;
ERROR 1146 (42S02): Table 'mysql.mysql' doesn't exist
mysql> select host, user from user;
+-----------------------+------+
| host | user |
+-----------------------+------+
| 127.0.0.1 | root |
| ::1 | root |
| localhost | root |
| localhost.localdomain | root |
+-----------------------+-----
最后感谢google
参考
https://stackoverflow.com/questions/41645309/mysql-error-access-denied-for-user-rootlocalhost
https://askubuntu.com/questions/428243/mysql-access-denied-for-user-rootlocalhost
http://blog.csdn.net/menguiying/article/details/52633090
https://www.2cto.com/database/201308/237276.html