Can't connect to MySQL database over local network

Question

I have two macbooks at my home connected via Wi-Fi to my router. One of them has a MySQL server installed. I want to access a MySQL DB from the second mac. I found the internal IP address of the first mac and tried to use mysql -u <user> -p -h <internal IP> and I get

ERROR 2003 (HY000): Can't connect to MySQL server on '<internal IP>:3306'

To test the issue I pinged the internal IP of the first mac from the second mac and it worked. When I try telnet <internal IP> 3306 from the second mac, I get

Trying <internal IP>...
telnet: connect to address <internal IP>: Connection refused
telnet: Unable to connect to remote host

Just to make sure that I got the port correct, I try telnet 127.0.0.1 3306 on the first mac and it works. So this is probably some network configuration issue. I tried setting bind-address = 0.0.0.0 in the my.cnf file but it didn't help.

Does anyone has an idea? thanks

Answer 1

I figured out the issue. The solution is to create a new MySQL user with the relevant privileges like so

create user '<user>'@'192.168.1.%' identified by '<password>';
grant all privileges on *.* to '<user>'@'192.168.1.%' with grant option;

And to change bind-address = 127.0.0.1 to bind-address = 0.0.0.0 in the my.cnf file (for me its path is /usr/local/etc/my.cnf). And then it works!