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)
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)
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
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
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 firstname.lastname@example.org 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 email@example.com;
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.
The Nonsense Blogger !!