MySql8出现ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘

问题描述

有时候我们登录Mysql输入密码的时候，会出现这种情况：

mysql -u root -p 
Enter Password > '密码'
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
# 或者：错误：ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)　　

解决措施

1. 修改my.in/my.cnf配置文件

   vim /etc/my.cnf
   # 在[mysqld]下添加skip-grant-tables，保存即可。
   

2. 重启mysql服务

   systemctl restart mysqld
   systemctl status mysqld
   ● mysqld.service - MySQL Server
    Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
    Active: active (running) since 日 2021-08-22 00:52:22 CST; 12s ago
   	 Docs: man:mysqld(8)
    	      http://dev.mysql.com/doc/refman/en/using-systemd.html
   Process: 2663 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
    Main PID: 2691 (mysqld)
    Status: "Server is operational"
    Tasks: 37
    CGroup: /system.slice/mysqld.service
     	     └─2691 /usr/sbin/mysqld
   

3. 进入mysql，修改root密码

   # 不用输入密码，直接回车（出现Enter Password 也一样直接回车，即可登陆成功）
   mysql -u root -p
   use mysql; # 切换数据库
   # mysql 5.7.9以后废弃了password字段和password()函数；authentication_string:字段表示用户密码。
   select host,user,authentication_string from user;
   # 如果当前root用户authentication_string字段下有内容，先将其设置为空
   update user set authentication_string='' where user='root'
   # 使用ALTER修改root用户密码,方法为 ALTER user 'root'@'localhost' IDENTIFIED BY '新密码'。
   # 如果执行结果是ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement，则需要先执行 flush privileges;
   ALTER user 'root'@'localhost' IDENTIFIED BY 'root';
   quit # 退出
   # 移除在[mysqld]下添加的skip-grant-tables
   vim  /etc/my.cnf
   systemctl restart mysqld # 重启服务
   

4. 测试是否登陆成功

   mysql -u root -p 
   # 输入密码，进入成功