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 into 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)
Please:
- Check that MySQL is running on address localhost.
- Check that MySQL is reachable on port 3306 (note: 3306 is the default, but this can be changed).
- 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).
- 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 everything, but the problem persists.
This problem is no longer 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.
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 the root user
You need to login into MySQL with the root user to make changes for that open a terminal window using Ctrl + Alt + T and run 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': Specifying root information along with the host IDENTIFIED WITH mysql_native_password BY: Change password socket_auth mechanism to mysql_native_password. 'password': Over here pass the new 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.
Run MySQL Workbench without any error
If it’s asking for your password, enter the password you have created from the above steps.
Why can’t MySQL Workbench connect to a database server?
I have aforementioned that we’ll explain to you why this error occurs. There are many possibilities for the following error, but this error can be resolved using the above steps.
By default, MySQL uses socket_authentication to authenticate root users without requiring passwords. Now the question is, what is socket_authentication? It is the mechanism through which the user does not need to enter a password to log in.
And when you try 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.
Wrapping Up
That’s all for how to resolve can not connect to a database server (MySQL workbench).
Read this to :- Completely uninstall MySQL-server in 3 simple steps
If you are stuck somewhere, please feel free to comment, and if you like the article, or if 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 is exploring more. While moving towards, I had a chance to work on Android development, Linux, AWS, and DevOps with several open-source tools.
