526互联

关于Django4.2.4连接mysql8.0.32的问题

发布时间 2023-08-10 13:08:52作者: 潇湘神剑

Django4.2.4连接mysql8.0.32报错：

(blog) root@MyHost:/data/blog# python3 manage.py makemigrations
/root/.pyenv/versions/blog/lib/python3.9/site-packages/django/core/management/commands/makemigrations.py:158: RuntimeWarning: Got an error checking a consistent migration history performed for database connection 'default': (2059, "Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory")
  warnings.warn(
Migrations for 'user':
  user/migrations/0001_initial.py
    - Create model User

具体解决方式

　　mysql8.0以上的版本使用新版本的加密方式：caching_sha2_password

mysql> show variables like '%authen%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| authentication_policy         | *,,                   |
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
2 rows in set (0.02 sec)

mysql> select host,user,authentication_string,plugin from user;
+-------------+------------------+------------------------------------------------------------------------+-----------------------+
| host        | user             | authentication_string                                                  | plugin                |
+-------------+------------------+------------------------------------------------------------------------+-----------------------+
| 192.168.%.% | root             | $A$005$'(
                                            E>`P+up7asyQrIkUbnEKTzdGhomFnOlZ7CTyWfOQGtfBt7EXl9b/5 | caching_sha2_password |
| localhost   | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost   | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost   | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost   | root             | $A$005$w!-<Q^V_[`f;WY{2 F/dYkbOTYmQ46O/ndVVY4qdJ9.MKbMYIrdjTOEu8rx/5 | caching_sha2_password |
+-------------+------------------+------------------------------------------------------------------------+-----------------------+
5 rows in set (0.00 sec)

　　但是目前来看，Django不支持caching_sha2_password这种加密方式：

(blog) root@MyHost:/data/blog# python3 manage.py makemigrations
/root/.pyenv/versions/blog/lib/python3.9/site-packages/django/core/management/commands/makemigrations.py:158: RuntimeWarning: Got an error checking a consistent migration history performed for database connection 'default': (2059, "Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory")
  warnings.warn(
Migrations for 'user':
  user/migrations/0001_initial.py
    - Create model User

　　mysql5.7的版本的加密方式为：mysql_native_password；

　　因此就需要将caching_sha2_password更换为mysql_native_password；只修改Django连接需要的用户的加密方式即可。

mysql> ALTER USER 'root'@'192.168.%.%' IDENTIFIED WITH mysql_native_password BY '123.com';
Query OK, 0 rows affected (0.04 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.03 sec)

mysql> select host,user,authentication_string,plugin from user;
+-------------+------------------+------------------------------------------------------------------------+-----------------------+
| host        | user             | authentication_string                                                  | plugin                |
+-------------+------------------+------------------------------------------------------------------------+-----------------------+
| 192.168.%.% | root             | *AC241830FFDDC8943AB31CBD47D758E79F7953EA                              | mysql_native_password |
| localhost   | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost   | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost   | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost   | root             | $A$005$w!-<Q^V_[`f;WY{2 F/dYkbOTYmQ46O/ndVVY4qdJ9.MKbMYIrdjTOEu8rx/5 | caching_sha2_password |
+-------------+------------------+------------------------------------------------------------------------+-----------------------+
5 rows in set (0.00 sec)

　　再次执行不再报错

(blog) root@MyHost:/data/blog# python3 manage.py makemigrations
Migrations for 'user':
  user/migrations/0001_initial.py
    - Create model User