Let's say you want to connect to a remote MySQL server from your home computer. Consider the following configuration:
MySQL server IP address: 126.96.36.199 Your Home PC IP address: 188.8.131.52
By default, MySQL accepts connections only from a localhost. Thus, executing the command:
#mysql -u root -p -h 184.108.40.206
will produce the following error:
ERROR 2003 (HY000): Can't connect to MySQL server on '220.127.116.11' (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 = 18.104.22.168
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 = '22.214.171.124' where user = 'root';
This will enable MySQL remote access only from 126.96.36.199 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 188.8.131.52