How to allow remote connection to mysql

Question

I have installed MySQL Community Edition 5.5 on my local machine and I want to allow remote connections so that I can connect from external source.

How can I do that?

Answer 1

If you installed MySQL from brew it really does only listen on the local interface by default. To fix that you need to edit /usr/local/etc/my.cnf and change the bind-address from 127.0.0.1 to *.

Then run brew services restart mysql.

Answer 2

If mysqld has a bind address set to a loopback/local address (e.g. 127.0.0.1), the server will not be reachable from remote hosts, because a loopback interface cannot be reached from any remote host.

Set this option to 0.0.0.0 (:: for IPv4+6) to accept connections from any host, or to another externally-reachable address if you want to only allow connections on one interface.

Source

Answer 3

Just a note from my experience, you can find configuration file under this path /etc/mysql/mysql.conf.d/mysqld.cnf.

(I struggled for some time to find this path)

Answer 4

If your MySQL server process is listening on 127.0.0.1 or ::1 only then you will not be able to connect remotely. If you have a bind-address setting in /etc/my.cnf this might be the source of the problem.

You will also have to add privileges for a non-localhost user as well.

Answer 5

And for OS X people out there be aware that the bind-address parameter is typically set in the launchd plist and not in the my.ini file. So in my case, I removed <string>--bind-address=127.0.0.1</string> from /Library/LaunchDaemons/homebrew.mxcl.mariadb.plist.

Answer 6

For whom it needs it, check firewall port 3306 is open too, if your firewall service is running.