Monday, September 20, 2010

How to connect to mysql server from a remote machine

My colleague wanted to connect to my local MySql server 5.5 (installed on Windows 7) from his machine. I had not give "root" user, privileges to connect remotely.

Hence, I decided to create a new user for my colleague and give the new user remote access.

So how do we do it?

Let say the user we want to create is called "batman". Lets say that, the machine from which this batman user is going to log on to my local MySql server is called "batmanscave". My machines name is supercomputer.

Our first step towards allowing remote access is to create a user. This is how we do it.

The above syntax tell MySql to create a user batman who is at batmanscave i.e. he will connect to the MySql server from batmanscave machine. And his password is catwoman.

At this point if you try to login to MySql from the batmanscave using the following command it wont work

Because, the user batman still does not have rights to connect to MySql server installed on my machine remotely.

Next, we need to grant remote privileges to the batman user. This is how we do it.

Basically, We are granting all rights to the batman user.

Next, we need to flush privileges so that they are persisted in MySql tables using the following command.

We are almost done!

Even after doing all this we were not able to connect to my machine remotely. Tried various things but no luck.

Turned out that problem was not with MySql, but the real problem was windows firewall. It was blocking the default port of MySql 3306.

I opened up the port in the windows firewall and bingo! We were able to connect to MySql installed on my machine remotely!
Have some Fun!