Mysql – Configuring Access from a different Host


I just configured access to a mysql server from a machine outside the server machine. I find that there are a couple of steps that you need to remember. Just posting it here for my self reference and for any one else who would find it useful.

Enabling access to Mysql server from a different machine requires you to do the following two steps.

1. Mysql has to be configured so that it can bind on a host ip/address ( By default it listens only on localhost)

In my.cnf, under [mysqld] change bind-address to Host IP or Host Name. By default this attribute will be set to localhost. Find below, the relevant section of my.cnf file (Find higlighted the bind-address attribute)

[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address = 192.168.1.45
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover = BACKUP
query_cache_limit = 1M
query_cache_size = 16M

2. Now restart mysql service using any of the following commands

restart mysql
/etc/init.d/mysql restart

3. Give priviliges to the required users – For access from different hosts

Format : GRANT ALL on databasename.* to username@HostIP identified by ‘userpassword’;
Example : GRANT ALL on test.* to tj@192.168.1.50 identified by ‘tjpassword’;

This will give access to user tj from Host 192.168.1.50 to database test. If you want to see the current priviliges for user tj, use the following command.

Format : show grants for username@hostip
Example : show grants for tj@192.168.1.50;

This will show all the grants given to user tj connecting from 192.168.1.50

Note : In case you encounter the following exception (like i did). Now you know what you’ve got to do right !!

java.sql.SQLException: null, message from server: "Host 'XXXX' is not allowed to connect to this MySQL server"

Hope this helps.

Cheers,
The Nonsense Blogger !!

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