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

Question

I'm trying to write a sample jdbc program to connect to mysql db which is in the remote machine

    // JDBC driver name and database URL
    String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    String DB_URL = "jdbc:mysql://<remote machine>/<database>"

    //  Database credentials
    String USER = "root";
    String PASS = "mnlinux";
    Class.forName(JDBC_DRIVER);
    Connection con = DriverManager.getConnection(DB_URL, USER, PASS);
    Statement stmt = con.createStatement();
    String sql = "SELECT x FROM t";
    ResultSet resultSet = stmt.executeQuery(sql);
    while(resultSet.next()){
        //Retrieve by column name
        int id  = resultSet.getInt("x");
        //Display values
        System.out.print("ID: " + id);
    }
    resultSet.close();
    stmt.close();
    con.close();


*Its returning Exception in thread "main" java.sql.SQLException: Access denied for user 'root'@'CurrentMachine' (using password: YES)*

while executing DriverManager.getConnection(DB_URL, USER, PASS);

Please help me to connect to the db. My MySQL is up & running. Both dbserver & currentmachine are in same network (i'm able ping).

Thanks in advance.

Answer 1

Allow full access to database from any ip OR replace % with your IP from where do you want to access database and try.

GRANT ALL PRIVILEGES ON *.* TO '<mysql user>'@'%'  WITH GRANT OPTION;
FLUSH PRIVILEGES ;

Answer 2

You need to give permission for that user

GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

MySql requires permission user as well as connecting remote address.

Answer 3

Normally the root MySQL user can't connect from a remote machine, unless you edit it.

If you have MySQL Workbench running on current machine, try connecting over the network with the credentials you are using in your java. If it doesn't work from Workbench, it obviously won't work from java.

You can use the following SQL to create a new user:

CREATE USER 'someuser'@'%' IDENTIFIED BY 'somepassword';
GRANT ALL PRIVILEGES ON yourdatabase.* TO 'someuser'@'%' WITH GRANT OPTION;

or use Workbench to do it (on the MySQL machine). Connecting as root is generally not advised, but it's ok for getting things up and running. Eventually you should switch to a dedicated user and depending on your application, implement proper security.

PLEASE NOTE: some answers above suggest adding privileges... however, the root user already has these privileges and failure connecting with root is not a privilege issue. It's because root can't connect from a remote machine. SO... add a new user and grant that user permissions.