解决手贱复制黏贴修改了 MySQL 中的 user 表信息导致连接不上，出现`Access denied for user ‘root‘@‘localhost`的问题

一、背景

  用可视化连接工具连接 MySQL 时，总是连不上，出现如下图的情况：

网上找了一下解决办法如下（window）：

# 进入 mysql 的 bin 目录后，cmd 打开 doc 命令窗口
# 登录
mysql -u root -p密码
# 切换数据库
mysql> use mysql;
# 查询
mysql> select user,host,plugin from user;

  出现如下结果：

+------------------+-----------+-----------+
| user             | host      |  plugin|
+------------------+-----------+-----------+
| root             | %         | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
+------------------+-----------+-----------+

   plugin 这个字段中 caching_sha2_password 就是导致上述情况的出现，因此需要更改插件的连接的验证方式，如下：

# 更改密码验证方式（无需加密连接）
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '密码';
# 刷新
flush privileges;

  以上是解决上图出现的问题，然而有一次，其他插件可以连接上，但 SQLyog 却连不上，此时在其他插件连接上的界面中，直接将 user 表中的 root 账号的密码认证方式由caching_sha2_password 覆盖成 mysql_native_password ，而不是通过命令的方式更改，导致所有的系统都连接不上 MySQL，出现Access denied for user 'root'@'localhost这个错误提示，手贱。

二、解决方式

2.1 先免密登录

  先关闭 MySQL 的进程和服务，在 my.ini 中的 [mysqld] 节点上加 skip-grant-tables，其主要作用是跳过表中的验证，可以无密码登录。然后启动 MySQL 服务，如下：

[mysqld]
port = 3306
basedir=C:\Program Files\MySql57\mysqlx64
datadir=C:\Program Files\MySql57\mysqlx64\data
max_connections=200
character-set-server=utf8
default-storage-engine=INNODB
skip-grant-tables

[mysql]
default-character-set=utf8

  然而问题来了，加入skip-grant-tables后，MySQL 无法启动了。查阅资料发现，上述方式在 MySQL8.0 中是失效的。而以下方式适合 5 代和 8 代，如下操作。

  cmd 打开两个 doc 命令窗口，第一个窗口如下命令：

mysqld --console --skip-grant-tables --shared-memory

  第二个窗口登录 MySQL，如下图：

# 免密登录，直接回车即可
mysql -uroot -p

注：我这里是要指定配置文件来开启免密登录的，可按需加入，若加入，记得地址用双引号，另外，命令最后不要加分号结束。如下：

mysqld --defaults-file="C:\Program Files\MySql57\mysqlx64\my-default.ini" --console --skip-grant-tables --shared-memory

2.2 修改连接的密码认证方式

  上述操作已成功登录 MySQL，说明已成功了一大半。接下来查看一下数据库是否完整

有些用其他方式登录 MySQL 的，数据库是原始的数据库，那种方式不对，但具体忘记是哪种方式操作了的，反之我都试过了。

# 查看所有数据库，若是有自己的数据库，则表示正确了。
mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| chat               |
| mysql              |
+--------------------+

# 切换数据库
mysql> use mysql;
# 查询密码认证方式
mysql> select user,host,plugin from user;

+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | %         | caching_sha2_password |
+------------------+-----------+-----------------------+

# 先刷新
flush privileges;
# 再更改密码验证方式（无需加密连接）
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '密码';
# 最后再刷新
flush privileges;

  至此，已修改为原始的密码认证了。然后关闭这两个窗口，开启 MySQL 服务，就可以正常连接了。

！说到最后，必须强调一下，修改敏感数据时必须用命令，不要手贱。