MySQL 8.0 ERROR 3118 (HY000): Access denied for user 'root'@'localhost'. Account is locked

Question

I tried to change my PHP login to MySQL 8.0 from using caching_sha2_password to mysql_native_password as below. Now I can't login at all to MySQL. I have restarted the mysqld to no avail.

Any ideas to remedy?

I followed this article 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 ~]$

JJ Ward JJ Ward · Accepted Answer · 2019-05-28T11:27:04

I had something similar happen in Windows when changing the auth plugin.

The default auth plugin for MySQL 8+ is caching_sha2_password; I wanted to change it to a simple mysql_native_password for simplicity's sake, as I'm just using the DBMS locally and didn't want to setup or need the additional protections beyond what our enterprise firewall already provides.

I believe I had to reset the root password, but it only worked once I'd edited the my.ini [client-side] config file (my.cnf on Unix-based machines) to adjust the password policy requirements:

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)

Make changes as necessary to the above variables (for my purposes, simply setting validate_password_policy=LOW did the trick). Restart the MySQL service/daemon so the changes take effect, and then try using mysql_admin to reset the root password and restore the account.

MySQL 8.0 ERROR 3118 (HY000): Access denied for user 'root'@'localhost'. Account is locked

1 Answers