MySQL连接报错2059

当启动Django自带的服务器时，报错2059：

...
MySQLdb._exceptions.OperationalError: (2059, <NULL>)
...

查看了一下mysql版本：

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.13    |
+-----------+
1 row in set (0.00 sec)

mysql>

在MySQL 5.7中，默认的身份验证插件是 mysql_native_password；MySQL 5.8开始将caching_sha2_password作为默认的身份验证插件。

mysql> use mysql;
Database changed
mysql> select user,host,plugin from user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
4 rows in set (0.00 sec)

mysql>

这里给出的解决办法是将新的加密插件caching_sha2_password更改为5.7之前的加密插件 mysql_native_password，方式如下：
alter user '用户名'@'localhost' identified with mysql_native_password by '密码'

以下是个临时解决办法：

首先我使用Navicat打开mysql数据库中的user表，在User为root那一行鼠标右击，选则复制为–>Insert语句。
将复制得到的sql语句的user值更改为root123

INSERT INTO `mysql`.`user`(`Host`, `User`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Reload_priv`, `Shutdown_priv`, `Process_priv`, `File_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`, `Show_db_priv`, `Super_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Execute_priv`, `Repl_slave_priv`, `Repl_client_priv`, `Create_view_priv`, `Show_view_priv`, `Create_routine_priv`, `Alter_routine_priv`, `Create_user_priv`, `Event_priv`, `Trigger_priv`, `Create_tablespace_priv`, `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject`, `max_questions`, `max_updates`, `max_connections`, `max_user_connections`, `plugin`, `authentication_string`, `password_expired`, `password_last_changed`, `password_lifetime`, `account_locked`, `Create_role_priv`, `Drop_role_priv`, `Password_reuse_history`, `Password_reuse_time`, `Password_require_current`) VALUES ('localhost', 'root123', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0, 0, 'caching_sha2_password', '$A$005$tA0Ka%mBa88\rf!sK2/CnVlc1OCpEQTh3Hc7XlcAHqKFyVwi7uKJI3/7Gm/', 'N', '2018-12-24 13:30:16', NULL, 'N', 'Y', 'Y', NULL, NULL, NULL);

执行sql语句。这样，就拷贝了一条和root一样的数据，接下来就操作这条数据。

# 执行命令修改加密方式
mysql> alter user 'root123'@'localhost' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.07 sec)

# 刷新权限使配置生效
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql>

查看一下：

mysql> select user,host,plugin from user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
| root123          | localhost | mysql_native_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)

mysql>

更改连接数据库的用户名，然后再启动Django：

(Py3_spider) D:\PythonProject\djangostart>python manage.py runserver
Performing system checks...

System check identified no issues (0 silenced).

You have 15 unapplied migration(s). Your project may not work properly until you apply the migrations for app(s): admin, auth, contenttypes, sessions.
Run 'python manage.py migrate' to apply them.
February 06, 2019 - 10:47:58
Django version 2.1.5, using settings 'djangostart.settings'
Starting development server at http://127.0.0.1:8000/
Quit the server with CTRL-BREAK.

发现已经正常了。

参考：

MySQL8.0新特性——默认使用caching_sha2_password作为身份验证插件 http://blog.51cto.com/fengfeng688/2147169

转载于:https://www.cnblogs.com/onefine/p/10499360.html