我尝试将我的 PHP 登录更改为 MySQL 8.0，从使用 caching_sha2_password 更改为 mysql_native_password，如下所示。现在我根本无法登录 MySQL。我已经重新启动了 mysqld 无济于事。

有什么补救的办法吗？

我关注了这篇文章php mysqli_connect: authentication method unknown to the client [caching_sha2_password]

mysql> ALTER USER 'elstatuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mysecretpassword';
ERROR 3009 (HY000): Column count of mysql.user is wrong. Expected 50, found 49. Created with MySQL 80012, now running 80013. Please use mysql_upgrade to fix this error.



[root@han ~]# mysql_upgrade --force -uroot -p
Enter password: 
Checking server version.
Running queries to upgrade MySQL server.
Upgrading system table data.
Checking system database.
mysql.columns_priv                                 OK

mysql.component                                    OK
mysql.db                                           OK
mysql.default_roles                                OK
mysql.engine_cost                                  OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.global_grants                                OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.password_history                             OK
mysql.plugin                                       OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.role_edges                                   OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
The sys schema is already up to date (version 2.0.0).
Checking databases.
elstat.account                                     OK
[...]
wp.wp_users                                        OK
Upgrade process completed successfully.
Checking if update is needed.
[root@han ~]# logout
[thommym@han ~]$ mysql -u root -p
Enter password: 
ERROR 3118 (HY000): Access denied for user 'root'@'localhost'. Account is locked.
[thommym@han ~]$ 

最佳答案

我在 Windows 中更改身份验证插件时发生了类似的事情。

MySQL 8+ 的默认身份验证插件是caching_sha2_password；为了简单起见，我想将其更改为简单的 mysql_native_password，因为我只是在本地使用 DBMS，不想设置或需要我们的企业防火墙已经提供的额外保护。

我相信我必须重设 root 密码，但它只有在我编辑 my.ini [客户端] 配置文件(在基于 Unix 的机器上是 my.cnf)以调整密码策略要求后才有效:

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
6 rows in set (0.01 sec)

根据需要对上述变量进行更改(出于我的目的，只需设置 validate_password_policy=LOW 即可)。重新启动 MySQL 服务/守护进程以使更改生效，然后尝试使用 mysql_admin 重置 root 密码并恢复帐户。

关于MySQL 8.0 ERROR 3118 (HY000) : Access denied for user 'root' @'localhost' . 账户被锁定，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/53090586/