Authentication plugin ‘caching_sha2_password’ cannot be loaded: 客户端工具连接MySQL报错处理方式

最近在用Navicat连接新安装的MySQL<8.0.17>数据库时会出现Authentication plugin 'caching_sha2_password' cannot be loaded的错误。

出现这个原因是mysql8 之前的版本中加密规则是mysql_native_password,而在mysql8之后,加密规则是caching_sha2_password, 解决问题方法有两种,一种是升级navicat驱动,一种是把mysql用户登录密码加密规则还原成mysql_native_password.

具体代码及操作如下：

# 连接mysql
zhujin@zhujindeMacBook-Pro ~ % mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.17 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

# 开始执行代码
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; 
Query OK, 0 rows affected (0.01 sec)
# 设置新的加密规则下的密码
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'zj123456';
Query OK, 0 rows affected (0.01 sec)
# 刷新规则
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

然后就顺利连上了。

MacOS zsh: command not found: mysql 解决方案

最近重新安装了Mac上的MySQL,突然发现从终端连接不上，command not found: mysql。

zhujin@zhujindeMacBook-Pro ~ % mysql
zsh: command not found: mysql

很明显我们一眼就知道，这属于mysql命令没有链接上而已，很好处理。
直接通过alias设置别名就行了, 常见的设置别名的文件如下。

~/.bashrc:该文件包含专用于你的bash shell的bash信息,当登录时以及每次打开新的shell时,该文件被读取.（每个用户都有一个.bashrc文件，在用户目录下）

~/.bash_profile:每个用户都可使用该文件输入专用于自己使用的shell信息,当用户登录时,该文件仅仅执行一次!默认情况下,他设置一些环境变量,执行用户的.bashrc文件.

~/.zshrc:zsh配置文件，每次打开新的shell时,该文件被读取.如果没有，可以自行创建。

所以，我们可以在~/.bashrc 或者 ~/.zshrc中写下别名，首先看看mysql安装地址。

我没有修改过，所以默认 /usr/local/mysql，所以，在~/.bashrc 或者 ~/.zshrc任一文件中添加：

alias mysql=/usr/local/mysql/bin/mysql

然后source一下，试试效果：

# 编辑.zshrc添加alias语句
zhujin@zhujindeMacBook-Pro ~ % vim .zshrc
# 运行一次.zshrc文件
zhujin@zhujindeMacBook-Pro ~ % source .zshrc 
# 尝试连接mysql
zhujin@zhujindeMacBook-Pro ~ % mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.17 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
# 完美
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)