Connecting to a mysql server not on localhost

kornfan2007 · 07-12-2007, 09:22 AM

I have just installed Centos 4 with MySQL version 4.1.20 and have been having a problem connecting to it from an external host. Browsing around it would appear that by default it is set to only allow localhost connections. Can anyone tell me the command to enable external server access to the databases please?

puntjuh · 07-12-2007, 10:07 AM

If you have a firewall, i suggest opening the port via the NAT feature in your router. This should allow you to access your MYSQL from anothercomputer outside the network. And i'd suggest setting in your mysql config file.. usually : /etc/my.cnf

And setting behind :

Bind-address: YOUR EXTERNAL IP!!! instead of localhost. Perhaps there allready is a setting inside your conf file.

Doing these 2 things should solve it.

kornfan2007 · 07-12-2007, 10:16 AM

My firewall is disabled so this will not be causing the problem.

Just to clarify as /etc/my.cnf does not currently have anything relating to localhost do you mean edit /etc/my.cnf and add the line "Bind-address: <external IP address>"(Obviously entering the IP address)?

Will have to restart mysql or anything else to activate the change?

Thanks for your advice

rylan76 · 07-12-2007, 10:27 AM

As far as I know, you need to do this in MySQL:

1. Get into MySQL. At a terminal prompt do

mysql -u root -prootpass

to get into MySQL as root. (Use whatever root password you have configured on your installation of MySQL)

2. At the mysql prompt type

grant all privileges on *.* to normaluser@'%' identified by 'normaluser';
flush privileges;

(note this is a stupid setup - you probably don't want to do this - it makes ALL your databases available to ANYBODY who uses the normaluser access)

3. A more restrictive example would be

grant select on database_a to normaluser@'%' identified by 'normaluser';
flush privileges;

I. e. this will make the person who logs in have only read rights (select) on "database_a".

or

grant select,insert on database_b to normaluser@'%' identified by 'normaluser';
flush privileges.

I. e. this will make the person who logs in have read and insert rights on "database_b" - but he won't be able to delete any items.

4. Now, you should be able to use a MySQL client (or PHP, for example) to connect to the server from another machine. The username you will use is

normaluser

and the password will be

normaluser

again.

That's what the '%' as in normaluser@'%' identified by 'normaluser'; means (as far as I know) the % is automagically replaced with the servername / IP of any remote user, i. e. anybody who knows the username and password, on any machine, should be able to get in.

I. e. there is another form like this:

grant all privileges on *.* to normaluser@localhost identified by 'normaluser';
flush privileges;

This is actually how root is created (as far as I know) i. e. ONLY on the local machine will "normaluser" have full access to all databases - somebody who tries to log in from a remote machine WON'T be able to access the databases, EVEN if he identifies himself as "normaluser" and gives the "normaluser" password...

The above of course assumes that all your other settings on the box to ensure MySQL connectivity (besides the stuff above) is working 100%...

kornfan2007 · 07-12-2007, 10:54 AM

Could not get grant all privileges on *.* to normaluser@'%' identified by 'normaluser'; to work. I get the error:

ERROR 1064 (42000): You have an error in your SQL syntax

I could get the following to work though ,but it hasnt resolve the problem:

grant usage on *.* to normaluser@'%' identified by 'normaluser';

felixc · 07-12-2007, 09:47 PM

Hi,

A possible resolution for your problem can be found in the my.cnf file (typically /etc/mysql/my.cnf). One of the lines will say

Code:

 bind-address         = 127.0.0.1

Commenting it out and restarting mysqld will stop it from listening only on localhost. Cheers!

Felix

kornfan2007 · 07-13-2007, 03:27 AM

The my.cnf file does not contain a bind-address line. I tried adding 'bind-address=Eternal IP address' but this did not resolve the problem. Any more ideas?

this213 · 07-13-2007, 04:51 AM

Rylan's code is right, except that he's not using GRANT options:

Code:

GRANT ALL PRIVILEGES ON *.* TO myusername@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;

You shouldn't need to use your grant usage statement.

However, you should update your mysql server before you go getting started with an old version. Install at least version 5.

Make sure you're doing this as a user with permissions to create full access users, like root unless someone's changed that.

You don't need the bind_address line either, that's only for servers that listen ONLY to a given address (usually localhost [127.])

If you can restrict the user to a given database and permissions set, that's obviously better than allowing complete control over your whole database server. As already mentioned, replace the *.* above with something like dbname.* (the two stars denote database.table)

If you can restrict to a given IP address/range that would be even better. % is a wildcard to MySQL (figure something like 'SELECT * FROM table WHERE something LIKE "%that%"'). For example, to restrict to a private LAN, you might do: myusername@'192.168%'

Of course, if you only need this open for internal communications (LAN to LAN), you should NOT have port 3306 open on your firewall - otherwise, you do need it open.

In case you've overly fudged your my.cnf, this one should work (it's default from FC6, but close enough)

Code:

[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
     
[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

If you make changes to my.cnf, you have to restart mysql in order for them to take effect

kornfan2007 · 07-13-2007, 05:31 AM

The command GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION; resolved the issue.

I am unable to upgrade the version as I am creating a test environment which I require version 4. Thanks for your help with this.

rylan76 · 07-18-2007, 02:55 AM

If I were you, I'd think about upgrading the version... I've had definite issues with MySQL 5, 4.1.12-standard in my experience works perfectly with Apache 2 and PHP 5... For one thing, I could never get MySQL 5 to allow any user except root to connect (either on localhost or from a remote machine), after I followed much the same path detailed above.

lord-fu · 07-18-2007, 07:43 AM

On the contrary we have multiple clusters of lamp machines all running 5 that allow users other than root to connect, actually if I wasn't able to get other users than root to connect I would drop MySQL in a heartbeat. Allowing all processes to attach to your SQL server as root is asking for a security incident. An do not have your servers bind to an external ip. Use port forwarding from a router and have it listen on it's lan address.

For those that are not mysql cli proficient there is an awesome tool available on mysql's website called mysql-gui tools, there is a write up about it by Jeremy under article discussions in the general forum with a link. It offers an easy way to do management of a MySQL server all from a gui"licious" interface.