mysql root账户连接问题：Access denied for user ‘root‘@‘localhost

问题描述

一个项目，框架用了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