Let's say you want to connect to a remote MySQL server from your home computer. Consider the following configuration:
MySQL server IP address: 220.127.116.11 Your Home PC IP address: 18.104.22.168
By default, MySQL accepts connections only from a localhost. Thus, executing the command:
#mysql -u root -p -h 22.214.171.124
will produce the following error:
ERROR 2003 (HY000): Can't connect to MySQL server on '126.96.36.199' (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 = 188.8.131.52
Next, save the file and restart the MySQL server with one of the following commands:
#service mysql restart
Configure MySQL remote access
Fire up your MySQL client and execute the following queries.
mysql> use mysql; mysql> update user set host = '184.108.40.206' where user = 'root';
This will enable MySQL remote access only from 220.127.116.11 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 18.104.22.168