django 连接远程mysql 报错 django.db.utils.OperationalError: (1045, "Access denied for user 'root'@'localhost' (using password: NO)")

django 连接远程的mysql8 一直报错，查看数据库链接信息均没有问题

E:HappyAutoTest>python manage.py makemigrations autotest
Traceback (most recent call last):
  File "D:Program FilesPython3.6libsite-packagesdjangodbackendsasease.py", line 220, in ensure_connection
    self.connect()
  File "D:Program FilesPython3.6libsite-packagesdjangoutilsasyncio.py", line 26, in inner
    return func(*args, **kwargs)
  File "D:Program FilesPython3.6libsite-packagesdjangodbackendsasease.py", line 197, in connect
    self.connection = self.get_new_connection(conn_params)
  File "D:Program FilesPython3.6libsite-packagesdjangoutilsasyncio.py", line 26, in inner
    return func(*args, **kwargs)
  File "D:Program FilesPython3.6libsite-packagesdjangodbackendsmysqlase.py", line 234, in get_new_connection
    return Database.connect(**conn_params)
  File "D:Program FilesPython3.6libsite-packagespymysql\__init__.py", line 90, in Connect
    return Connection(*args, **kwargs)
  File "D:Program FilesPython3.6libsite-packagespymysqlconnections.py", line 704, in __init__
    self.connect()
  File "D:Program FilesPython3.6libsite-packagespymysqlconnections.py", line 974, in connect
    self._request_authentication()
  File "D:Program FilesPython3.6libsite-packagespymysqlconnections.py", line 1203, in _request_authentication
    auth_packet = self._read_packet()
  File "D:Program FilesPython3.6libsite-packagespymysqlconnections.py", line 1059, in _read_packet
    packet.check_error()
  File "D:Program FilesPython3.6libsite-packagespymysqlconnections.py", line 384, in check_error
    err.raise_mysql_exception(self._data)
  File "D:Program FilesPython3.6libsite-packagespymysqlerr.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.OperationalError: (1045, "Access denied for user 'root'@'10.20.13.38' (using password: NO)")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "manage.py", line 21, in <module>
    main()
  File "manage.py", line 17, in main
    execute_from_command_line(sys.argv)
  File "D:Program FilesPython3.6libsite-packagesdjangocoremanagement\__init__.py", line 401, in execute_from_command_line
    utility.execute()
  File "D:Program FilesPython3.6libsite-packagesdjangocoremanagement\__init__.py", line 395, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "D:Program FilesPython3.6libsite-packagesdjangocoremanagementase.py", line 328, in run_from_argv
    self.execute(*args, **cmd_options)
  File "D:Program FilesPython3.6libsite-packagesdjangocoremanagementase.py", line 366, in execute
    self.check()
  File "D:Program FilesPython3.6libsite-packagesdjangocoremanagementase.py", line 395, in check
    include_deployment_checks=include_deployment_checks,
  File "D:Program FilesPython3.6libsite-packagesdjangocoremanagementase.py", line 382, in _run_checks
    return checks.run_checks(**kwargs)
  File "D:Program FilesPython3.6libsite-packagesdjangocorechecks
egistry.py", line 72, in run_checks
    new_errors = check(app_configs=app_configs)
  File "D:Program FilesPython3.6libsite-packagesdjangocorechecksmodel_checks.py", line 34, in check_all_models
    errors.extend(model.check(**kwargs))
  File "D:Program FilesPython3.6libsite-packagesdjangodbmodelsase.py", line 1275, in check
    *cls._check_constraints(),
  File "D:Program FilesPython3.6libsite-packagesdjangodbmodelsase.py", line 1841, in _check_constraints
    connection.features.supports_table_check_constraints or
  File "D:Program FilesPython3.6libsite-packagesdjangoutilsfunctional.py", line 48, in __get__
    res = instance.__dict__[self.name] = self.func(instance)
  File "D:Program FilesPython3.6libsite-packagesdjangodbackendsmysqlfeatures.py", line 93, in supports_column_check_constraints
    if self.connection.mysql_is_mariadb:
  File "D:Program FilesPython3.6libsite-packagesdjangoutilsfunctional.py", line 48, in __get__
    res = instance.__dict__[self.name] = self.func(instance)
  File "D:Program FilesPython3.6libsite-packagesdjangodbackendsmysqlase.py", line 364, in mysql_is_mariadb
    return 'mariadb' in self.mysql_server_info.lower()
  File "D:Program FilesPython3.6libsite-packagesdjangoutilsfunctional.py", line 48, in __get__
    res = instance.__dict__[self.name] = self.func(instance)
  File "D:Program FilesPython3.6libsite-packagesdjangodbackendsmysqlase.py", line 351, in mysql_server_info
    with self.temporary_connection() as cursor:
  File "D:Program FilesPython3.6libcontextlib.py", line 82, in __enter__
    return next(self.gen)
  File "D:Program FilesPython3.6libsite-packagesdjangodbackendsasease.py", line 604, in temporary_connection
    with self.cursor() as cursor:
  File "D:Program FilesPython3.6libsite-packagesdjangoutilsasyncio.py", line 26, in inner
    return func(*args, **kwargs)
  File "D:Program FilesPython3.6libsite-packagesdjangodbackendsasease.py", line 260, in cursor
    return self._cursor()
  File "D:Program FilesPython3.6libsite-packagesdjangodbackendsasease.py", line 236, in _cursor
    self.ensure_connection()
  File "D:Program FilesPython3.6libsite-packagesdjangoutilsasyncio.py", line 26, in inner
    return func(*args, **kwargs)
  File "D:Program FilesPython3.6libsite-packagesdjangodbackendsasease.py", line 220, in ensure_connection
    self.connect()
  File "D:Program FilesPython3.6libsite-packagesdjangodbutils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "D:Program FilesPython3.6libsite-packagesdjangodbackendsasease.py", line 220, in ensure_connection
    self.connect()
  File "D:Program FilesPython3.6libsite-packagesdjangoutilsasyncio.py", line 26, in inner
    return func(*args, **kwargs)
  File "D:Program FilesPython3.6libsite-packagesdjangodbackendsasease.py", line 197, in connect
    self.connection = self.get_new_connection(conn_params)
  File "D:Program FilesPython3.6libsite-packagesdjangoutilsasyncio.py", line 26, in inner
    return func(*args, **kwargs)
  File "D:Program FilesPython3.6libsite-packagesdjangodbackendsmysqlase.py", line 234, in get_new_connection
    return Database.connect(**conn_params)
  File "D:Program FilesPython3.6libsite-packagespymysql\__init__.py", line 90, in Connect
    return Connection(*args, **kwargs)
  File "D:Program FilesPython3.6libsite-packagespymysqlconnections.py", line 704, in __init__
    self.connect()
  File "D:Program FilesPython3.6libsite-packagespymysqlconnections.py", line 974, in connect
    self._request_authentication()
  File "D:Program FilesPython3.6libsite-packagespymysqlconnections.py", line 1203, in _request_authentication
    auth_packet = self._read_packet()
  File "D:Program FilesPython3.6libsite-packagespymysqlconnections.py", line 1059, in _read_packet
    packet.check_error()
  File "D:Program FilesPython3.6libsite-packagespymysqlconnections.py", line 384, in check_error
    err.raise_mysql_exception(self._data)
  File "D:Program FilesPython3.6libsite-packagespymysqlerr.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
django.db.utils.OperationalError: (1045, "Access denied for user 'root'@'localhost' (using password: NO)")

经多番折腾终于找到解决的办法

更改远程连接端的mysql配置
登录mysql

mysql -u root -p
Enter password:

更改 "mysql" 数据库里的 "user" 表里的 "host" 项，将"localhost"改称"%"

mysql>update user set host = '%' where user = 'root';

mysql>select host, user from user;

select host, user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
4 rows in set (0.00 sec)

mysql升级到8.0之后的新特性有一条是更新了密码的加密方式。如果要用原来的Django连接mysql8.0就得将mysql的加密方式转变为原来的方式。 

也就是如果不改的话，就会出现如下报错

django.db.utils.OperationalError: (1045, "Access denied for user 'root'@'localhost' (using password: NO)")

 更改方法

mysql>ALTER USER '用户名（一般为root）'@'%' IDENTIFIED WITH mysql_native_password BY '密码';
mysql>FLUSH PRIVILEGES;
这时在django的setting.py里DATABASES中的‘HOST’，填入远程的IP即可
再次执行终于不报错了

django.db.utils.OperationalError: (1045, "Access denied for user 'root'@'localhost' (using password: NO)")

E:HappyAutoTest>python manage.py makemigrations autotest
No changes detected in app 'autotest'

E:HappyAutoTest>python manage.py migrate
Operations to perform:
  Apply all migrations: admin, auth, autotest, contenttypes, sessions
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying admin.0003_logentry_add_action_flag_choices... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying auth.0010_alter_group_name_max_length... OK
  Applying auth.0011_update_proxy_permissions... OK
  Applying autotest.0001_initial... OK
  Applying sessions.0001_initial... OK