For most of the database users in a particular network may need to access the database from a remote machine connected in LAN. By default remote access to the MySQL database server is disabled for security reasons. However, sometimes you need to provide remote access to database server from home or a web server. In order to enable the same follow the guidelines given below.
1.Make sure you have a firm connection between two machines (better to test the connection via ping).
2.Login to the MySQL database server through ssh
3.Locate the file my.cnf
Probable locations are /etc/mysql/my.cnf or /etc/my.cnf.
4.Edit the file
5.Make sure that you comment the line skip-networking to look like
6.Move to the line where ‘bind-address = ‘ is written and substitute the ip with the MySQL server ip.
bind-address = YOUR_SERVER_IP
7.Save and close the file
8.Restart MySQL server using
service mysql restart or /etc/init.d/mysql restart or /etc/init.d/mysqld restart
9.To grant access to a particular remote ip, connect to the MySQL server
$ mysql -u root -p 123456
where 123456 is the mysql root password(substitute the password accordingly).
And to grant access over a complete database
mysql>grant all on test.* to tom@’192.168.1.1′ identified by ‘passwd’;
tom is the remote user and 192.168.1.1 is the remote ip. Choose a password instead of passwd.
Quit MySQL by
You can use this username and password in any programming language that support MySQL for accessing the remote database.
For example in Java a statement like
static Connection conn = DriverManager.getConnection(“jdbc:mysql://YOUR_SERVER_IP/test”,”tom”,”passwd”);
can establish a connection to the remote database ‘test’.