Connect to a remote MySQL Server from a dynamic IP address

Question

My ISP at home uses a dynamic IP address. I'm trying to access a MySQL database at work. How can I connect to it (using MySQL Workbench)?

I could grant all privileges to % so that any IP address will do, but I think this is NOT VERY SAFE. I don't know if granting privilege to my current IP Address will work.

If it does, it will still be tedious to grant privilege every time the IP changes.

Thanks in advance!

Answer 1

Do you have the possibility to connect to your office network using a VPN? Then you could receive a static IP address in the range of your internal network. For this one, you could easily grant the necessary privileges.

• John Bautista: never tried VPN before. But I think it is possible. Currently I'm using TeamViewer and I see a VPN version. I'll check that out. Thanks.
• John Bautista: I can't access it using VPN :(

Answer 2

It looks like you can grant using a fqdn rather than an IP address - if that is right then you could make use of a dynamic DNS service such as DynDNS to get a stable DNS name that points to whatever your current home IP address is

Answer 3

You may need to contact your ISP with assistance on finding out your static IP. Once your find out, simple add root@'yourstaticIP' with the necessary grants.

You should also use the following options in /etc/my.cnf and restart mysql

[mysqld]
skip-host-cache
skip-name-resolve

Believe me, you need skip-host-cache and skip-name-resolve. Connecting to mysql without these options causes MySQL to authenticate via DNS, which is slow.

Answer 4

Connecting via DNS might be slower than IP but it is safer. I don't think setting skip name resolve is a good idea if this is a production server.

• RolandoMySQLDBA: This is just a valid a reason is mine, although contrary to what I was thinking !!! +1 !!!