问题描述
一个项目,框架用了Django
,数据库用的mysql
,但在执行python manage.py migrate
时遇到了如下问题:
Traceback (most recent call last):
File "/usr/local/lib/python3.8/dist-packages/django/db/backends/base/base.py", line 217, in ensure_connection
self.connect()
File "/usr/local/lib/python3.8/dist-packages/django/db/backends/base/base.py", line 195, in connect
self.connection = self.get_new_connection(conn_params)
File "/usr/local/lib/python3.8/dist-packages/django/db/backends/mysql/base.py", line 227, in get_new_connection
return Database.connect(**conn_params)
File "/usr/local/lib/python3.8/dist-packages/MySQLdb/__init__.py", line 84, in Connect
return Connection(*args, **kwargs)
File "/usr/local/lib/python3.8/dist-packages/MySQLdb/connections.py", line 166, in __init__
super(Connection, self).__init__(*args, **kwargs2)
MySQLdb._exceptions.OperationalError: (1698, "Access denied for user 'root'@'localhost'")
The above exception was the direct cause of the following exception:
...
后面还有一堆,大意就是连接mysql的数据库时出错了,开始以为是settings.py
的设置不对,后来检查了一遍,没啥错,而且本来这个项目在我的电脑上跑的好好的,放到阿里云上就开始出错了,所以问题就出现在mysql上
解决方法
前面说了,我的电脑上能跑,放到阿里云上就没法跑,其实是因为这两台主机的mysql配置有点出入,这个出入是版本不同导致的,mysql在8.0以后的版本,默认用的密码加密方法变了,所以说用之前的方法,直接使用root
账户+密码的形式就连接不上去
为了确认是不是这个问题导致的错误,可以sudo mysql
进到mysql client以后,用SELECT user,authentication_string,plugin,host FROM mysql.user;
查看:
+------------------+-------------------------------------------+-----------------------+-----------+
| user | authentication_string | plugin | host |
+------------------+-------------------------------------------+-----------------------+-----------+
| root | | auth_socket | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *CC744277A401A7D25BE1CA89AFF17BF607F876FF | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)
如果root
或者你配置的账户对应的plugin
像上面一样,为auth_socket
,那么问题就出在这,你需要使用如下的命令将这个引擎改到和其他账户相同的引擎:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
password
就是root
对应的密码,然后FLUSH PRIVILEGES;
更新一下权限,可以再用上面的命令查一遍引擎,改过来以后就可以正常连接了,再执行之前数据库迁移的命令就ok
参考资料
sql - MySQL Error: : 'Access denied for user ‘root’@‘localhost’ - Stack Overflow