Connecting to MySQL Database over server

Question

I'm quite a beginner when it comes to working with networks and the like, so apologizes up front. A while back I set up a mySQL database locally on my machine, and have built a number of simple programs that work with it. (aka root:localhost sort of thing).

This has been great, but I'm now hoping to allow other colleagues at my work to access the database from their machines, but I have no idea how.

Likely there will be some network protection issues (firewalls etc), so that may need to be taken into account... (although I have IT's help on this, neither IT or myself really know what is required to 'connect' to the database).

For example, is it just an IP I need? Do I have to change the setup of my database? I understand that localhost would not work from my colleagues computer's, for obvious reasons, I have no idea what would go in its place for others to access it.

I also do not mind having my machine run as the dedicated database machine... I would not be able to run it off a dedicated server or anything like that, beyond my machine.

Any help would be much appreciated! Thanks.

Answer 1

First of all, what your colleagues need are:

• The IP Address where MySQL server is running.
• User and Password to connect remotely
• Have the port 3306 open on the network
• A MySQL Client (mysql workbench, mysql query browser, toad, heidi or just the Command Line tool).

When you create user in MySQL the have to be something like this:

'root'@'localhost'

That means, the user will work if you connect from localhost with the user root. So you can create user allowed to connect from anywhere:

'juanperez'@'%'

And finally you have be careful about what privileges are you granting to them. Do not forget to comment a line in the options file of the MySQL that says "bind-address" (this options prevents remote connection).

Answer 2

For example, is it just an IP I need?

Yes. You'll be much happier if you set up proper domain names, but a domain name is just an alias for the IP address.

Do I have to change the setup of my database?

No, but... You have to add some user credentials to support remote logins. That's a change, but not a change to a schema. It's changes to the permissions.

I understand that localhost would not work from my colleagues computer's, for obvious reasons, I have no idea what would go in its place for others to access it.

What MySQL Admin tools are you using? Often there is good help there.

You must read reference manual 4.1 or 5.0

For whatever version is appropriate.

It's very clear.

A user is identified by a username@hostname. You can specify IP addresses (or even "%" for the hostname.

Answer 3

You will use following commandline to connect -

mysql -u<user-id> -p<password> -h<your-hostname-or-ipaddress>

For applications running on different machines trying to connect to your database, you only need to replace 'localhost' with your machine's hostname or ipaddress.

In, general if you are able to ping your machine from a different system, your database can be connected to from that machine, just use whatever name you used for 'pinging' in place of localhost.