Remote Connection to MYSQL (1 Viewer)

Banana said:

According to this page...


Basically you need to create an entry in the host table to allow your username to log in from the client's IP.

To put it in an concrete example, here's how I have my user table set up:

Code:Copy to clipboard

mysql> select user, host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | localhost | 
+------+-----------+
1 row in set (0.00 sec)

What this means that MySQL server will only accept connection from only user named 'root' from host 'localhost' (e.g. the same computer the MySQL is running on). It will not accept anyone else anywhere. If I wanted to allow my another computer on the same network to access to the MySQL, using the same user name, I have to add a new entry in the table:

Code:Copy to clipboard

mysql> grant select on test.* to 'root'@'192.168.0.%';
Query OK, 0 rows affected (0.62 sec)

Note that while I used the same user, 'root', I assigned a wildcard IP meaning that the root can connect to the MySQL any where as long the IP is between 192.168.0.1 to 192.168.0.255, effectively within the network only.

The user table now looks like this:

Code:Copy to clipboard

mysql> select user, host from mysql.user;
+------+-------------+
| user | host        |
+------+-------------+
| root | 192.168.0.% | 
| root | localhost   | 
+------+-------------+
2 rows in set (0.00 sec)

You also need to grant the appropriate permissions (e.g. SELECT, INSERT, UPDATE, etc.)

More info on GRANT statement.

HTH.

Click to expand...