navicat连接mysql出现2059错误

参考：https://www.cnblogs.com/wu-guo-xing/p/9970244.html

本次使用的是 docker 部署的

xiaohui@DESKTOP-44T4DSR:/mnt/c/Users/xiaohui$ docker run -d -p13306:3306 --name some-mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8.0.26
8ba4f33b2df772f8157d41cca8203e6af7e5a272a027345a60165055ae38fc21
xiaohui@DESKTOP-44T4DSR:/mnt/c/Users/xiaohui$
xiaohui@DESKTOP-44T4DSR:/mnt/c/Users/xiaohui$
xiaohui@DESKTOP-44T4DSR:/mnt/c/Users/xiaohui$ docker ps
CONTAINER ID   IMAGE          COMMAND                  CREATED         STATUS         PORTS                                                                                   NAMES
8ba4f33b2df7   mysql:8.0.26   "docker-entrypoint.s…"   6 seconds ago   Up 4 seconds   33060/tcp, 0.0.0.0:13306->3306/tcp, :::13306->3306/tcp   some-mysql
xiaohui@DESKTOP-44T4DSR:/mnt/c/Users/xiaohui$

navicat 无法连接，报错

简单采用 mysql_native_password 方式加密密码，操作如下：

# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 8.0.26 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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>
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 from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
5 rows in set (0.00 sec)
mysql> select null = null, version();
+-------------+-----------+
| null = null | version() |
+-------------+-----------+
|        NULL | 8.0.26    |
+-------------+-----------+
1 row in set (0.00 sec)
mysql> alter user 'root'@'%' identified WITH mysql_native_password by '123456';
Query OK, 0 rows affected (0.02 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql>

主要使用该句命令：alter user ‘root’@’%’ identified WITH mysql_native_password by ‘123456’;
查看用户加密使用的插件 plugin : select host,user,plugin from mysql.user;

show VARIABLES like '%password%';
show variables;
select host,user,plugin from mysql.user;
mysql> select host,user,plugin from mysql.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 |
| localhost | root             | caching_sha2_password |
+-----------+------------------+-----------------------+
5 rows in set (0.00 sec)
mysql>

mysql8 默认的鉴权加密 default_authentication_plugin caching_sha2_password

mysql> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.00 sec)
mysql>

mysql5.7 是 mysql_native_password。