Not able to connect Database server on MySQL Workbench? Solve this error with a simple ALTER command
I think last week I published an article on How to Install MySQL Workbench on Ubuntu 20.04. After a couple of days, someone sent me an email.
And the email was from one of our readers, and She wrote to me, “I was able to install MySQL Workbench on my Ubuntu OS, and once I try to log in a local database, I get the pop-up with the following error and list of steps for troubleshooting steps.
“Your connection attempt failed for user ‘root’ to the MySQL server at localhost:3306: Access denied for user ‘root’@’localhost’ (using password: YES)
- 1 Check that MySQL is running on address localhost.
- 2 Check that MySQL is reachable on port 3306 (note: 3306 is the default, but this can be changed).
- 3 Check the user root has rights to connect to localhost from your address (MySQL rights define what clients can connect to the server and from which machines).
- 4 Make sure you are both providing a password if needed and using the correct password for localhost connecting from the host address you’re connecting from”.
I tried it all, but the problem persists.
This problem was no more new to me, I replied to her with the steps, and later she thanked me for helping out.
In this article, We will see how to resolve the above error, and before getting to the steps, please try to troubleshoot with the above guidelines.
Table of Contents
Steps to Solve Database Connection
If you are in a hurry to solve this problem, first pass the following command, and later we will explain to you, Why this error occurred?
Step 1 :- Login to MySQL with root user
You need to login to MySQL with the root user to make changes for that open a terminal window using Ctrl + Alt + T and pass the following command:
$ sudo mysql -u root
Step 2:- Alter or change password mechanism
By default, MySQL uses socket authentication to authenticate a user, so we need to pass the below command to change the password mechanism.
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
ALTER USER:- MySQL Command to modify user details
'root'@'localhost':- enter root information along with the host
IDENTIFIED WITH mysql_native_password BY:- Change password socket_auth mechanism to mysql_native_password.
'password':- Ovehere pass the passphrase.
Output Query OK, 0 rows affected (0.00 sec)
Step 3 :- Run MySQL Workbench
The final step is to run & check whether the error is resolved. To run MySQL Workbench, press the super key and search for ‘MySQL Workbench’.
Go to the MySQL Connections options and click on the “Local Instance” to connect.
If it’s asking for your password, enter the password you have created from the above steps.
Why mysql workbench cannot connect to Database server?
Aforementioned that we’ll explain to you why this error occurs? There are many possibilities of the following error, but this error can be resolved using the above steps.
By default, MySQL uses socket_authentication to authenticate root users without entering passwords. Now the question is, what is socket_authentication? It is the mechanism through which the user does not need to enter a password for login.
And when you tried to access your Database through MySQL workbench, you end up with the above error.
To resolve that error, we have to use the traditional method to log in, and you can even Disable it permanently.
That’s all for How to resolve Cannot connect to a database server (MySQL workbench).
Read this to :- How to set or change the temporary hostname in Linux
If you are stuck somewhere, please feel free to comment down and If you like the article, or somewhere I missed something, please let me know to make this article more amazing.
A man with a tech effusive, who has explored some of the amazing technology stuff and exploring more, While moving towards, I had a chance to work on Android Development, Linux, AWS, DevOps with several Open source tools.
One of my life mottos “Always be lifelong Students.”