How to Enable and Grant Remote Access to MySQL Database Server

For reasons of security, remote access to MySQL database server is disabled by default because they are considered potential security threats. However, due to some reason, it is necessary to allow access from a remote location or web server. Let assume that we are making connection from remote web server IP called 192.168.0.3 for database called db1 for user user1 at remote MySQL server, 192.168.0.2, then we need to grant access to this IP address.

If the remote access is not enable you will get this error :

ERROR 1130 (HY000): Host ‘192.168.0.3’ is not allowed to connect to this MySQL server

IP Adress 1 : 192.168.0.2 – MySQL Server
IP Adress 2 : 192.168.0.3 – Web Server (Nginx or Apache)

Steps to Enable and Grant Remote Access to MySQL Database Server

1. Edit the my.cnf file :

# vim /etc/mysql/my.cnf

Comment out or remove below line :

#bind-address           = 127.0.0.1

2. The following command will allow access to the MySQL database(192.168.0.2) from a remote web server IP address(192.168.0.3):

mysql> create user 'user1'@'192.168.0.3' identified by 'PASSWORD';
mysql> grant all on db1.* to 'user1'@'192.168.0.3';

3. Test the connection from the remote web server :

# mysql -u user1 -pPASSWORD -h 192.168.0.2

4. Verify the user privileges for user1 :

mysql> select * from information_schema.user_privileges where grantee like "'user1'%";

5. In case you want to revoke all options the access from all machine or web server(192.168.0.3) only :

mysql> revoke all privileges, grant option from 'user1'@'%';

mysql> revoke all privileges, grant option from 'user1'@'192.168.0.3';