Cannot connect to port 3306 on a remote MySQL server using MySQL Workbench in Mac OS X

Question

I want to access MySQL remotely:

GRANT ALL ON *.* TO thufir@'192.168.0.16' IDENTIFIED BY 'hfdks4329vjkl';

When Workbench failed to connect, I took a look with ping and telnet:

Brents-MacBook:~ thufir$ 
Brents-MacBook:~ thufir$ ping 192.168.0.21
PING 192.168.0.21 (192.168.0.21): 56 data bytes
64 bytes from 192.168.0.21: icmp_seq=0 ttl=64 time=0.274 ms
64 bytes from 192.168.0.21: icmp_seq=1 ttl=64 time=0.499 ms
^C
--- 192.168.0.21 ping statistics ---
2 packets transmitted, 2 packets received, 0.0% packet loss
round-trip min/avg/max/stddev = 0.274/0.387/0.499/0.113 ms
Brents-MacBook:~ thufir$ 
Brents-MacBook:~ thufir$ telnet 192.168.0.21
Trying 192.168.0.21...
^C
Brents-MacBook:~ thufir$ 
Brents-MacBook:~ thufir$ telnet 192.168.0.21 3306
Trying 192.168.0.21...
^C
Brents-MacBook:~ thufir$ 

I'm installing coreutils and will have to learn how to use netstat on the Mac, and lsof -- apparently it's different.

The arrakis server is running OpenSuSE; it has closed ports?

Note: I haven't installed MySQL on the Mac, it gives me errors...

------------------------------------------------------------------------------update

MariaDB [mysql]> 
MariaDB [mysql]> GRANT ALL ON *.* TO `thufir`@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> 
MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> 
MariaDB [mysql]> select host, user, grant_priv from user;
+------------+--------+------------+
| host       | user   | grant_priv |
+------------+--------+------------+
| localhost  | root   | Y          |
| linux-k7qk | root   | Y          |
| 127.0.0.1  | root   | Y          |
| ::1        | root   | Y          |
| localhost  |        | N          |
| linux-k7qk |        | N          |
| %          | cron   | N          |
| localhost  | cron   | N          |
| localhost  | custom | N          |
| %          | custom | N          |
| %          | slave  | N          |
| %          | root   | N          |
| %          | thufir | N          |
+------------+--------+------------+
13 rows in set (0.00 sec)

MariaDB [mysql]> 

Solution

To enable networking in MySQL you need to make sure two things are handled correctly: MySQL grants and MySQL itself needs to have networking enabled:

Setting MySQL grants.

Your GRANT command seems odd to me:

GRANT ALL ON *.* TO thufir@'192.168.0.16' IDENTIFIED BY 'hfdks4329vjkl';

Why is the '192.168.0.16' in single quotes but the user is not? It might see that '192.168.0.16' as literal with the single quotes for the host. So try doing it like this:

GRANT ALL ON *.* TO '[email protected]' IDENTIFIED BY 'hfdks4329vjkl';

But when I grant permissions like this, there are two commands and they are structured like this:

GRANT USAGE ON *.* TO '[email protected]' IDENTIFIED BY 'thufir';
GRANT ALL PRIVILEGES ON *.* TO '[email protected]';

And then after setting grants you must flush the privileges table to get the new privileges to load in:

FLUSH PRIVILEGES;

And you should be good to go.

Enable MySQL networking.

But that said, check your my.cnf and if there is a bind-address setting like this:

bind-address = 127.0.0.1

That would bind MySQL to localhost (ie: 127.0.0.1) by default and is normal “out of box” security behavior for MySQL since exposing database servers to a network can be a security risk. So in this case—since you need networking enabled—you need to comment it out like this:

#bind-address = 127.0.0.1

And also make sure skip-networking is disabled, commented out like this:

#skip-networking

And then restart the MySQL server for the new settings to take effect.

Postscript.

Now all that said, the MySQL GRANT command you have uses the IP address 192.168.0.16 but all of your ping and telnet tests are for 192.168.0.21. So which IP address is it? When you set grants in MySQL, you do so based on the client IP address; not the server IP address.

So if the server has the IP address 192.168.0.16 and the client has the IP address 192.168.0.21, then the MySQL GRANT command should use 192.168.0.21 and not the 192.168.0.16 as your examples show. Maybe that is the issue?

Comments

[wurtel]

If ping works but a telnet times out, then the port is firewalled somewhere; probably incoming on arrakis although it could be possible that outpoing ports on the mac are blocked (but not probable). Note also that often mysql is bound to 127.0.0.1 to prevent attacks, so you'll need to modify the mysql server config (bind-address).

[Giacomo1968]

Posted an answer, but with that said, the MySQL GRANT command you have uses the IP address 192.168.0.16 but all of your ping and telnet tests are for 192.168.0.21. So which IP address is it? When you set grants in MySQL, you do so based on the client IP address; not the server IP address. More details in my full answer in the “Postscript” section, but maybe the issue is something as simple/basic as that.

[Thufir]

I'll have to try the postscript tomorrow.

[netadictos]

Your comment about skip-networking solved my problem, it is not mentioned in other sites and answers. Thx