#Failed #connect #MYSQL #workbench #EC2 #DevOps

Error :

Failed to connect to mysql at server ip with user root access denied for user ‘root’@’localhost'(using password:YES)


1. Open and edit /etc/my.cnf or /etc/mysql/my.cnf, depending on your distribution.

2. Add skip-grant-tables under [mysqld]

3. Restart MySQL

4. You should be able to log in to MySQL now using the below command mysql -u root -p

5. Run mysql> flush privileges;

6. Set new password by ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘NewPassword’;   (here don’t log from the same screen open another ssh session for the same server and remove the key skip-grant-tables and stop and start the mysql but don’t exiting for another where you are already in the mql)

7. Go back to /etc/my.cnf and remove/comment skip-grant-tables

8. Restart MySQL

9. Now you will be able to login with the new password mysql -u root -p

Here might still you face error , if yes follows

Run the from the sql screen where you already in

SELECT user,authentication_string,plugin,host FROM mysql.user;


+——————+——————————————-+———————–+———–+|user|authentication_string                     |plugin                |host      |+——————+——————————————-+———————–+———–+|root             ||auth_socket           |localhost ||mysql.session    |*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |mysql_native_password |localhost ||mysql.sys        |*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |mysql_native_password |localhost ||debian-sys-maint |*CC744277A401A7D25BE1CA89AFF17BF607F876FF |mysql_native_password |localhost |+——————+——————————————-+———————–+———–+4rowsinset(0.00sec)

In this example, you can see that the root user does in fact authenticate using the auth_socket plugin. To configure the root account to authenticate with a password, run the following ALTER USER command. Be sure to change password to a strong password of your choosing, and note that this command will change the root password you set in Step 2:

ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;

Then, run FLUSH PRIVILEGES which tells the server to reload the grant tables and put your new changes into effect:


Check the authentication methods employed by each of your users again to confirm that root no longer authenticates using the auth_socket plugin:

SELECT user,authentication_string,plugin,host FROM mysql.user;


+——————+——————————————-+———————–+———–+|user|authentication_string                     |plugin                |host      |+——————+——————————————-+———————–+———–+|root             |*3636DACC8616D997782ADD0839F92C1571D6D78F |mysql_native_password |localhost ||mysql.session    |*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |mysql_native_password |localhost ||mysql.sys        |*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |mysql_native_password |localhost ||debian-sys-maint |*CC744277A401A7D25BE1CA89AFF17BF607F876FF |mysql_native_password |localhost |+——————+——————————————-+———————–+———–+4rowsinset(0.00sec)

You can see in this example output that the root MySQL user now authenticates using a password. Once you confirm this on your own server, you can exit the MySQL shell:


NOW open another screen and test your solution 




From <https://stackoverflow.com/questions/50177216/how-to-grant-all-privileges-to-root-user-in-mysql-8-0>

Source :- 




ahmedaljanahy Creative Designer @al.janahy Founder of @inkhost I hope to stay passionate in what I doing

Leave a Reply

Your email address will not be published. Required fields are marked *