Error accessing MySQL database remotely with Java

Question

Hello to the beloved StackOverflow Community.

So, I am new to Java and MySQL development and, long story short, I'm trying to make devices communicate over the internet using a MySQL database (and the tools provided by MySQL Workbench (v5.6) ), my PC as a server and a Java programm that I created. And all that with no providing a domain name, but my server IP. I've done my research, but now I'm in the point where I have to cry for help.

The Problem: It seems that all are working JUST FINE when my connect variable gets connection from localhost:3306, BUT I just want to access it from the internet and I tried various things that just didn't work out at all.

Failure 1 - I tried to manipulate the my.ini file on C:\ProgramData\MySQL Workbench 5.6\ setting the bind-address to 127.0.0.1, then to my IPv4 I got from ipconfig (CMD) and then to 'mypublicip' from http://www.whatismypublicip.com/ .

and it didn't work.

Failure 2 - I tried to edit the connection from MySQL Workbench -> Hostname= 'mypublicip', port=3306, Username and Password as it was when I was testing it with Hostname=localhost.

That just didn't work for the MySQL Workbench. I could not connect to my server from Workbench and when I tried to set up the Remote Management dialog I didn't know what works and what doesn't.

Failure 3 - I tried keeping as Hostname=localhost at Workbench and I changed the getConnection() method to return connection from the 'mypublicip' ip. I couldn't work with my IPv6 (I don't know how to use it in getConnection()), so I used a public id I found from http://www.whatismypublicip.com/ . When I did that an Error Occured saying this: "Cannot connect to database server. Communications link failure. The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server." . That's why I tried the failure no.1

That's the code from my Java App that creates the connection between the programm and the db. connectMessage is a JFrame that gives the suitable message to the user about the success or the failure of the connection, the error code and the error message:

package aPackage;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
*
* @author manosmer
*/
public class MySQLConnector {
    private Connection connect;

    public MySQLConnector(){
        connect = null;
    }

    public Connection connecting(UsersInfo user) throws ClassNotFoundException{
        try{
            Class.forName("com.mysql.jdbc.Driver");           
            connect = DriverManager.getConnection("jdbc:mysql://localhost:3306/connectmessages", user.getName(), user.getPassword());

            connectMessage messageconn = new connectMessage();
            messageconn.setConnection(connect, user);

        }catch(SQLException ex){
            UserLogin errorlogin = new UserLogin();
            connectMessage messageconn = new connectMessage();
            messageconn.failed(ex);
        }
        return connect;
    }  
}

Answer 1

I found the problem! The real problem was that I did not open a port at my router firewall for the program to have access via internet! Go to your Internet Wizard, make a port (the same your MySQL Server uses) and apply it to your server machine. Then you have to find your IP and use it in your code. That's all! Happy coding.

Answer 2

If your sql server behind router, try to reroute traffic from some router port to your server MySql port.

Answer 3

Everything you have tried so far is the exact opposite of what you should have been doing. Causing the MySQL server to bind to 127.0.0.1 or 'localhost' will prevent any connection from outside the localhost.

What you need to do is have it bind to 0.0.0.0 and open port 3306 in your firewall.