1

I have a MySQL database which i access through Java JDBC and it works correct in my local network(using localhost or 192.168.*.* local adress). But i also want to access it remotely from internet and when i connect to it using my global adress 176.214.186.243 (it is dynamyc adress but it stays still for 2 days of my tries) i get next error com.mysql.cj.jdbc.exceptions.CommunicationsException: 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. In MySQL workbench i have priveleges showed on Picture(All ot them. But i still cant connect to database using global IP.

What am i doing wrong?

Bukas
  • 35
  • 1
  • 8
  • You have mentioned nothing about having completed the necessary network address translation port mapping in the router/firewall device that is connecting your 192.168.x.x private network to the Internet, and it is not possible for this to work without that having been put in place. – Michael - sqlbot Sep 22 '18 at 23:54
  • You really should not do this. It is unsafe. – Mark Rotteveel Sep 23 '18 at 07:26
  • As i figured? the problem was with port forwarding. Router wasn't sending packets comitg from web to my machine – Bukas Oct 15 '21 at 18:30

2 Answers2

0

you should grant the specified user the privilege to access from a certain IP address like:

GRANT [privileges|ALL PRIVILEGES] ON schema.table TO 'username'@'hostname' IDENTIFIED BY 'password'
WITH GRANT OPTION; FLUSH PRIVILEGES;

  • privileges can be operation names like select, drop and ... or if you want to grant all the privileges to a user set "ALL PRIVILEGES"
  • schema.table can just specified a single table or it may be something like . to support all the schemas and tables.
  • username which is also called role is abvious but it can be % to grant all the users the given privileges.
  • hostname can be ip or host name or as like as username can be % to grant the specified users the given privileges.

for further information use can follow https://dev.mysql.com/doc/refman/8.0/en/grant.html, https://stackoverflow.com/a/6239145/2137378

The mysql documentation is much more prefered. check you mysql version and continue the exact versions documentation on mysql.

Definitely, above descriptions are to let the mysql engine know its remote users and if there is some obstacles between these two like firewall should be resolved somehow. e.g. if you are on ubuntu you can check if the firewall is the main problem by disabling it using: 

sudo ufw disable

After disabling the firewall and granting the privileges, remote access was ok then you know the firewall is the issue. to enable if use:

sudo ufw enable
Amin Heydari Alashti
  • 881
  • 1
  • 9
  • 20
  • I would create another user beside root and grant ONLY the permission the app needs. Also, is your machine actually hooked to the internet, and are the ports configured to allow the proper database traffic to flow from the internet, through any firewalls and right to your machine? – Matt Runion Sep 22 '18 at 19:37
  • @mrunion I am using not root user and i've opened all connections in firewall. Can i somehow check availability of database from net? Can my dynamic IP cause this troubles? – Bukas Sep 22 '18 at 19:44
  • Your dynamic IP will cause problems, yes. When it changes your code will no longer have the correct IP and will not be able to connect. – Matt Runion Sep 22 '18 at 19:45
  • there is some modification for further descriptions about granting privileges and of course your questions discussed in comments. – Amin Heydari Alashti Sep 23 '18 at 02:28
0

mysql restricts access for any combination of user, table and remote address. In your case the user most likely can access everything locally, but not from extern (e.g for root this would be a good thing). This can be changed by:

GRANT ALL ON <database>.* TO 'client'@'%.%.%.%' IDENTIFIED BY '<a password for external usage>';
FLUSH PRIVILEGES;
  • <database>.* grants access to alle tables in <database>
  • I already added your username which seems to be client
  • %.%.%.% means any IP Adress where the 1.-4. digit can be anything
    • maybe you can limit the range of IPs or use a FQDN instead
  • <a password for external usage> is just for this connections and can be something else.

More infos here

DerMolly
  • 464
  • 5
  • 17
  • I tried to write this command for a lot of times but i always get similar error. For now i type `GRANT ALL ON smartooth.* TO client@%.%.%.% IDENTIFIED BY '1234';` and get next error `[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%.%.%.% IDENTIFIED BY '1234'' at line 1`. I tried different variations of `%.%.%.%` such as % or direct adress, and it still same error. I am using MySQL 8.0 version – Bukas Sep 22 '18 at 19:54
  • Which version of mysql are you using? – DerMolly Sep 22 '18 at 19:58
  • I am using MySQL 8.0 version – Bukas Sep 22 '18 at 20:01
  • try to quote the user and host like so `'client'@'%.%.%.%'` – DerMolly Sep 22 '18 at 20:03
  • Well, using quotes like `GRANT ALL ON smartooth.* TO 'client@%.%.%.%' IDENTIFIED BY '1234';` i get next error `[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY '1234'' at line 1` so i suppose it doesn't like IDENTIFIED part – Bukas Sep 22 '18 at 20:08
  • There should be a `'` before and after the `@` and not around the whole user@host string – DerMolly Sep 22 '18 at 20:09
  • Writing so `GRANT ALL ON smartooth.* TO 'client'@'%.%.%.%' IDENTIFIED BY '1234'` i still get the same error. I've tried that command for a lot of times whith all quotes combinations and error is always same – Bukas Sep 22 '18 at 20:13
  • Is there any ways to do it with workbench without using commands? – Bukas Sep 22 '18 at 20:16
  • Thanks for helping, i gonna try to fully reinstall MySQL and see what happens. I will mark your answer as accepted) – Bukas Sep 22 '18 at 20:24