How to Resolve Cannot Connect to Database Server (MySQL Workbench)

Not able to connect to the database server on MySQL Workbench? Solve this error with a simple ALTER query.

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 system, and once I try to log into a local database, I get the pop-up with the following error with the list of steps for troubleshooting.”

Your connection attempt failed for user ‘root’ to the MySQL server at localhost:3306: Access denied for user “root”@”localhost” (using password: YES)

Please:

  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 everything, but the problem persists.

This problem is no longer new to me, so I replied to her with the steps to resolve the error.

If you are also getting a similar kind of error, then you don’t need to look any further because in this article, we will see how to resolve that error.

Steps to Solve Database Connection

I believe you are in a hurry to solve this problem, so first we will see how to resolve the cannot connect to database server error, and then we will explain to you why this error occurred.

Step 01Login to MySQL with the root User

You need to login to MySQL as the root user to make changes. To do that, open a terminal window using Ctrl + Alt + T and run the following command:

$ sudo mysql -u root

Step 2 Alter or Change the 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 the password socket_auth mechanism to mysql_native_password
  • 'password': Over here, pass the new passphrase/password
Output

Query OK, 0 rows affected (0.00 sec)

Step 3Run MySQL Workbench

The final step is to run and 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 “Local Instance” to connect.

Cannot connect to database server mysql workbench: Login Prompt
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 already mentioned 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 the 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).

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 even more amazing.

0 0 votes
Article Rating
Subscribe
Notify of
guest
4 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Manos Georgiou
Manos Georgiou
2 years ago

Solved my problem right away. Fast and simple explanation straight to the point. Thank you!

Jovane
1 year ago

Thank you!

Atul
Atul
1 year ago

How to resolve this problem for a company database where we cannot alter password. I stuck in this error not for a local but some other database which gave access to me with a password.