MySQL Remote Access Setup via LAN on macOS

Posted on | Post modified: | Visitors:
Words count in article: 267 | Reading time ≈ 2

Remote User Access Setup

Remote User Access in MySQL is allowed, only the remote root user access is disabled by default. To enable that, run:

1
2
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

You may also need to change bind-address in /etc/mysql/my.cnf from 127.0.0.1 to 0.0.0.0 to enable access for all IP address.

To add non-root remote user, run:

1
mysql> CREATE USER 'user'@'ipaddress' IDENTIFIED BY 'password';

Then grant privileges, for granting all privileges, run:

1
mysql> GRANT ALL ON databasename.* TO 'user'@'ipaddress';

Or for granting all privileges on all databases, run:

1
mysql> GRANT ALL ON *.* TO 'user'@'ipaddress';

Or for granting specific privilege(s), run:

1
mysql> GRANT privilege1(, privilege2,...) ON databasename.* TO 'user'@'ipaddress';

Finally:

1
mysql> FLUSH PRIVILEGES;

Remote User Access Setup via LAN using Apple’s Bonjour Service

Multicast DNS is one of the features of Bonjour (formerly “Rendezvous”), included in Mac OS X 10.2 or later. It allows you to connect via Internet protocol (IP) to other computers on a local network by name, rather than a numbered address.

The local hostname (or local network name) can be found in System Preferences > Sharing , which ends with .local . For setting up remote access for multiple devices supporting this feature under the same local network, you could simply replace your ip address with your local hostname.

For instance, replace:

1
mysql> CREATE USER 'user'@'ipaddress' IDENTIFIED BY 'password';

With:

1
mysql> CREATE USER 'user'@'localhostname.local' IDENTIFIED BY 'password';