LinuxQuestions.org
View the Most Wanted LQ Wiki articles.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices

Reply
 
Search this Thread
Old 07-12-2007, 09:22 AM   #1
kornfan2007
LQ Newbie
 
Registered: Jul 2007
Posts: 5

Rep: Reputation: 0
Connecting to a mysql server not on localhost


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?

Last edited by kornfan2007; 07-12-2007 at 09:27 AM.
 
Old 07-12-2007, 10:07 AM   #2
puntjuh
Member
 
Registered: Apr 2006
Location: holland
Distribution: Gentoo / debian / suse / mint
Posts: 558

Rep: Reputation: 41
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.
 
Old 07-12-2007, 10:16 AM   #3
kornfan2007
LQ Newbie
 
Registered: Jul 2007
Posts: 5

Original Poster
Rep: Reputation: 0
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
 
Old 07-12-2007, 10:27 AM   #4
rylan76
Senior Member
 
Registered: Apr 2004
Location: Potchefstroom, South Africa
Distribution: Fedora 17 - 3.3.4-5.fc17.x86_64
Posts: 1,475

Rep: Reputation: 87
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%...
 
Old 07-12-2007, 10:54 AM   #5
kornfan2007
LQ Newbie
 
Registered: Jul 2007
Posts: 5

Original Poster
Rep: Reputation: 0
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';
 
Old 07-12-2007, 09:47 PM   #6
felixc
Member
 
Registered: Jul 2005
Location: Canada
Distribution: Ubuntu, Debian
Posts: 94

Rep: Reputation: 15
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
 
Old 07-13-2007, 03:27 AM   #7
kornfan2007
LQ Newbie
 
Registered: Jul 2007
Posts: 5

Original Poster
Rep: Reputation: 0
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?
 
Old 07-13-2007, 04:51 AM   #8
this213
Member
 
Registered: Dec 2001
Location: ./
Distribution: Fedora, CentOS, RHEL, Gentoo
Posts: 167

Rep: Reputation: 34
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
 
Old 07-13-2007, 05:31 AM   #9
kornfan2007
LQ Newbie
 
Registered: Jul 2007
Posts: 5

Original Poster
Rep: Reputation: 0
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.
 
Old 07-18-2007, 02:55 AM   #10
rylan76
Senior Member
 
Registered: Apr 2004
Location: Potchefstroom, South Africa
Distribution: Fedora 17 - 3.3.4-5.fc17.x86_64
Posts: 1,475

Rep: Reputation: 87
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.
 
Old 07-18-2007, 07:43 AM   #11
lord-fu
Member
 
Registered: Apr 2005
Location: Ohio
Distribution: Slackware && freeBSD
Posts: 676

Rep: Reputation: 30
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.

Last edited by lord-fu; 07-18-2007 at 07:48 AM.
 
  


Reply

Tags
host, mysql, remote


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
mysql error connect to server at 'localhost' failed pete44904 Linux - Server 10 11-23-2012 06:08 AM
Squirrelmail Error connecting to IMAP server: tls://localhost cccc Linux - Server 4 01-12-2007 01:39 AM
Squirrelmail Error connecting to IMAP server: tls://localhost cccc Debian 1 09-18-2006 01:44 PM
Sarge mysql-server Access denied for user: root@localhost infinity432 Linux - Software 2 05-18-2005 11:42 PM


All times are GMT -5. The time now is 04:09 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration