Cannot connect to Database server (MySQL workbench)

Question

When I am trying to click  "query database" under the database menu in the MySQL workbench, it gives me the following error:

Cannot Connect to Database Server

Your connection attempt failed for user 'root' from your host to server at 
127.0.0.1:3306:Can't connect to mysql server on '127.0.0.1'(10061)

Please:

 1. Check that mysql is running on server 127.0.0.1
 2. Check that mysql is running on port 3306 (note: 3306 is the default, but this can 
    be changed)
 3. Check the root has rights to connect to 127.0.0.1 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 127.0.0.1 connecting from the host address you're connecting from

 Can anyone help me solve it?

Answer 1

The problem is due to the socket authentication being enabled for the root user by default when there is no password set, while the upgrade to ubuntu 16.04.

The solution will be to revert back to native password authentication. You can do this by logging in to the MySQL using socket authentication by doing:

sudo mysql -u root

Once logged in:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

It would return back to the native (old default) password authentication.

Then you can use "password" as the password whenever required by MySQL.

Want to be a SQL expert? Come and join this SQL training and Certification by Intellipaat.