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)

READ  How to Install phpMyAdmin on Ubuntu 14.04

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';

database

Leave a Reply

Your email address will not be published. Required fields are marked *

SiteGround.com

A world leading hosting company that provides fully-managed innovative and secure solutions, suitable for hosting small to medium-sized websites

Built on the best available technologies combined with Google Cloud for strong redundancy and application availability. Backed by skilled experts to address web security threats, a devops team to create advanced custom security solutions, and 24/7 sysadmins to watch over the platform. This powerful, hands-on approach makes your sites faster, safer, and easier to manage. Starting from only $3.95/mo.

TRY FREE

* up to 30 days money back guarantee