Introduction

This article will focus on how to solve the error message available or appear after executing the following command execution :

mysql -uroot -p -Pxxxx -hxxx.xxx.xxx.xxx

In order to be more specific, the following is the full and complete output command execution using the above command pattern :

root@hostname:/opt/mysql/temp/mysql-5.7.28-linux-glibc2.12-x86_64# mysql -uroot -p -P3350 -h127.0.0.1
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
root@hostname:/opt/mysql/temp/mysql-5.7.28-linux-glibc2.12-x86_64#

The command execution according to the above output is very clear. Actually, there is an attempt for inserting password when the ‘Enter password:’ prompt appear. Although there is a password insert attempt, it will generate an error. It is because in this context, MySQL Database Server is a service where the installation is done manually. Furthermore, there has been no attempt for initializing the password for ‘root’ account. So, it is normal that there is no password will match on the entry process.

 

Solution

Actually, there is a solution for solving the problem above. The following are the steps for implementing that solution :

1. Just edit the file ‘my.cnf’ to skip the authentication process. The following is the line entry to achieve it :

[mysqld]
skip-grant-tables

2. Don’t forget to restart the MySQL Database Server to implement the changes on the MySQL Database Server’s configuration file. After that, just re-execute the above command for logging in to MySQL Database Server :

user@hostname:/opt/mysql/temp/mysql-5.7.28-linux-glibc2.12-x86_64$ mysql -uroot -P3350 -h127.0.0.1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.28-log MySQL Community Server (GPL)
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>

3. Use ‘mysql’ database by executing the following query :

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>

4. Execute the following query to update the user ‘root’ password :

mysql> update user set authentication_string=PASSWORD("password") where User='root';
Query OK, 1 row affected, 1 warning (0.26 sec)
Rows matched: 1  Changed: 1  Warnings: 1
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)
mysql>

5. Quit from MySQL Command Console by executing the following command :

mysql> quit
Bye

6. Edit back the ‘my.cnf’ file by omitting the line configuration added before. Just remove ‘skip-grant-tables’ for making MySQL Database Server to authenticate the logging process to MySQL Command Console. Don’t forget to restart the MySQL Database Server so that the change in the MySQL configuration file will have an an affect.

7. Finally, just log back to MySQL Command Console using the previous password where it has been set in the fourth step. Just execute as follows :

user@hostname:/opt/mysql/temp/mysql-5.7.28-linux-glibc2.12-x86_64$ mysql -uroot -p -P3350 -h127.0.0.1
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.28-log
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> 

8. Apparently, there is a new problem. Since it is a first time for using the MySQL Database Server with a new password. There must be a first step for using the MySQL Database Server. It is exist as in the following output :

mysql> use mysql;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> 

In order to solve the above problem, just read the article in this link. It is an article where the title is completely match the error message above. The title of the article is ‘How to Solve MySQL Error Message ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.’