Enable remote access to MySQL database server

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
ssh user@hostname
3.Locate the file my.cnf
Probable locations are /etc/mysql/my.cnf or /etc/my.cnf.
4.Edit the file
nano /etc/mysql/my.cnf
5.Make sure that you comment the line skip-networking to look like
# skip-networking
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
mysql>exit

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’.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s