MySQL密码正确却登录失败

系统版本

$ lsb_release -a
No LSB modules are available.
Distributor ID:	Ubuntu
Description:	Ubuntu 21.04
Release:	21.04
Codename:	hirsute

Centos pass掉，好像和Ubuntu的处理不太一样

错误提示:

# mysql -uroot -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

错误来源

情况1:一个是密码错误，一般这种情况居多；
情况2:root账户的本地登录被限制,我这次遇到的就是这种情况

解决方法

首先停止mysql服务

sudo service mysql stop

无密码模式登录

其中 --skip-grant-tables 的意思是跳过授权表，通过此参数来跳过输入密码，后面跟得 & 符号是表示设置此进程为后台进程

# sudo mysqld_safe --user=root --skip-grant-tables &
sudo mysqld_safe --user=root --skip-grant-tables
2021-11-27T08:30:15.329550Z mysqld_safe Logging to '/var/lib/mysql/zhengzx.err'.
2021-11-27T08:30:15.332655Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.

‘/var/run/mysqld’ for UNIX socket file don’t exists.

# 没有下面这些文件就创建它，有则忽略
mkdir -p /var/run/mysqld
chown mysql:mysql /var/run/mysqld

$ sudo mysqld_safe --skip-grant-tables --skip-networking
2021-11-27T09:02:03.349206Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2021-11-27T09:02:03.385756Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

登录

mysql -uroot -p

敲回车就okay了,然后修改

情况1-忘记密码，修改root密码

-- 修改root密码
use mysql;
alter user root@'localhost' identified by '123456';
flush privileges; #刷新系统授权表

修改成功，退出–skip-networking模式，重启mysql即可

情况2-root账户的本地登录被限制

show databases;
use mysql;
mysql> select host,user from user;
+---------------+------------------+
| host          | user             |
+---------------+------------------+
| 192.168.0.105 | root             |
| localhost     | debian-sys-maint |
| localhost     | mysql.infoschema |
| localhost     | mysql.session    |
| localhost     | mysql.sys        |
+---------------+------------------+
5 rows in set (0.00 sec)
mysql> update user set host='localhost' where user = 'root';
mysql> FLUSH PRIVILEGES;

然后退出，关掉之前的–skip-grant-tables模式，然后重新启动mysql，就okay了；