How to enable remote access to mysql database server


Enable remote access to mySQL database server

1. Open the MySQL server configuration file my.cnf.

sudo gedit /etc/mysql/my.cnf

2. Change the bind-address value to your MySQL server ip. If the MySQL server IP=10.100.2.200, change it as follows, save and close my.cnf file.

bind-address=10.100.2.200

3. Now Grant access to remote IP.
If the remote IP=10.100.2.180 

Grant permission to a specific database (ex: testbd)

GRANT ALL ON testdb.* TO testuser@'10.100.2.180' IDENTIFIED BY 'test123';

Grant all permissions

GRANT ALL PRIVILEGES ON *.* TO testuser@'10.100.2.180' IDENTIFIED BY 'test123' WITH GRANT OPTION;

4. Open port 3306, if firewalls are enabled. Following is a sample iptables rule to open Linux iptables firewall.


/sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT


5. logout of mysql.

6. Connect from remote host

mysql -u root -h 10.100.2.200 -p

Comments

  1. If you allowing to IP range grant permision should be :

    GRANT ALL ON testdb.* TO testuser@'10.100.2.0/255.255.255.0' .... There you can provide access from the 10.100.2.0 to 10.100.2.255

    or

    GRANT ALL ON testdb.* TO testuser@'10.100.2.%' ...

    ReplyDelete

Post a Comment

Popular posts from this blog

Manually install a maven dependency

PHP-SOAP web service with out a WSDL

Axis 2 web services in eclipse: Exception java.lang.NoClassDefFoundError: org/apache/http/HttpResponseFactory thrown when Services link clicked