MySQL remote access

Mysql remote access - Can't connect to MySQL server on

Let's say you want to connect to a remote MySQL server from your home computer. Consider the following configuration:

MySQL server IP address: 78.46.170.169
Your Home PC IP address: 8.8.8.9

By default, MySQL accepts connections only from a localhost. Thus, executing the command:

#mysql -u root -p -h 78.46.170.169

will produce the following error:

ERROR 2003 (HY000): Can't connect to MySQL server on '78.46.170.169' (111)

Configure MySQL bind address

In order to set a MySQL remote access, you will need to change the default MySQL settings in the configuration file. What you have to do is to set the MySQL bind address to server's public IP address. In Ubuntu, my.cnf configuration file can be found under /etc/mysql/. If you are using other Linux distribution you can search it with the following command:

#find / -name 'my.cnf'

Next, just open the file with your favorite editor and search for the next line

bind-address = 127.0.0.1

By default, MySQL is using the loop back as a bind address. This directive tells MySQL server to listen on the localhost IP address and accept connections only on this address. You will need to change it to server's public address.

Bind-address = 78.46.170.169

Next, save the file and restart the MySQL server with one of the following commands:

#service mysql restart

or

#/etc/init.d/mysql restart

Configure MySQL remote access

Fire up your MySQL client and execute the following queries.

mysql> use mysql;
mysql> update user set host = '8.8.8.9' where user = 'root';

This will enable MySQL remote access only from 8.8.8.9 with the user 'root'. If you have dynamic IP address you can use the % sign.

mysql> update user set host = '8.8.%.%' where user = 'root';

Now, every IP address that is on the same subnet can access to the MySQL server. If you want to connect to MySQL from any address just set the host value to %.

mysql> update user set host = '%' where user = 'root';

Restart MySQL server and test the new configuration.

#mysql -u root -p -h 8.8.8.8
- Posted by Eva to MySql