Mysql5.7.30 Root账户失去权限的解决 Access denied for user (using password: YES)

0.问题描述

使用root 用户登录，选择系统表，或者增删改查的时候出现 Access denied for user (using password: YES)的错误

1.找到mysql数据的配置位置

whereis mysql

我的配置文件是在/etc/mysql 下，该目录下，主要有以下内容：

conf.d  my.cnf  mysql.conf.d 

查看my.cnf文件

有效内容仅以下两行

!include /etc/mysql/conf.d/
!include /etc/mysql/mysql.conf.d/

2.使用skip-grant-tables方式登录mysql

实际的配置在以下两个目录中，在conf.d下面mysqld.conf下有如下内容

[mysqld]
 pid-file        = /var/run/mysqld/mysqld.pid
 socket          = /var/run/mysqld/mysqld.sock
 datadir         = /var/lib/mysql
 log-error       = /var/log/mysql/error.log
#By default we only accept connections from localhost
 bind-address  = 127.0.0.1
#Disabling symbolic-links is recommended to prevent assorted security risks
 symbolic-links=0
 character_set_server = utf8
 init_connect = 'SET NAMES utf8'

在[mysqld]下面增加一行skip-grant-tables，配置如下

[mysqld]
 skip-grant-tables
 pid-file        = /var/run/mysqld/mysqld.pid
 socket          = /var/run/mysqld/mysqld.sock
 datadir         = /var/lib/mysql
 log-error       = /var/log/mysql/error.log
#By default we only accept connections from localhost
 bind-address  = 127.0.0.1
#Disabling symbolic-links is recommended to prevent assorted security risks
 symbolic-links=0
 character_set_server = utf8
 init_connect = 'SET NAMES utf8'

保存后重启mysql

su root
service mysql restart
mysql -uroot

3.重新给root用户权限

use mysql
update user set Update_priv ='Y' where user ='root';
update user set Select_priv ='Y' where user = 'root';
update user set Insert_priv ='Y' where user = 'root';
set password for root@'%' = password('root');

然后去掉skip-grant-tables恢复原本的状态，重启mysql以后root用户即恢复正常。