Error :

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

Solution

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;

Output

+——————+——————————————-+———————–+———–+|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:

FLUSH PRIVILEGES;

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;

Output

+——————+——————————————-+———————–+———–+|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:

exit

NOW open another screen and test your solution 

mysql>CREATEUSER’root’@’%’IDENTIFIED BY’PASSWORD’;

mysql>GRANTALLPRIVILEGES ON*.*TO’root’@’%’WITHGRANTOPTION;

mysql>FLUSH PRIVILEGES;

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

Source :- 

https://stackoverflow.com/questions/41645309/mysql-error-access-denied-for-user-rootlocalhost