java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)

This is specific to Ubuntu 18.04 LTS and MySQL 5.x Followed this link Follow everything from here onwards:

sudo mysql_secure_installation

sudo mysql

Once logged into MySQL then from the MySQL prompt execute these commands:

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

Now verify that the table has the password for the root

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

This solved my issue and now i am able to login.

This appears to mostly happens when the MySQL username and password are not correct. Check your MySQL username and password.

I am using Spring Boot 2.2.6(Windows) and faced the same issue when I tried the run the application: java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)

What solved my problem:

1. Create a new user (from the MySQL workbench or a similar GUI which you might be using)
2. Grant DBA priviledges (Tick the DBA checkbox) also from the GUI
3. Run the spring boot application.

Or follow the @evg solution to grant privilegdes from command line in Linux env.

This Exception is also caused due to version mismatch of mysql db and your pom.xml/jar.

Make sure your pom.xml/jar version is higher than your mysql db version. Because higher versions are compatible with lower version's, but the same is not true for the inverse.

Solution's for java project

• Replace the Jar with suitable version.

Solution for maven based

• Change the dependency Version in pom.xml

Solution for spring boot - Override the spring boot dependency by adding 5.1.5.Final

My application.properties looked something like this

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/mydb

spring.datasource.username=root

spring.datasource.password=root

The only thing worked for me is to maven clean and then maven install.

In my case, I created a new user with name admin and used it...

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost'
WITH GRANT OPTION;

CREATE USER 'admin'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%'
WITH GRANT OPTION;

I had to change my SQL setup (in my host, Dreamhost in my case) to allow the username to access the database from other hosts, not just Dreamhost since I'm running a program from the IDE on my computer. I did that for now by adding % to the list of Allowable Hosts. It now works!

The order of declaring:

• Driver
• Connection string
• username
• password

If we declaring in order

• Connection string
• username
• password
• Driver

the application will fail

1. use correct jar (with correct version)
2. give root user host-independent access or create a user

For me the solution worked as by changing the url format to: con=DriverManager.getConnection("jdbc:mysql://localhost/w3schools?user=###&password=###"); make sure to add proper jar to class path and use the driver Class.forName("com.mysql.jdbc.Driver");

This resolved issue for me.

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

GRANT ALL PRIVILEGES ON *.cfe TO 'root'@'%' IDENTIFIED BY 'password';

FLUSH PRIVILEGES;

I was also facing the same issue, My connection string is valid and username and password also valid, even user has sufficient privileges to access database.

I solved this issue by deleting temporary folder created by MySQL(Please take backup before deleting any folders so in case if it not works then you can place that files again).

Default folder location for temp and connection files of MySQL in Windows is:

C:\ProgramData\MySQL

I deleted(taken backup) of below folder:

C:\ProgramData\MySQL\MySQL Server \Data\sys

Note: Before deleting any items make sure that MySQL service is not started or else it would not allow to delete any files

And It works for me.

When I'm running a springboot project, the application.yml configuration is like this：

server:
  port: 8080
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/lof?serverTimezone=GMT
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver

Notice that there isn't quotation marks around the password. And I can run this project in my windows System.

But when I try to deploy to the server, I have the problem and I fix it by changing the application.yml to:

server:
  port: 8080
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/lof?serverTimezone=GMT
    username: root
    password: "root"
    driver-class-name: com.mysql.cj.jdbc.Driver

Add port number (something like 3306) in:

Connection c = DriverManager.getConnection("jdbc:mysql://localhost:urport","root","root");

It's working fine for me while using same password after executing the following statement:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';

spring.jpa.generate-ddl=true
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
spring.jpa.hibernate.ddl-auto=create-drop
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=
spring.datasource.url=jdbc:mysql://localhost:3306/DATABASE_NAME? useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

Go to your phpmyadmin database

Create a database called DATABASE_NAME and go to priviledges and check the users who have priviledges in the database and use them the default is "root"

Due to below reason I created new user and password for mysql, now I am able to connect successfully. if your password is in encoded format please create new password

https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql#:~:text=There%20is%20a,mysql_native_password%20plugin%20instead%3A

There is a known issue with some versions of PHP that causes problems with caching_sha2_password. If you plan to use this database with a PHP application — phpMyAdmin, for example — you may want to create a user that will authenticate with the older, though still secure, mysql_native_password plugin instead:

If you are connecting a remote mysql server from your local machine using java see the below steps. Error : "java.sql.SQLException: Access denied for user 'xxxxx'@'101.123.163.141' (using password: YES) "

for remote access may be cpanel or others grant the remote access for your local ip address.

In the above error message: "101.123.163.141" is my local machine ip. So First we have to give remote access in the Cpanel-> Remote MySQL®. Then run your application to connect.

Class.forName("com.mysql.jdbc.Driver");  
Connection con=DriverManager.getConnection(  
    "jdbc:mysql://www.xyz.com/abc","def","ghi");  
//here abc is database name, def is username and ghi        
Statement stmt=con.createStatement();  
ResultSet rs=stmt.executeQuery("select * from employee");  
    while(rs.next())  
        System.out.println(rs.getInt(1)+" "+rs.getString(2)+"  
                          "+rs.getString(3));  
con.close();    

Hope it will resolve your issue.

Thanks

I also have this problem, this solved it.

Change the:

Sring url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&user=root&password=password";

Set:

"serverTimezone=UTC" is "Unified standard world time". "useUnicode=true&characterEncoding=UTF-8" is "Solve Chinese garbled".

Although my database have not any Chinese words. But it is working. Reference from https://www.cnblogs.com/EasonJim/p/6906713.html

If you are using a MySql workbench, go to the home page of workbench, right click on your database, copy the JDBC connection string, and paste it into the Java program.

be sure that the port you are using in:

spring.datasource.url=jdbc:mysql://localhost:8000/database

is the same that MySQL uses on your machine: check for it with this command in MySQL CLI:

SHOW VARIABLES WHERE Variable_name = 'port';