This error is very common when you try to log in to your MySQL for the first time.
Personally, I too faced this issue multiple times, maybe because we were not following the right way to install MySQL, or we forgot to set a password, or else we didn’t get the options to set a password.
Similarly, there may be many other reasons for the above error, but the solution to this error is pretty simple and easy. In this article, you will find a way to resolve 1698 (28000) Access denied for user ‘root’@’localhost’.
There are two ways to resolve this error. First, you will see a simple and less time-consuming method. If the first method didn’t work in your case, then go to the second one.
Method 1: The easiest way to fix error 1698 (28000): Access denied for user
One of the easiest ways to resolve this error is to invoke “mysql_secure_installation”, which will ask you to set a password for your MySQL root user.
$ sudo mysql_secure_installation
You need to enter a new password and, for confirmation, re-type that password.
If the password is less than 25 characters, you will be prompted to confirm whether you want to continue or not. If you don’t want to set password characters more than 25, simply type “n”.
Fix error using mysql_secure_installation
In my case, it didn’t work and threw me an error on-screen:
“Failed! Error: SET PASSWORD has no significance for user ‘root’@’localhost’ as the authentication method used doesn’t store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters.”
If the same thing happens with you too, then close the terminal and follow the second set of steps, which worked for me.
Method 2: Fix error 1698 (28000): Access denied for user using Alter
When you install MySQL for the first time, there may be a chance you will not find the option to set a password. If it is like that, then you need to login into MySQL as a root user.
Most of the time, users may try to log in to MySQL as “mysql -u user” or “mysql -u root”, but it won’t work because “[email protected]” has not yet been created on your system, and root cannot be accessed without sudo privileges.
And when your try to log in as root user “mysql -u [email protected]”, you will get the below error.
MySQL Error 1698(2800)
To resolve this, you must log into MySQL as a sudo on the new installation because the account that is linked with MySQL is the “root” account, not the “user” account.
Run the below command in your terminal:
$ sudo mysql -u root
Once you are logged in, you will find the MySQL console looking like the below-shown image.
Login to MySQL with sudo
Now you can access your MySQL account with the above command, but there is a small problem with this like you cannot log in to MySQL without sudo privileges, and MySQL can be accessed by any privilege or sudo user.
To prevent that, you can set a password prompt to appear whenever anyone tries to log in.
There are different types of password mechanisms supported by MySQL by default you will find “auth_socket” for your root account to make it robust you can choose conventional “mysql_native_password” or the latest “caching_sha2_password“ on later version 8.0.0
If you want to learn more about authentication plugins, then do check out MySQL authentication documentation.
Before moving to the next step, you should check what password mechanism or plugin is attached to your root account to find out that you can run the following queries on the MySQL console.
mysql> SELECT User, plugin from mysql.user ;
The behavior of the above command is shown below:
Check password plugin in MySQL
Set a password on the root account
Now you can set or update your password by following the below snippet. Make sure to replace [ENTER-NEW-PASSWORD] with your password and, prior to that, select the MySQL database.
mysql> use mysql;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH 'caching_sha2_password' BY '[ENTER-NEW-PASSWORD]';
Output:
Set Password on MySQL
I’m using “caching_sha2_password”. If you want to use “mysql_native_password” on > MySQL 5.7 version, type the below queries on your MySQL console.
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY '[ENTER-NEW-PASSWORD]';
Once the changes have been implemented, restart the MySQL service.
$ sudo systemctl restart mysql.service
And once the service is restarted, type the below code in your terminal and enter the password which you have set above.
$ mysql -u root -p
Output:
Login back to MySQL after resolving the error
That’s all to fix this error.
Suggestion for you: How to change MySQL port in Ubuntu Linux
A man with a tech effusive who has explored some of the amazing technology stuff and is exploring more. While moving towards, I had a chance to work on Android development, Linux, AWS, and DevOps with several open-source tools.
