Remote connecting into a MySQL DB

Owned by Selvyn Wright
Last updated: Jul 22, 2017
2 min read

I recently had problems connecting into my MySQL server and couldn't understand what the problem was.  This was cause by an application I built t test the DB connection called "tesdbconnection" - can be found on my Bitbucket at 

shell command output
java -cp "target\testdbconnection-1.0.jar;target\mysql-connector-java-5.1.23-bin.jar" com.celestial.testdbconnection.MainUnit
Jul 22, 2017 6:47:12 PM com.celestial.testdbconnection.MainUnit log
INFO: On Entry -> DBConnector.connect()
On Entry -> DBConnector.connect()
Connection details:
dbDriver: com.mysql.jdbc.Driver
dbPath: jdbc:mysql://52.213.231.182/
java.sql.SQLException: null,  message from server: "Host 'cpc80981-perr17-2-0-cust82.19-1.cable.virginm.net' is not allowed to connect to this MySQL server"
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
        at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1110)
        at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2465)
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2498)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2283)
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:822)
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
        at java.lang.reflect.Constructor.newInstance(Unknown Source)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:404)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:317)
        at java.sql.DriverManager.getConnection(Unknown Source)
        at java.sql.DriverManager.getConnection(Unknown Source)
        at com.celestial.testdbconnection.DBConnector.connect(DBConnector.java:88)
        at com.celestial.testdbconnection.MainUnit.main(MainUnit.java:35)
Jul 22, 2017 6:47:13 PM com.celestial.testdbconnection.MainUnit log
INFO: On Exit -> DBConnector.connect()
On Exit -> DBConnector.connect()


After doing a lot of searching I found a useful test telnet into the server using the following command

command

telnet 52.213.231.182 3306

The server returned back  

Host 'cpc80981-perr17-2-0-cust82.19-1.cable.virginm.net' is not allowed to connect to this MySQL server

So I tried opening the port on the server using

shell command
iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j ACCEPT

This is also failed

So now I am trying to edit the my.cnf file.  This didn't solve the problem either.

FIXED

A school boy error that took me all day to solve.  I hadn't created a user that could access the DB remotely, I needed to run my script

code sample
use bclys_grad_cs_1917;
CREATE USER 'test-user'@'%' IDENTIFIED BY 'test-user';
CREATE USER 'test-user'@'localhost' IDENTIFIED BY 'test-user';
GRANT USAGE ON *.* TO 'mysql.sys'@'localhost';
GRANT TRIGGER ON `sys`.* TO 'mysql.sys'@'localhost';
flush privileges;
GRANT ALL PRIVILEGES ON *.* TO 'test-user'@'localhost' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'test-user'@'localhost' WITH GRANT OPTION;
flush privileges;
GRANT ALL PRIVILEGES ON *.* TO 'test-user'@'%' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'test-user'@'%' WITH GRANT OPTION;
flush privileges;

As soon as I ran this script it all started working