[java] How to connect to a remote MySQL database with Java?

I am trying to create a JSF application using the Eclipse IDE. I am using a remote mySQL server as my database. How do I connect to this remote database for creating tables and accessing them?

1. Create a new user in the schema ‘mysql’ (mysql.user) Run this code in your mysql work space “GRANT ALL ON . to user@'%'IDENTIFIED BY '';

2. Open the ‘3306’ port at the machine which is having the Data Base. Control Panel -> Windows Firewall -> Advance Settings -> Inbound Rules -> New Rule -> Port -> Next -> TCP & set port as 3306 -> Next -> Next -> Next -> Fill Name and Description -> Finish ->

3. Try to check by a telnet msg on cmd including DB server's IP

Close all the connection which is open & connected to the server listen port, whatever it is from application or client side tool (navicat) or on running server (apache or weblogic). First close all connection then restart all tools MySQL,apache etc.

Plus, you should make sure the MySQL server's config (/etc/mysql/my.cnf, /etc/default/mysql on Debian) doesn't have "skip-networking" activated and is not binded exclusively to the loopback interface (127.0.0.1) but also to the interface/IP address you want connect to.

On Ubuntu, after creating localhost and '%' versions of the user, and granting appropriate access to database.tables for both, I had to comment out the 'bind-address' in /etc/mysql/mysql.conf.d/mysql.cnf and restart mysql as sudo.

bind-address = 127.0.0.1

You need to pass IP/hostname of the rempote machine in the connection string.

import java.sql.*;
import javax.sql.*;

public class Connect
{
   public static void main (String[] args)
   {
       Connection conn = null;

       try
       {

           String url = "jdbc:mysql://localhost:3306/mydb";
           Class.forName ("com.mysql.jdbc.Driver");
           conn = DriverManager.getConnection (url,"root"," ");
           System.out.println ("Database connection established");
       }
       catch (Exception e)
       {
           e.printStackTrace();

       }
       finally
       {
           if (conn != null)
           {
               try
               {
                   conn.close ();
                   System.out.println ("Database connection terminated");
               }
               catch (Exception e) { /* ignore close errors */ }
           }
       }
   }
}

in my.cnf file , please change the following

## Instead of skip-networking the default is now to listen only on ## localhost which is more compatible and is not less secure. ## bind-address = 127.0.0.1

Just supply the IP / hostname of the remote machine in your database connection string, instead of localhost. For example:

jdbc:mysql://192.168.15.25:3306/yourdatabase

Make sure there is no firewall blocking the access to port 3306

Also, make sure the user you are connecting with is allowed to connect from this particular hostname. For development environments it is safe to do this by 'username'@'%'. Check the user creation manual and the GRANT manual.

to access database from remote machine , you need to give grant all privileges to you data base.

run the following script to give permissions: GRANT ALL PRIVILEGES ON . TO user@'%' IDENTIFIED BY 'password';