Introduction

The article focuses on how to solve an error message as in the title of it. The error is ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES). The error appears upon logging attempt to MySQL command console. The following is the execution of the logging attempt to MySQL Command console showing the error message :

root@hostname ~# mysql -uroot -p 
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
root@hostname ~#

The error message above occurs after upgrading mysql. The following is the output of the upgrade process :

Enter password: 
Checking server version.
Running queries to upgrade MySQL server.
mysql_upgrade: (non fatal) [ERROR] 1728: Cannot load from mysql.proc. The table is probably corrupted
mysql_upgrade: (non fatal) [ERROR] 1545: Failed to open mysql.event
mysql_upgrade: [ERROR] 1136: Column count doesn't match value count at row 1
Checking system database.
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.gtid_slave_pos                               OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.index_stats                                  OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.roles_mapping                                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.table_stats                                  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
Upgrading the sys schema.
mysql_upgrade: [ERROR] 1136: Column count doesn't match value count at row 1
Checking databases.
...
...
sys.sys_config                                     OK
...
Upgrade process completed successfully.
Checking if update is needed.
root@hostname ~# 

Solving the Problem

The impact of the upgrade process using the ‘mysql_upgrade’ command affect the MySQL database. The normal user account ‘root’ can no longer logging in with the usual password. In order to solve the problem, the following steps are the solution to solve the problem :

1. Edit the MySQL configuration file. In the context of this article, the file exist in /etc/mysql/conf.d. The file name is ‘mysql.cnf’. Add the following line in the file :

skip-grant-tables

Place that line in the ‘mysqld’ block so the content in the file will be exist as follows :

[mysqld]

skip-grant-tables
port                           = 3306
...

2. Restart MySQL database service to enforce the change in the MySQL database configuration file. Execute the following command to restart the MySQL database service :

root@hostname ~# systemctl restart mysql
root@hostname ~# 

3. After restarting the MySQL database service, try to login to MySQL database command console without having to supply any kind of password. Just execute the following command to login and execute a query to update the password :

root@hostname ~# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.27-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set authentication_string=password('password') where user='root';
Query OK, 1 row affected, 1 warning (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected, 11 warnings (0.15 sec)

mysql>, 

4. Since the password for ‘root’ account has been changed, edit back the MySQL database configuration file into its original state. The configuration line of ‘skip-grant-tables’ is only for skipping the authentication mechanism for logging in to MySQL command console. Just remove it and restart the MySQL database server’s service again.

5. Finally, access back to MySQL command console as follows :

root@hostname ~# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.27-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

At last, the connection to the MySQL command console is finally a success without having to face the same error message as in the previous output command