Remotely connecting to MySQL

August 25, 2014 12.1k views
dan701258
By:
dan701258

Tried every tutorial that I've found about remotely connecting to the MySQL database. I've commented out the bind-address from my.cnf and used GRANT PRIVILEGES ON root@% for all databases.

But I still get the error:
SQLSTATE[HY000] [2003] Can't connect to MySQL server on 'IP ADDRESS' (61)

when connecting from a different server. While connecting locally with phpMyAdmin works.

Any idea what I can do to get this working?

1 comment
  • Your root account, and this statement applies to any account, may only have been added with localhost access (which is recommended).

    You can check this with:

    SELECT host FROM mysql.user WHERE User = 'root';
    If you only see results with localhost and 127.0.0.1, you cannot connect from an external source. If you see other IP addresses, but not the one you're connecting from - that's also an indication. If you see %, well then, there's another problem altogether as that is "any remote source".

    You should be able to add this remote access with:

    GRANT ALL PRIVILEGES ON . TO 'root'@'%';
    FLUSH PRIVILEGES;

    From: http://stackoverflow.com/questions/19101243/error-1130-hy000-host-is-not-allowed-to-connect-to-this-mysql-server

3 Answers

Do you use phpMyadmin? If so, it is quite easy to log in, create a new user, then give that user permissions for various hosts when setting the login info.

So, if you have a static Ip, you just set that
If you have a dynamic IP, and your IP is named caltel, you could do: %.caltel.com
If your dynamic IP is always within a block of IP's you can set this: 75.18.%.%

Then, be sure to log in using that new User you just created.

I always use a separate user for remote logins rather than setting things globally or for root. The last thing you want is a user named root to be available from remote locations.

Something longer and a tad more complex: mysite_dbadmin or something.
Also, in the past I have had issues with special characters in db passwords..I never explored why that was, I just switched to using passwords like: My99Super8899177Password
(as opposed to something like: #3y^?>44tyos"")

add or change this in your my.cnf

bind-address = your.public.ip

then restart mysql

Remove bind-address entries (so it just reads bind-address=). This will allow MySQL to use localhost, public_ip and any private ips to access MySQL.
Setup iptables to restrict access to port 3306 to the IP address / IP range that will remotely access your MySQL instance.
Don't be tempted to allow root to be accessed from anywhere but localhost ;-)

If you are accessing MySQL from another DigitalOcean droplet setup private networking between the two droplets. DO Documentation here explains how

Have another answer? Share your knowledge.