1.问题背景
安装禅道的时候，自带的mysql用navicat无法连接，报错如下：
数据库版本是：8.0.35
2.问题处理
2.1 登入数据库直接修改
- 查看默认认证插件，是caching_sha2_password
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, plugin from user;
+-----------+------------------+-----------------------+
| Host | User | plugin |
+-----------+------------------+-----------------------+
| % | root | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
+-----------+------------------+-----------------------+
4 rows in set (0.00 sec)
- 考虑到修改配置文件要重启生效，想直接修改命令
update user set plugin='caching_sha2_password';
- 直接修改后问题出现了，重启数据库出现如下提示：
2023-12-29T06:56:15.832000Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-12-29T06:56:15.847796Z 0 [Warning] [MY-010319] [Server] Found invalid password for user: 'root@%'; Ignoring user
2023-12-29T06:56:15.847838Z 0 [Warning] [MY-010319] [Server] Found invalid password for user: 'mysql.infoschema@localhost'; Ignoring user
2023-12-29T06:56:15.847850Z 0 [Warning] [MY-010319] [Server] Found invalid password for user: 'mysql.session@localhost'; Ignoring user
2023-12-29T06:56:15.847860Z 0 [Warning] [MY-010319] [Server] Found invalid password for user: 'mysql.sys@localhost'; Ignoring user
2023-12-29T06:56:15.859713Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
- 说明直接修改的操作有问题，导致数据库里的密码都无法通过验证且此时数据库无法登陆，应用界面也无法展示。
2.2 恢复数据库可登陆状态
此时应用已经无法正常使用，数据库也不可登陆。急需先恢复正常的状态，然后在谈修改上述配置。
- 修改my.cnf配置文件，添加如下配置：
该配置是跳过验证，直接以root身份登录数据库，存在安全隐患，慎用。
[mysqld]
skip-grant-tables
- 重启数据库实例后，通过命令行登入数据库，恢复之前的认证插件配置
update user set plugin='caching_sha2_password';
- 修改完之后，将步骤1的配置注释掉，重启实例
- 重启之后2.1里的报错消失，可以正常登录数据库，应用恢复正常
2.3 重新修改认证插件
直接在命令行单独修改root用户的认证模式即可：
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123';
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123';
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'
mysql> SELECT Host, User, plugin from user;
+-----------+------------------+-----------------------+
| Host | User | plugin |
+-----------+------------------+-----------------------+
| % | root | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
+-----------+------------------+-----------------------+
4 rows in set (0.00 sec)
可以看到localhost不允许修改，直接修改咱们远程登录的用户即可。
